Postgres Pro
Downloads
Home   >   mailing lists

Thread: What query optimisations are included in Postgresql?

What query optimisations are included in Postgresql?

From
N
Date:
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?

thanks.

Bentley.

Re: What query optimisations are included in Postgresql?

From
Adrian Klaver
Date:
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

Re: What query optimisations are included in Postgresql?

From
N
Date:
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

Re: What query optimisations are included in Postgresql?

From
Adrian Klaver
Date:
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

Re: What query optimisations are included in Postgresql?

From
Albe Laurenz
Date:
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

Re: What query optimisations are included in Postgresql?

From
John R Pierce
Date:
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