Chapter 23 — Quiz

14 questions. Answers at the bottom.

Multiple choice

Q1. An index is: - A) A copy of the whole table - B) A separate sorted data structure to find rows without scanning the whole table - C) A type of constraint - D) A backup

Q2. An indexed lookup is roughly _ vs. a full scan's _: - A) O(n) / O(log n) - B) O(log n) / O(n) - C) O(1) / O(n) - D) O(n²) / O(n)

Q3. PostgreSQL's default index type is: - A) Hash - B) B-tree - C) GIN - D) BRIN

Q4. The #1 cause of slow joins is: - A) Too many columns - B) An unindexed join column - C) Using aliases - D) A LEFT JOIN

Q5. Which index type for JSONB containment (@>) or full-text search? - A) B-tree - B) Hash - C) GIN - D) BRIN

Q6. In a multi-column index (a, b), which query can use it? - A) Filter on b alone - B) Filter on a, or a + b (leftmost prefix) - C) Any subset - D) None

Q7. WHERE lower(email) = '...' with a plain index on email: - A) Uses the index - B) Cannot use it — needs an expression index on lower(email) - C) Errors - D) Is always fast

Q8. Adding an index makes: - A) Reads and writes both faster - B) Reads faster, writes slower (must maintain the index) - C) Writes faster - D) No difference

Q9. You should usually NOT index: - A) A foreign key used in joins - B) An email you look up constantly - C) A low-cardinality column on a write-heavy table - D) A column you filter and sort by

Q10. A Seq Scan on a large table where you fetch few rows usually signals: - A) Everything is fine - B) A missing index - C) A corrupt table - D) Too many indexes

Q11. CREATE INDEX CONCURRENTLY: - A) Locks the table for writes while building - B) Builds without blocking writes (slower, not in a transaction) - C) Is faster than a normal build - D) Skips validation

True/False

Q12. Indexing every column is a good default for performance. (True / False)

Q13. The optimizer may correctly choose a sequential scan even when an index exists. (True / False)

Short answer

Q14. A query SELECT * FROM orders WHERE customer_id = 4267 is slow on a 100M-row table. Describe how you'd diagnose and fix it, and why the fix works.

---

Answer key

Q1 — B. A separate sorted structure for fast lookups.

Q2 — B. Index ≈ O(log n); full scan = O(n).

Q3 — B. B-tree.

Q4 — B. An unindexed join column forces a scan per the join.

Q5 — C. GIN (composite-value containment, full-text, arrays).

Q6 — B. Leftmost prefix: a, or a+b; not b alone.

Q7 — B. A function on the column needs an expression index on that expression.

Q8 — B. Faster reads, slower writes (and storage + maintenance).

Q9 — C. Low-cardinality + write-heavy + rarely queried → the write tax outweighs benefit.

Q10 — B. Missing index (the classic slow-query smell).

Q11 — B. Non-blocking build; slower and can't be in a transaction.

Q12 — False. Indexes cost write speed/storage/maintenance; index only what you filter/join/sort on.

Q13 — True. If a query returns most of the table, a scan is faster than an index scan, and the optimizer chooses it.

Q14. Run EXPLAIN ANALYZE — you'll see a Seq Scan on orders reading all 100M rows to find the few matching customer_id = 4267. Add CREATE INDEX [CONCURRENTLY] idx_orders_customer_id ON orders (customer_id);. Re-run EXPLAIN ANALYZE; the plan switches to an Index Scan that jumps to the matching rows in O(log n), dropping runtime from seconds to milliseconds. It works because the index lets the database locate the rows by value instead of scanning every row.

Scoring: 12–14 you've got the core performance skill; 9–11 review index types and costs; below 9, do the anchor exercise (A) hands-on.