Performance issues with PostgreSQL queries
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.
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 like
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 TABLEscript showing data types and constraints for relevant columns and
CREATE INDEXscripts for all relevant indexes. Or the output from
Cardinalities (rough number of rows) in involved tables. Rough distribution of critical values (if applicable).
Query plan(s) obtained with
EXPLAIN (BUFFERS, ANALYZE)or
EXPLAIN ANALYZEin pg 9.0 or older. If possible paste them on explain.depesz.com and include links.
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.