An execution plan shows how PostgreSQL intends to run a query: the operations chosen, their estimated costs, and the access methods used. For a slow query, start with EXPLAIN rather than guessing which index to add. The planner considers possible plans, uses statistics and configuration to cost them, and chooses the lowest-cost candidate. section 6.8
A common mistake is treating every sequential scan as a reason to add a broad index. In a support-ticket table with roughly 500,000 rows but only 250 open tickets, counting open tickets initially uses a sequential scan and filters on status = 10. A normal status index changes this to an Index Only Scan and improves response time, but it also indexes the many closed rows that the query does not need. 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 exposes the costly path: Aggregate receives rows from a Seq Scan, which reads tickets and applies the status filter afterward. section 1.5.2
Read the plan in terms of the work it predicts. A Seq Scan reads the table and then filters; an Index Only Scan can satisfy the lookup through an index. For highly selective, stable predicates, a partial index can keep only relevant entries. An index restricted to status = 10 shrinks from 3408 kB to 16 kB in the ticket scenario while retaining an Index Only Scan. Smaller indexes use less disk, require less traversal, and impose less update work on INSERT and UPDATE operations. section 1.5.2
Use EXPLAIN ANALYZE when estimates alone are insufficient. Unlike EXPLAIN, it executes the statement and reports actual time for each component. That makes it useful for diagnosing slow statements, but dangerous for data-changing SQL: it applies the changes. A BEGIN and ROLLBACK wrapper can contain the changes, although a large rolled-back operation may still require vacuuming. For recurring slow queries, auto_explain can log execution plans; collecting plans too broadly can itself affect performance. section 6.8
Use plans as a performance workflow:
- Run EXPLAIN on a reported slow query and identify the selected scan, filter, and index access path. section 1.5.2 section 6.13
- Compare the plan with the query predicate and data distribution before creating an index. section 1.5.2
- Use EXPLAIN ANALYZE for actual component timings, while protecting data-changing statements from unintended effects. section 6.8
- Review indexes as costs as well as accelerators: unnecessary indexes consume disk and slow writes. section 1.5.2 section 6.13
Counting the small active set
For SELECT count(*) FROM support.tickets WHERE status = 10, EXPLAIN first reports Seq Scan followed by a filter. A full status index produces an Index Only Scan, but a partial index defined with WHERE status = 10 keeps the same access strategy with far fewer indexed rows. The lesson is not “index every filter,” but “verify the plan and index the subset the workload actually needs.” section 1.5.2
