Slow Query Questions

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Information You Need To Post: explain (analyze, buffers))
(Information You Need To Post: Add guide to turning on I/O timing)
 
Line 24: Line 24:
 
* '''EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN:''' <code>EXPLAIN (ANALYZE, BUFFERS)</code> 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.
 
* '''EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN:''' <code>EXPLAIN (ANALYZE, BUFFERS)</code> 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.
 
** EXPLAIN (ANALYZE, BUFFERS) was introduced in 9.0.  If your Postgres version is prior to 9.0, use '''<code>EXPLAIN ANALYZE</code>''' instead.
 
** EXPLAIN (ANALYZE, BUFFERS) was introduced in 9.0.  If your Postgres version is prior to 9.0, use '''<code>EXPLAIN ANALYZE</code>''' 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 '''<code>SET track_io_timing = on;</code>'''
 
** Even better, paste your <code>EXPLAIN (ANALYZE, BUFFERS)</code> or <code>EXPLAIN ANALYZE</code> results into [http://explain.depesz.com/ explain.depesz.com] and post links to the resulting pages. We love this, because it makes the plans much easier to read and examine.
 
** Even better, paste your <code>EXPLAIN (ANALYZE, BUFFERS)</code> or <code>EXPLAIN ANALYZE</code> results into [http://explain.depesz.com/ 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.
 
* '''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.

Latest revision as of 14:40, 7 October 2012

[edit] 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.

[edit] 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.
    • 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.

[edit] 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:

  • Read Using EXPLAIN if you haven't already.
  • ANALYZE your database to update query stats.
  • VACUUM your database to purge if you are not already running Autovacuum.
  • Check your main GUC settings to make sure that they are set to sensible values (see Tuning Your PostgreSQL Server for additional hints):
    • shared_buffers should be 10% to 25% of available RAM
    • effective_cache_size should be 75% of available RAM
  • 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.
Personal tools