About PostgreSQL query optimization. Include sufficient information in questions. For complex, advanced questions or if you're looking to tune without modifying queries, ask on https://dba.stackexchange.com/ instead.
In PostgreSQL there are typically many different queries to achieve the same output from a given input. If you already have a working query but need a faster solution then this tag may be appropriate. Tag with postgresql as well.
Questions about overall Postgres server performance tuning, or the performance of queries you cannot change are probably better directed to dba.stackexchange.com. See the Stack Overflow Help about suitable questions.
Read the Slow Query Questions page on the PostgreSQL Wiki before posting, including the "Things to try before you post" section. Using EXPLAIN ANALYZE is particularly important.
When posting questions, include:
Your Postgres version, at least the major version like "15" or "9.6". (Since Postgres 10, the major version is just the leading number.) Find out with:
SELECT version();The full query text, in readable format and as brief as possible. But don't remove anything that might be relevant. Describe the expected result, include an example.
Definition of involved objects. Best as valid
CREATE TABLEandCREATE INDEXscripts. The output from\d+ tablename(for tables) inpsqlis second best.Cardinalities (rough number of rows) in involved tables. And rough number of distinct values in relevant columns.
Query plan(s) obtained with
EXPLAIN (BUFFERS, ANALYZE)(from v12 on, includeSETTINGSin the parentheses). If possible, paste on explain.depesz.com and include link(s).If possible, link to a short demo on dbfiddle.uk or similar.
Only if 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"
