5 exercises on database query optimization vocabulary.
0 / 5 completed
1 / 5
What does the EXPLAIN ANALYZE command reveal?
EXPLAIN ANALYZE: unlike plain EXPLAIN (estimated), ANALYZE actually runs the query and reports real row counts and time per node. Discrepancies between estimated and actual rows reveal stale statistics.
2 / 5
What is a sequential scan and when is it preferable?
Sequential scan: reads pages in order without an index. For queries returning >5–10% of a table, the random I/O of an index scan is slower than a sequential pass. The planner weighs selectivity when choosing.
3 / 5
What is an N+1 query problem?
N+1 problem: fetching 100 orders and then issuing 100 separate queries for each order's customer is an N+1. Fix it with a JOIN, eager loading, or a batch query that fetches all customers in one round trip.
4 / 5
What does query parameterization improve beyond security?
Plan caching: a parameterized query is parsed and planned once. Subsequent executions with different parameter values reuse the cached plan, saving planning overhead — especially critical for high-throughput OLTP workloads.
5 / 5
What is a covering index?
Covering index: if SELECT a, b FROM t WHERE c = 1 and the index covers (c, a, b), the engine satisfies the query from the index alone — an index-only scan — eliminating expensive heap fetches.