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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings