Module Review: Indexing Strategies

Congratulations on completing the Indexing Strategies module! You’ve moved beyond “just add an index” to understanding the internal structures and advanced optimizations that make Postgres unique.

1. Key Takeaways

  • B-Tree is King: Use it for 95% of queries (Equality, Range, Sorting). It’s the only index that enforces uniqueness.
  • GIN for Composites: Use GIN for JSONB containment (@>), Arrays (&&), and Full Text Search (@@). It maps keys to lists of TIDs.
  • GiST for Spatial/Custom: Use GiST for Geometric data (PostGIS), Ranges, and Nearest Neighbor (<->) searches.
  • Partial Indexes Save Space: Use a WHERE clause in your index definition to index only the rows that matter (e.g., WHERE status = 'active').
  • Covering Indexes for Speed: Use INCLUDE to add payload columns to leaf nodes, enabling Index-Only Scans.
  • VACUUM Matters: Index-Only Scans rely on the Visibility Map, which is updated by VACUUM.

2. Cheat Sheet: Which Index?

Data Type Query Pattern Recommended Index Example
Integer / Text Equality (=) B-Tree CREATE INDEX ON table (col);
Integer / Text Range (<, >) B-Tree CREATE INDEX ON table (col);
Text Prefix (LIKE 'abc%') B-Tree CREATE INDEX ON table (col text_pattern_ops);
JSONB Containment (@>) GIN CREATE INDEX ON table USING GIN (col);
JSONB Extract Key (->>) B-Tree (Expression) CREATE INDEX ON table ((col->>'key'));
Array Overlap (&&) GIN CREATE INDEX ON table USING GIN (col);
Geometry Intersection (&&) GiST CREATE INDEX ON table USING GIST (col);
Geometry Nearest (<->) GiST CREATE INDEX ON table USING GIST (col);
Full Text Match (@@) GIN CREATE INDEX ON table USING GIN (to_tsvector(...));

3. Flashcards

Test your understanding of the core concepts.

What index type should you use for JSONB containment queries (@>)?

(Click to reveal)

GIN (Generalized Inverted Index). It extracts keys/values and stores them in an inverted structure for efficient lookup.

What is the main requirement for an Index-Only Scan?

(Click to reveal)

A Covering Index (containing all required columns) AND a clean Visibility Map (so Postgres knows tuples are visible without checking the heap).

How does a Partial Index differ from a regular index?

(Click to reveal)

It includes a WHERE clause (e.g., WHERE status='active'), indexing only a subset of rows to save space and speed up writes.

Why does Postgres check the Heap even after finding a row in the Index?

(Click to reveal)

MVCC (Multi-Version Concurrency Control). The index doesn't store visibility info, so Postgres must check the heap to see if the row is visible to the current transaction.

Which index type supports Nearest Neighbor (K-NN) searches?

(Click to reveal)

GiST (Generalized Search Tree). It uses bounding boxes and can efficiently find points closest to a given location.

Why is B-Tree lookup O(log N) in practice?

(Click to reveal)

Because of high fan-out (hundreds of pointers per node). Even with billions of rows, the tree is only 3-4 levels deep.

How does setting a lower FILLFACTOR help performance?

(Click to reveal)

It leaves free space on pages, enabling HOT (Heap-Only Tuple) updates. This avoids index updates and keeps the Visibility Map clean.

4. Next Steps

Now that you’ve mastered indexing, it’s time to look at how to write queries that actually use them effectively.