Hello,
continuing the saga,
http://archives.postgresql.org/pgsql...4/msg00558.php ,
my coleague created a test database with fake data (see below).

The above archived message contains the the timings of firebird and postgresql.
The weird problem are the 2 queries that firebird executes in less than 2
seconds and postgresql took almost half hour to complete at 100% cpu.


you could download the test database at the address below. It is a 128 kpbs
adsl connection.
74 MB
http://www.eicomm.no-ip.com/download/BackDNF_Cript.zip


Many thanks.
Andre Felipe Machado


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
> Hello,
> continuing the saga,
> http://archives.postgresql.org/pgsql...4/msg00558.php ,
> my coleague created a test database with fake data (see below).
>
> The above archived message contains the the timings of firebird and postgresql.
> The weird problem are the 2 queries that firebird executes in less than 2
> seconds and postgresql took almost half hour to complete at 100% cpu.

How about posting EXPLAIN ANALYZE for those two queries, as well as the
queries themselves?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby wrote:
> On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
>> Hello,
>> continuing the saga,
>> http://archives.postgresql.org/pgsql...4/msg00558.php ,
>> my coleague created a test database with fake data (see below).
>>
>> The above archived message contains the the timings of firebird and postgresql.
>> The weird problem are the 2 queries that firebird executes in less than 2
>> seconds and postgresql took almost half hour to complete at 100% cpu.
>
> How about posting EXPLAIN ANALYZE for those two queries, as well as the
> queries themselves?

I have this database downloaded if anyone wants a copy off a faster link.

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"andremachado" <andremachado@techforce.com.br> writes:
> continuing the saga,
> http://archives.postgresql.org/pgsql...4/msg00558.php ,
> my coleague created a test database with fake data (see below).

Thanks. I played around with this a bit, and got results like these:
original query, 8.1 branch from a couple weeks back: 945 sec
original query, 8.1 branch tip: 184 sec
modified query, 8.1 branch tip: 15 sec

The first differential is because of this patch:
http://archives.postgresql.org/pgsql...4/msg00355.php
viz
Remove the restriction originally coded into
optimize_minmax_aggregates() that MIN/MAX not be converted to
use an index if the query WHERE clause contains any volatile
functions or subplans.

Allowing the max(DEC2.AM_REFERENCIA) subquery to be converted to an
indexscan makes for about a 5X reduction in the number of times the
EXISTS sub-subquery is executed. But the real problem is that Postgres
isn't excessively smart about EXISTS subqueries. I manually changed it
into an IN to get the 15-second runtime: instead of

(select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2
where DEC2.IN_FOI_RETIFICADA=0 and
exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where
CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and
CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )

write

(select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2
where DEC2.IN_FOI_RETIFICADA=0 and DEC2.ID_DECLARACAO in
(select CAD3.ID_DECLARACAO from CADASTRO CAD3 where
CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )

I'm not clear on how Firebird is managing to do this query in under
a second --- I can believe that they know how to do EXISTS as a join
but it still seems like the subqueries need to be done many thousand
times. I thought maybe they were caching the results of the overall
subquery for specific values of CADASTRO.ID_EMPRESA, but now that I
see your test data, there are several thousand distinct values of
that, so there's not a lot of traction to be gained that way.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hello, Jim
I did not want to clutter mailboxes of those who are not interested at
this weird problem.
So, i pointed to the archived message that contains 2 tar.gz files
(around 50 KB) with the two sets of queries (firebird and postgresql
respectively), its results, explain analyze, pg configurations, firebird
plan outputs, etc.
Please, open the cited link and scroll to the end of message. You will
find the 2 tar.gz files.
http://archives.postgresql.org/pgsql...4/msg00558.php
If you have some difficulty, I could send a private email containing the
2 files in order to not send the big email to the all list again.
Many thanks.
Andre Felipe Machado


Em Sex, 2006-05-12 às 14:14 -0500, Jim C. Nasby escreveu:
> On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
> > Hello,
> > continuing the saga,
> > http://archives.postgresql.org/pgsql...4/msg00558.php ,
> > my coleague created a test database with fake data (see below).
> >
> > The above archived message contains the the timings of firebird and postgresql.
> > The weird problem are the 2 queries that firebird executes in less than 2
> > seconds and postgresql took almost half hour to complete at 100% cpu.
>
> How about posting EXPLAIN ANALYZE for those two queries, as well as the
> queries themselves?


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"andremachado" <andremachado@techforce.com.br> writes:
> continuing the saga,
> http://archives.postgresql.org/pgsql...4/msg00558.php ,
> my coleague created a test database with fake data (see below).

I tried to use this data to replicate your results, and could not.
I grabbed a copy of what I think is the latest Firebird release,
firebird-1.5.3.4870, built it on a Fedora Core 4 machine (32-bit,
couldn't get it to build cleanly on my newer 64-bit machine :-()
and compared to Postgres 8.1 branch tip on the same machine.
On the interesting sub-sub-EXISTS query, I see these results:

Firebird:
SQL> set stats on;
SQL> set plan on;
SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end ;

PLAN (CAD3 INDEX (RDB$FOREIGN1))
PLAN (DEC2 NATURAL)
PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN1))
PLAN (CADASTRO NATURAL)
Current memory = 786704
Delta memory = 309056
Max memory = 786704
Elapsed time= 344.19 sec
Cpu = 0.03 sec
Buffers = 75
Reads = 2081702
Writes = 16173
Fetches = 21713743

The cpu = 0.03 sec bit is bogus; in reality the CPU is maxed out
and the isql process accumulates very nearly 344 seconds runtime.

Postgres:
bc=# \timing
Timing is on.
bc=# update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end ;
UPDATE 15490
Time: 420350.628 ms

Now I know nothing about Firebird and it's quite possible that I missed
some essential tuning step, but I'm sure not in the same ballpark as
your report of 0.72 sec to run this query.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org