Re: What query optimisations are included in Postgresql? - Mailing list pgsql-general

N wrote:
> Are there documents specifying the query optimisations in Postgresql
> like the SQLite (http://www.sqlite.org/optoverview.html)?
> 
> From the web, I can say, there are index and join optimisation, but
> are there anything others like Subquery flattening?

There is subquery flattening:

test=> EXPLAIN SELECT id1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE id2 = id1);
                       QUERY PLAN
---------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..2.03 rows=1 width=4)
   Join Filter: (t1.id1 = t2.id2)
   ->  Seq Scan on t1  (cost=0.00..1.01 rows=1 width=4)
   ->  Seq Scan on t2  (cost=0.00..1.01 rows=1 width=4)
(4 rows)

I guess that the reason why there is no such list is that there
are so many query optimizations that it would be difficult to
list them all.  And things are improving from release to release.

Yours,
Laurenz Albe