Skip to content
Ameen Abozaid
Englishالعربية
MID-012

Index internals

  • 6 Lessons
  • 3 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • B-tree
  • hash
  • GIN
  • GiST
  • BRIN
  • composite
  • partial
  • covering indexes

Learn first

3 h of practice (estimate)

Learning outcomes

  1. Explain B-tree index structure and why column order matters in composite indexes.
  2. Choose GIN, GiST, BRIN, or hash indexes for given workloads.
  3. Design partial and covering indexes and explain index-only scans.
  4. Weigh the write and storage cost of indexes.

Lessons

  1. 01How a B-tree index works
  2. 02Composite indexes and column order
  3. 03GIN, GiST, BRIN, hash
  4. 04Partial and covering indexes
  5. 05The cost of indexes
  6. 06Demo: one query, five indexes, five plans

Lab and exercises

Lab

MID-012-LAB1 · Index the order history

Outcomes: 1–4 · Time: ~2 h Load 10 M orders. For five frequent Shop queries (orders by customer newest first, open orders by age, orders by date range, product search by attributes in jsonb, order totals per customer), design the minimum set of indexes. Measure insert time for 100 k rows before and after adding them. Acceptance: each query's plan uses the intended index; write overhead measured and reported.

Exercises (assessment: index design for a new workload)

IDOutcomeTask
MID-012-EX011Draw a three-level B-tree for 12 keys and trace a lookup.
MID-012-EX021Given WHERE a = ? AND b > ? ORDER BY b, choose the order for (a, b) or (b, a) and justify.
MID-012-EX032Choose an index type for: JSON containment, date ranges on a 500 M-row log, tsvector search, plain equality.
MID-012-EX043Write a partial index for the 2% of orders that are unshipped and a query that uses it.
MID-012-EX053Make SELECT customer_id, total_cents for one customer an index-only scan.
MID-012-EX064List unused and duplicate indexes in a database using the statistics views.
MID-012-EX074Measure INSERT throughput with 0, 3, and 8 indexes and summarize.
Show solutions
  • EX01 Root with 2 children, each with leaf pages; the lookup compares at each page and follows one pointer per level.
  • EX02 (a, b): equality column first, then the range/sort column; (b, a) cannot use a to narrow.
  • EX03 GIN; BRIN; GIN on tsvector; B-tree.
  • EX04 CREATE INDEX ... ON orders (created_at) WHERE shipped_at IS NULL; the query must repeat shipped_at IS NULL.
  • EX05 CREATE INDEX ... ON orders (customer_id) INCLUDE (total_cents); then VACUUM and check for Index Only Scan with low Heap Fetches.
  • EX06 Query pg_stat_user_indexes for idx_scan = 0; find indexes with the same leading columns.
  • EX07 Throughput drops as indexes are added (measured values vary by machine); report cost per index.
  • Outcomes 2 and 4 are also tested in the quiz.

Quiz

Outcomes: O1 B-tree and column order · O2 index types · O3 partial and covering · O4 cost.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1A B-tree lookup on millions of rows reads:O1
  2. 2Index (a, b) best serves:O1
  3. 3For WHERE a = ? AND b > ? the best order is:O1
  4. 4Best index for jsonb containment @>:O2
  5. 5BRIN fits best:O2
  6. 6Hash indexes support:O2
  7. 7A partial index is used when:O3
  8. 8INCLUDE columns in an index help:O3
  9. 9An index with idx_scan = 0 over months is:O4
  10. 10Adding indexes mostly makes writes:O4

Practical part

Design indexes for a new workload given as five queries and a write rate; justify order and type. Pass: quiz 8/10 and a design where each query is served and no index is redundant, plus the lab measurements.

Common mistakes

One index per column; wrong composite order; indexes never used.

Assessment

Index design for a new workload.

Career relevance

Query optimization (MID-013) and database scaling (SRC-007).

Type to search levels, phases, modules and lessons.