Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"andremachado" <andremachado@xxxxxxxxxxxxxxxx> writes:
> continuing the saga, 
> http://archives.postgresql.org/pgsql-performance/2006-04/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-committers/2006-04/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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux