Thread: General query optimization howto

Hi all,

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.

--
Miroslav Šulc

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.

I doubt that there is a generic documentation on SQL optimization,
because this heavily depends on the particular implementation that you
are using.

> 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.

You should probably start with the Performance Tips chapter in the
PostgreSQL documentation.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Peter Eisentraut wrote:

>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.
>>
>>
>
>I doubt that there is a generic documentation on SQL optimization,
>because this heavily depends on the particular implementation that you
>are using.
>
>
>
>>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.
>>
>>
>
>You should probably start with the Performance Tips chapter in the
>PostgreSQL documentation.
>
>
>
I've read that one. Sure it is good starting point but for a newbie (=
me) it's not enough :-(

Miroslav Šulc

Bruce Momjian wrote:

>Have you read the FAQ?
>
Yes, but I have found only some useful information saying when indexes
are not used.

Miroslav Šulc