An index scan retrieves matching rows through an index instead of examining every table row. PostgreSQL exposes the chosen access method through EXPLAIN, making it possible to verify whether a query uses an index or falls back to a sequential scan. section 4.4
Use execution plans to read an index scan in context:
- An Index Cond identifies the indexed predicate used to locate rows, such as player_id = 250. section 4.4
- A B-tree index ordered by score can scan the highest values first, producing a leaderboard efficiently with ORDER BY score DESC LIMIT 5. section 4.5.1
- Composite indexes depend on column order: an index on (recorded_at, watch_id) supports recorded_at alone or with watch_id, but traditionally not watch_id alone. section 9.7.1
- Partition-local indexes can combine with partition selection, reducing a time-series query to the relevant partition before scanning indexed rows. section 9.7.1
Verify a primary-key index scan
Run EXPLAIN on a dashboard lookup: SELECT username, level, score FROM game.player_stats WHERE player_id = 250; The plan reports “Index Scan using player_stats_pkey” with “Index Cond: (player_id = 250).” EXPLAIN ANALYZE adds observed timing, while EXPLAIN (analyze, buffers on) also reports buffer activity. section 4.4
QUERY PLAN
--------------------------------------------------------------
Index Scan using player_stats_pkey on player_stats
➥ (cost=0.29..8.30 rows=1 width=18)
Index Cond: (player_id = 250)
(2 rows)
Inspect the complete execution plan for the primary-key lookup and compare its index condition with the query predicate. section 4.4
