An index-only scan answers a query using index contents instead of fetching table rows. It is especially effective when the query selects only indexed columns and its predicates match the index. section 2.9 section 1.5.2
Counting open tickets
A sequential scan counts 250 open tickets in about 110 ms. Adding an index on status changes the plan to an Index Only Scan and reduces the query to about 3.7 ms. Because only status = 10 matters and open tickets represent roughly 0.05% of the table, a partial index with WHERE status = 10 shrinks the index from 3408 kB to 16 kB and reduces execution to about 0.8 ms. section 1.5.2
Use the execution plan to verify whether the optimization is actually helping:
- Run EXPLAIN before and after creating the index; look for Index Only Scan rather than Seq Scan. section 1.5.2 section 6.10
- Select only indexed columns when possible. Adding non-indexed columns makes PostgreSQL fetch table rows, producing an Index Scan instead. section 2.9
- Prefer a partial index when the query repeatedly targets a small, stable subset; smaller indexes reduce traversal, disk use, and write-update work. section 1.5.2
- Do not assume a foreign key creates an index on its referencing column; adding one can convert a sequential scan into an index-only scan. section 6.10
QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=4.16..4.17 rows=1 width=8) -> Index Only Scan using tickets_status_idx on tickets (cost=0.14..4.16 rows=1 width=0) (2 rows)
Inspect the compact plan produced by the partial index and confirm that the count uses an Index Only Scan. section 1.5.2
