About

About improving / explaining the performance of existing queries. May concern particular query techniques or the setup of your hard- and software. Tag with postgresql 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 "15" or "9.6". (Since Postgres 10, the major version is just the leading number.) Find out with: 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 relevant indexes. (The output from \d+ tablename in psql is a less useful fallback.)

• 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, SETTINGS). (Before Postgres 12 just EXPLAIN (BUFFERS, ANALYZE).)
  Consider pasting plans on explain.depesz.com, and include the resulting link.

• Link to an online demo at db<>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.