What query optimisations are included in Postgresql?

On 11/28/2013 09:46 AM, N wrote:

Are there documents specifying the query optimisations in Postgresql
like the SQLite (http://www.sqlite.org/optoverview.html)?

http://www.postgresql.org/docs/9.3/interactive/overview.html
http://www.postgresql.org/docs/9.3/interactive/performance-tips.html

From the web, I can say, there are index and join optimisation, but

are there anything others like Subquery flattening?

thanks.

Bentley.

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks, but still not same. For example, is there subquery flattening
, is there any other query re-write ? is there website or books have
such detail ?

On Thu, Nov 28, 2013 at 5:54 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 11/28/2013 09:46 AM, N wrote:

Are there documents specifying the query optimisations in Postgresql
like the SQLite (http://www.sqlite.org/optoverview.html)?

http://www.postgresql.org/docs/9.3/interactive/overview.html
http://www.postgresql.org/docs/9.3/interactive/performance-tips.html

From the web, I can say, there are index and join optimisation, but

are there anything others like Subquery flattening?

thanks.

Bentley.

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/28/2013 10:27 AM, N wrote:

Thanks, but still not same. For example, is there subquery flattening
, is there any other query re-write ? is there website or books have
such detail ?

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/optimizer/README;h=adaa07ee60eebef0199b6e7208f709e56a6411e8;hb=b7f59e6d3e7c10ef0e222ce8ee6d19e8be304e29

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/28/2013 9:46 AM, 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's nearly as much query plan optimization as a C compiler has code
optimization.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general