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 <your_query>. 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