When a PostgreSQL query is slow, start by separating the plan from the observed run. EXPLAIN shows the query plan PostgreSQL intends to use. EXPLAIN ANALYZE executes the statement and adds actual timings for each component, making it useful for testing whether the selected plan explains the delay. section 6.8
A common mistake is treating EXPLAIN ANALYZE as a harmless inspection command. Unlike EXPLAIN, it runs the SQL. On an INSERT, UPDATE, DELETE, or other data-changing statement, its effects are applied. For exploratory work, place it between BEGIN and ROLLBACK, while recognizing that a large rolled-back insert can still create vacuuming work. section 6.8
Find the scan behind a slow ticket count
A count of 250 open tickets among 500,000 tickets initially uses a Seq Scan and filters on status = 10. The plan exposes why the correct query is slow: PostgreSQL must scan the table. After indexing status, the plan changes to an Index Only Scan. Replacing the broad index with a partial index for status = 10 keeps that scan type while sharply reducing index size. section 1.5.2
EXPLAIN SELECT count(*)
FROM support.tickets
WHERE status = 10;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=9927.28..9927.29 rows=1 width=8)
-> Seq Scan on tickets (cost=0.00..9927.28 rows=1 width=0)
Filter: (status = 10)
(3 rows)
This plan identifies the Aggregate, the Seq Scan on tickets, and the status filter—the operations to investigate before adding an index. section 1.5.2
Use EXPLAIN and EXPLAIN ANALYZE as a performance workflow:
- Run EXPLAIN first to inspect the chosen plan without executing the statement. section 6.8
- Use EXPLAIN ANALYZE when actual component timings are needed, with particular care around statements that modify data. section 6.8
- Read plans alongside planner statistics and configuration: PostgreSQL costs possible plans using those inputs and selects the lowest-cost candidate. section 6.8
- Treat an index as a trade-off, not an automatic fix: unnecessary indexes consume disk space and slow writes; inspect plans to verify their value. section 1.5.2 section 6.13
- Use auto_explain selectively to log plans for slow queries; explaining many queries can itself affect performance. section 6.8
Plan reading need not begin with every node detail. A Seq Scan and its filter can already explain a selective query’s poor performance, while an Index Only Scan can confirm a better access path. For growing tables, sequential scans become linearly slower, so plan evidence should guide indexing decisions rather than assumptions that every index helps. section 1.5.2 section 6.13
