Slow Query Questions

From PostgreSQL wiki

Jump to: navigation, search

Contents

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)

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.

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. Run psql command "\d table" with the tables/views/indices referenced in the problem query.

Table Metadata

In addition to the table definition, please also post the approximate number of rows in the table(s):

  • SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='TABLE_NAME';

Does the table have anything unusual about 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.

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.

Hardware benchmark

  • Run 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.
    • bonnie++ -f -n0 -x4 -d /var/lib/pgsql
  • Test sequential read speed of the device where the postgres data files are stored, run this several times. If you have a separate drive for WAL, or tmp, or multiple tablespaces, run several times for each. 128*$RANDOM/32 is 128GB, which needs to be proportionally increased if you have more than ~64GB RAM. 32K is 32GB, which needs to be increased if your drives are very fast (or very slow). If your DB is very small, this may not be a good test (but in that case, drive speed maybe doesn't matter anyway).
    • time dd if=/dev/sdX2 of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32))

Maintenance Setup

Are you running autovacuum? If so, with what settings? If not, are you doing manual VACUUM and/or ANALYZE? How often? SELECT * FROM pg_stat_user_tables WHERE relname='table_name';

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 "shared_buffers", "work_mem", "enable_seq_scan", "effective_io_concurrency", "effective_cache_size", etc). See Server Configuration for a useful query that will show all of your non-default database settings, in an easier to read format than posting pieces of your postgresql.conf file.

Statistics: n_distinct, MCV, histogram

Useful to check statistics leading to bad join plan. SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;

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
Personal tools