LLM Best Practices

How to optimize a Postgres query

4 min read

View markdown source

Overview

Most Postgres performance problems fall into one of four categories: missing index, stale statistics, too-large result set, or wrong join order. This guide takes a query from identified-as-slow to verified-as-fast using pg_stat_statements, EXPLAIN ANALYZE, and a targeted index. For the deep reference on reading plans see postgres-explain; for index design rules see postgres-indexes.

Prerequisites

  • Postgres 14 or newer. Postgres 16+ exposes the pg_stat_io view which adds I/O breakdown; the steps work on older versions without it.
  • pg_stat_statements extension enabled. See debug-postgres-slow-query step 1 for setup.
  • A connection with pg_read_all_stats privilege or superuser access.
  • Production-shaped data in the database. A table with ten rows will not reproduce a production plan.

Steps

1. Identify the top offenders

Sort by total_exec_time to find queries that cost the most cumulative time, not just the slowest single execution.

SELECT
  left(query, 80) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

Pick a query with high total_ms and many calls. That is where a fix pays off most. Note the normalized query text; you will need real parameter values next.

2. Capture a representative execution

Get the actual query with real parameters from the application log or from log_min_duration_statement:

# postgresql.conf — temporary for diagnosis only
log_min_duration_statement = 100   # log queries over 100 ms

Reload config without restart: SELECT pg_reload_conf();

3. Run EXPLAIN ANALYZE

Execute the plan with real parameters. Always use ANALYZE (which runs the query) and BUFFERS (which shows cache hits vs disk reads).

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, status, total_cents
FROM orders
WHERE customer_id = 4291
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Read the output bottom-up: leaf nodes run first. Key signals:

  • Seq Scan on a large table with a selective predicate: missing index.
  • actual rows far exceeds rows estimate: stale statistics; run ANALYZE.
  • Buffers: shared read=N still high on the second run: working set does not fit in shared_buffers.

The postgres-explain page decodes every node type.

4. Add a targeted index

For the example query, the predicate filters on customer_id and status, then sorts by created_at. A composite index ordered equality-first, range-last covers the predicate and avoids a sort.

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC)
INCLUDE (total_cents);

Rules:

  • Use CONCURRENTLY in production to avoid a table lock.
  • Equality predicates come before range predicates in the column list.
  • INCLUDE adds payload columns so the index covers the projection; the plan becomes an Index Only Scan.
  • One index per query pattern; do not add a redundant index if the leftmost columns already appear in an existing index.

See postgres-indexes for partial indexes, expression indexes, and covering index tradeoffs.

5. Re-run and verify the plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents
FROM orders
WHERE customer_id = 4291
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

The new plan should show:

Index Only Scan using orders_customer_status_created_idx on orders
  (actual time=0.123..0.456 rows=20 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=4 read=0

Heap Fetches: 0 confirms the index is covering. read=0 on a warm cache confirms the working set fits. If read is still non-zero, run VACUUM ANALYZE orders; the visibility map may not be current. See postgres-vacuum.

6. Measure under real load

Reset pg_stat_statements and let the application run for 30 minutes:

SELECT pg_stat_statements_reset();

Re-query the top 10. The fixed query should drop off the list. If it reappears, the planner is choosing a different plan under different parameter values. Capture those parameters and repeat the cycle.

Verify it worked

-- Plan uses the new index with low actual time.
EXPLAIN (ANALYZE, BUFFERS) <the query>;
 
-- Query has dropped from the top 10 slow list.
SELECT query FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
 
-- No unexpected table locks from the CREATE INDEX.
SELECT pid, query FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Common errors

  • Index is created but not used. The predicate is not selective enough; Postgres prefers a seq scan. Run ANALYZE orders to refresh statistics, then recheck.
  • CONCURRENTLY fails with “in a transaction”. Run CREATE INDEX CONCURRENTLY outside a BEGIN block.
  • Plan is great in dev, slow in prod. Statistics differ between environments. Compare pg_stats.n_distinct and run ANALYZE on the production table.
  • mean_exec_time is low but p99 is high. A lock contention spike, not a plan problem. Check pg_locks and pg_stat_activity for blocking queries.
  • Index bloat after adding the index. Monitor with pg_stat_user_indexes; if idx_tup_read is near zero after a week, the index is unused and wastes write overhead. Drop it.

Graph View

Backlinks