pumarogie 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;
Raise a column's statistics target only when measured skew or correlation still produces bad estimates. A sequential scan may be the correct plan for a small table or a query returning a large fraction of the rows.
Never use enable_seqscan=off or another global planner override as the fix. Diagnose estimates, selectivity, and index column order instead.
4. Change the query or add the smallest useful index
Derive indexes from the identified query's actual predicates and ordering. For equality filters followed by ordering, put equality columns first and the ordered column next:
CREATE INDEX CONCURRENTLY idx_tasks_tenant_status_created
ON tasks (tenant_id, status, created_at DESC);
This index supports tenant_id = ... AND status = ... ORDER BY created_at DESC LIMIT ... without a separate sort. Do not propose separate single-column indexes as the primary answer for this combined access path.
Before creating it, inspect existing indexes and do not add one already covered by an equivalent left prefix. Match composite column order to the query: an index on (tenant_id, created_at) only helps predicates that can use its leftmost ordering. B-trees can scan in either direction; explicit direction matters most for mixed-direction ordering.
Use CREATE INDEX CONCURRENTLY for a live table and follow writing-safe-migrations for lock_timeout, transaction restrictions, and invalid-index cleanup. Every index consumes disk and adds write and vacuum cost.
5. Verify the result
Re-run the same plan and workload window. Confirm improved actual time and buffers without unacceptable write cost. Optimize total workload cost, not one anecdotal call, and remove redundant indexes only after observing a representative workload.
Additional rules
- Index selective filters and join keys used by hot queries; foreign keys do not automatically index referencing columns.
- Parameterize values. Diagnose generic prepared plans before changing planner settings.
- Treat high sequential-scan counts as a signal, not proof; reporting and bulk reads often should scan.
- For unbounded time-series data whose hot queries prune by time, consider partitioning with
postgres-advanced-patterns.