writing-performant-queries

Writing Performant Queries

Required diagnostic sequence

Follow these steps in order. If the user already provides the query and plan, start at step 2. Never propose an index before identifying which query is slow and inspecting evidence from its plan.

1. Find the expensive query

Use pg_stat_statements to rank normalized SQL before tuning anything. total_exec_time finds aggregate database load; mean_exec_time finds individually slow calls. Compare a defined time window and note when statistics were reset.

SELECT queryid, calls, total_exec_time, mean_exec_time, rows,
       left(query, 200) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

If it is not installed, add pg_stat_statements to the existing comma-separated shared_preload_libraries, restart PostgreSQL, and create the extension in each database. Do not guess from a generic “the API is slow” report.

2. Inspect that query's plan safely

Use plain EXPLAIN first; it plans but does not execute the statement. Use EXPLAIN (ANALYZE, BUFFERS) only when executing the query is safe and representative.

Warning: EXPLAIN ANALYZE executes the statement. Never run it casually on a production INSERT, UPDATE, or DELETE; it performs writes, takes locks, and can trigger side effects. Prefer staging or a safe read-only reproduction for write queries.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks
WHERE tenant_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Read nodes from the inside out. Compare estimated rows with actual rows and inspect loops, buffer reads, sorts, and rows removed by filters. Estimated cost is not elapsed milliseconds.

3. Check statistics before changing indexes

Always check for stale or insufficient planner statistics before assuming an index is missing. A large estimated-versus-actual row mismatch is the signal. Run ANALYZE on the affected table, then inspect the plan again:

ANALYZE tasks;