Writing Performant Queries

---
name: writing-performant-queries
description: Guides evidence-first Postgres query diagnosis when an API or database becomes slow, an expensive query must be found, EXPLAIN must be used safely, the planner ignores an index, statistics may be stale, or a filter-and-sort query needs an index.
---

# 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.

```sql
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.

```sql
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:

```sql
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:

```sql
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`.