Обсуждение: What query optimisations are included in Postgresql?
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.
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
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
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
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
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