25

What information should be provided when asking how to improve the performance of a query?

24

As many of the following as possible:

  • Formatted text of the query/procedure/function.
    • Indenting SQL by 4+ spaces on each line is usually best.
  • Schema (CREATE TABLE); use text, not images
  • The or tag.
  • Query plans
    • Ideally the post-execution variety, containing runtime information.
    • Estimated execution plans (if obtaining a post-execution plan is not practical).
    • Anonymized plans are acceptable but may limit answer quality.
    • A picture of execution is occasionally enough (but discouraged).
  • Environment
    • Database product, precise version, and edition.
    • Hardware (e.g. memory/CPU/storage) and OS information.
  • About the issue
    • What is the intent of the query
    • Current and desired performance levels.
    • Things you can and cannot change (e.g. can add indexes, cannot change the query).
    • The tuning steps followed so far.

Further reading

2
  • 3
    I'm hoping to keep this answer to a manageable size. Please contribute, but prioritize brevity over comprehensiveness. Feel free to add additional links to the Further reading section at the end.
    – Paul White Mod
    Aug 27 '18 at 7:35
  • "Maneageable size" -- I added structure since there are only about 6 things. I can answer most query performance questions given only the first 2. (The 3rd, a tag, is to get my attention.)
    – Rick James
    Sep 10 at 15:57
3

Instructions for PostgreSQL Performance Questions

Copied from the tag info.

About improving / explaining the performance of existing queries. May concern particular query techniques or the setup of your hard- and software. Tag with as well.

Questions about trivial queries should rather go to stackoverflow.com. See DBA Help about suitable questions.

Consider basic advice on Performance Optimization and Slow Query Questions in the PostgreSQL Wiki, including the section "Things to try before you post". EXPLAIN is particularly important.

When posting questions, include:

  • Your Postgres version, at least the major version like 9.6 or 13. Use: SELECT version();

  • Your actual query/queries. In a readable format and as brief as possible, but don't remove anything that might be relevant. Describe the expected result. Include an example for simple cases.

  • Table definition(s): Preferably a CREATE TABLE script showing data types and constraints for relevant columns and CREATE INDEX scripts for all relevant indexes. Or the output from \d+ tablename in psql.

  • Cardinalities (rough number of rows) in involved tables. Cardinalities (rough number of distinct values) and typical distribution in crucial columns.

  • Query plan(s) obtained with EXPLAIN (BUFFERS, ANALYZE). Consider pasting plans on explain.depesz.com and include links.

  • Link to an online demo at db<>fiddle, SQL Fiddle or similar, populated with the schema, some sample data, and the query.

  • Only where relevant, a brief mention of your hardware and system, like: "CentOS 6.1, Xeon E5-2450 with 64GB RAM, 4-disk RAID 10 of Intel X-25E SSDs". Or more details if your question is about hardware.

1

Taken from a comment by Wilson Hauck on a MySQL performance issue question.

Instructions for MySQL Performance Questions

  1. Computer specifications

    • RAM size
    • SSD/NVME versus HDD on MySQL Host server

    Please post on pastebin.com and share the links. (Or similar product.)

Additionally from your SSH login root, text results of:

  1. SHOW GLOBAL STATUS; (after minimum 24 hours UPTIME)

  2. SHOW GLOBAL VARIABLES;

  3. SHOW FULL PROCESSLIST;

  4. a complete MySQLTuner report

  5. SHOW ENGINE INNODB STATUS;

  6. SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; required for server workload tuning analysis to provide suggestions.

  7. Enable the mysql slow query log (MySQL-5.7, MySQL-8.0, MariaDB). This can be done at runtime (in addition to the config file for next restart):

    • SELECT @@slow_query_log_file, @@log_output; to see what the filename (if @@log_output is FILE) will be. Change if required before enabling with slow_query_log.
    • SELECT @@long_query_time; to see what the time in seconds for the slow queries are. The default of 10 seconds is rarely useful; change to 1.
    • SET GLOBAL slow_query_log=1 /*M!100005 , GLOBAL log_slow_verbosity='query_plan,explain' */;
    • use mysqldumpslow -s t or pt-query-digest to summarize the important ones.
    • Post the most important queries along with EXPLAIN {query} and SHOW CREATE TABLE {tablename} for each table in the query. (Please do not use the less-descriptive DESCRIBE.)
  8. Please use text, not images.

3
  • You need also to post an EXPLAIN SELECT .... to see if and which indexes where used. Further a cull CREATE TABLE with INDEXES is qalso helpful.
    – nbk
    Sep 27 '20 at 20:06
  • @nbk You can add the details to the answer yourself. It's a community wiki answer and as such can be edited by anyone/everyone.
    – John K. N.
    Sep 28 '20 at 8:04
  • @danblack Thanks.
    – John K. N.
    Jul 8 at 5:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .