Thread: PostgreSQL: query optimizer: viewing query plans

PostgreSQL: query optimizer: viewing query plans

From
nzanella@cs.mun.ca (Neil Zanella)
Date:
Hello,

Several textbooks talk about query trees and query plans including the following
relatively recent book <http://www.oreilly.com/catalog/sqltuning/> which covers
how to apply performance tuning techniques by using knowledge about the query
optimizer to rewrite SQL queries so that they get interpreted more efficiently.

Unfortunately the book only shows how to view the query plan associated witht the
given query for Oracle, IBM DB2, and MS SQL Server. I wonder whether PostgreSQL
also has a way to let the user see the heuristically computed query plan created
by the query optimizer. Is it possible to view query plans with PostgreSQL server
and its related command line interfaces? How would I run such a command?

Thanks,

Neil

Re: PostgreSQL: query optimizer: viewing query plans

From
CoL
Date:
Hi,

Neil Zanella wrote:

> Hello,
>
> Several textbooks talk about query trees and query plans including the following
> relatively recent book <http://www.oreilly.com/catalog/sqltuning/> which covers
> how to apply performance tuning techniques by using knowledge about the query
> optimizer to rewrite SQL queries so that they get interpreted more efficiently.
>
> Unfortunately the book only shows how to view the query plan associated witht the
> given query for Oracle, IBM DB2, and MS SQL Server. I wonder whether PostgreSQL
> also has a way to let the user see the heuristically computed query plan created
> by the query optimizer. Is it possible to view query plans with PostgreSQL server
> and its related command line interfaces? How would I run such a command?

explain query
explain analyze query

and you can set logging in postgres.conf, and you can log
debug_print_XXX  things (xxx like parse, rewritten query, the plan)

C.