Slow Query Questions

From PostgreSQL wiki

Guide to Posting Slow Query Questions

In any given week, some 50% of the questions on #postgresql IRC and 75% on pgsql-performance are requests for help with a slow query. However, it is rare for the asker to post complete information about the slow query, frustrating both the asker and those who try to help them.

Therefore, before posting a "My query is slow" question, please collect the following information and observe the following recommendations. Please also examine and post the relevant information from the Guide to reporting problems.

Please post performance related questions to the pgsql-performance mailing list after reading the resources available in the performance category of the wiki, collecting the relevant information (see below) and after thinking about the problem. Before you click "Send", read your message, pretending to be somebody who knows nothing about your setup or application, and see if it actually makes sense. This will save you several "you didn't explain <blah>" and "what do you mean by foo" round trips before anybody has enough information to help you - so putting a little effort in at the start will get you better answers, faster.

Information You Need To Post

(note: post this onto a paste site like [pgsql.privatepaste.com] and not directly into the IRC channel if you're asking for help on IRC)

Full Table and Index Schema: Post the definitions of all tables and indexes referenced in the query. If the query touches views or custom functions, we'll need those definitions as well.

Table Metadata: in addition to the table definition, please also post the approximate number of rows in the table(s), plus any thing unusual about it such as if it:

contains large objects

has a large proportion of NULLs in several columns

receives a large number of UPDATEs or DELETEs regularly

is growing rapidly

has many indexes on it

uses triggers that may be executing database functions, or is calling functions directly

EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN:EXPLAIN (ANALYZE, BUFFERS) tells us how the query actually was executed, not just how it was planned. This is essential information in finding out how the planner was wrong, if anywhere. If you can't run an EXPLAIN (ANALYZE, BUFFERS) because the query never completes, then say so. Example: EXPLAIN (ANALYZE, BUFFERS) select * from tablename;

EXPLAIN (ANALYZE, BUFFERS) was introduced in 9.0. If your Postgres version is prior to 9.0, use EXPLAIN ANALYZE instead.

For Postgres version 9.2 and up turn on track_io_timing before executing the EXPLAIN. If you log in as a superuser you can turn track_io_timing on for only the current session by executing SET track_io_timing = on;

Even better, paste your EXPLAIN (ANALYZE, BUFFERS) or EXPLAIN ANALYZE results into explain.depesz.com and post links to the resulting pages. We love this, because it makes the plans much easier to read and examine.

Postgres version: please provide the exact server version you are using (SELECT version() is an easy way to get it). The behavior of the planner changes in every release, so this is important.

History: was this query always slow, or has it gotten slower over time? If the plan/execution of the query used to be different, do you have copies of those query plans? Has anything changed in your database other than the accumulation of data?

Hardware: please post the essential information about your hardware platform. If any elements of your hardware are unusual, please include detailed information on those. See the Guide to reporting problems for what sort of hardware information is useful.

Maintenance Setup: are you running autovacuum? If so, with what settings? If not, are you doing manual VACUUM and/or ANALYZE? How often?

WAL Configuration: (for data writing queries) have you moved the WAL to a different disk? Changed the settings?

GUC Settings: what database configuration settings have you changed? What are their values? (These are things like "work_mem", "enable_seq_scan", etc). See Server Configuration for a useful query that will show all of your non-default database settings, in an easier to read form than posting pieces of your postgresql.conf file.

Things to Try Before You Post

You will save yourself a lot of time if you try the following things before you post your question:

Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a difference?

For Insert/Update/Delete queries, you should also try configuring your WAL:

Move pg_xlog to a separate disk resource, if possible

Increase checkpoint_segments to 16 or more (assuming you have disk space)

Increase wal_buffers to 16MB

Test your IO support: run dd test, bonnie++ or other drive speed tests to see if your performance problem isn't simply hardware-based. For example, a RAID-5 configuration will never have fast inserts/updates no matter how you play with your queries.