Re: Are projected queries optimized like nonprojected ones

am  Thu, dem 16.10.2008, um 14:43:42 +0300 mailte Andrus folgendes:
> I'm looking a hint for new application dynamic query builder creation for 
> PostgreSQL 8.0+
> 
> Following two queries return same results:
> 
> SELECT ...
> FROM t1 JOIN t2 USING (cx)
> LEFT JOIN t3 USING (cy)
> LEFT JOIN t4 USING (cz)
> WHERE ...
> 
> and
> 
> SELECT ...
> FROM (SELECT * FROM t1 JOIN t2 USING (cx) LEFT JOIN t3 USING (cy) WHERE 
> ... ) p1
> LEFT JOIN t4 USING (cz)
> WHERE ...
> 
> Second query is easier to generate but contains two where clauses which 
> produce logically same resultset and in first query.
> So it is preferable to use second form. However I'm not clear how this 
> affects query perfomance.
> 
> Questions:
> 
> Will execution speed of both queries be the same ?
> Will 8.0+ optimizers create same execution plan for those queries ?

You can check this with EXPLAIN ANALYSE . I'm guessing it
depends on the PG-Version, recent versions are smarter.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net