What information should be provided when asking how to improve the performance of a query?
As many of the following as possible:
- Database product, precise version, and edition.
- Formatted text of the query/procedure/function.
- Current and desired performance levels.
- Things you can and cannot change (e.g. can add indexes, cannot change the query).
- 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 an execution is occasionally enough (but discouraged).
- A link to a demo that reproduces the core of the problem.
- Or table, index, and constraint definitions (scripts).
- Hardware (e.g. memory/CPU/storage) and OS information.
- The tuning steps followed so far.
- The performance tag.
Further reading
-
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♦ Aug 27 '18 at 7:35
Instructions for PostgreSQL Performance Questions
Served as description for the tag postgresql-performance before that was merged into the generic tag performance.
Change was reverted and tag is back:postgresql-performance
If you already know a way to get your output but need a faster solution then this tag may be appropriate. May concern techniques for a particular query or the setup of your hard- and software. Tag with postgresql as well.
Questions how to optimize simple queries should rather go to stackoverflow.com. See the DBA Help about suitable questions here.
Consider the basic advice on Performance Optimization and Slow Query Questions in the PostgreSQL Wiki before asking questions on this tag, including the "Things to try before you post" section. Using EXPLAIN
is particularly important.
When posting questions, include:
Your Postgres version, from
SELECT version()
, or at least your major version like9.6
or10
.The full text of the 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 andCREATE INDEX
scripts for all relevant indexes. Or the output from\d+ tablename
inpsql
.Cardinalities (rough number of rows) in involved tables. Rough distribution of critical values (if applicable).
Query plan(s) obtained with
EXPLAIN (BUFFERS, ANALYZE)
orEXPLAIN ANALYZE
in pg 9.0 or older. If possible paste them on explain.depesz.com and include links.If possible, add a 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 on Dell PERC H810 controller with flash write-back cache"
Or more details if your question is about hardware.
Taken from a comment by Wilson Hauck on a MySQL performance issue question.
Instructions for MySQL Performance Questions
Computer specifications
- RAM size
- # cores
- any SSD or NVME devices on MySQL Host server
Please post on pastebin.com and share the links.
Additionally from your SSH login root, text results of:
SHOW GLOBAL STATUS;
(after minimum 24 hours UPTIME)SHOW GLOBAL VARIABLES;
SHOW FULL PROCESSLIST;
a complete MySQLTuner report
- Windows version: github.com/pmachapman/mysqltuner
- Linux version: MySQLTuner-perl
SHOW ENGINE INNODB STATUS;
SELECT name, count FROM information_schema.innodb_metrics ORDER BY name;
required for server workload tuning analysis to provide suggestions.
-
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