Re: General query optimization howto

On Sun, 2005-03-13 at 02:34 +0100, Miroslav Šulc wrote:

> is there on the net any general howto on SQL query optimizations? We
> have recently moved our project from MySQL to PostgreSQL and are having
> problem with one of our queries. The EXPLAIN command is surely useful
> but I don't know how to read it and how to use the output to optimize
> the query so I'm looking for some intro that could help me.

EXPLAIN ANALYZE is even more useful.

start with looking for inconsistencies between row estimates and actual
row counts. these could mean that you need to ANALYZE, or increase
statistics for some columns. also look for expensive sequential scans
where you would expect an index scan. this may be due to missing
indexes, imcompatible column types, lack of ANALYZE, or insufficient
statistics.

browse through the archives of the pgsql-performance list, to get a feel
of typical problems, and to read illuminating responses from regulars.

if you still are having problems, make the simplest test case you can,
and post an EXPLAIN ANALYZE to pgsql-performance, along with relevant
data, such as table definitions, typical data distributions and
postgres version.

gnari