Query Optimization Discussion
5 exercises — practise the English vocabulary for query performance discussions: EXPLAIN scan types, N+1 anti-pattern, query plan cost units, buffer hit rates, and index-only scan terminology.
Query optimization vocabulary quick reference
- Seq Scan: reads every row in the table — expensive on large tables with selective WHERE
- Index Scan: traverses index, then fetches heap pages — good for low-selectivity (<5%) queries
- N+1: 1 query for a collection + 1 per item; fix with eager loading or batched IN query
- COST in EXPLAIN: dimensionless planner units — not milliseconds
- Buffer hit: page served from shared_buffers in memory — no disk read required
- Index Only Scan: satisfies query entirely from the index — no heap fetch
Question 1 of