Обсуждение: 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