Thread: Are projected queries optimized like nonprojected ones

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 ?

Andrus.

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