Indexing Strategies

Indexes are the single most impactful tool for query performance. This lesson covers B-tree, GIN, GiST, and hash indexes, plus partial indexes, expression indexes, covering indexes, and how to use EXPLAIN ANALYZE to verify your choices.

---

Why Indexes Matter

Without an index, PostgreSQL must perform a sequential scan — reading every row in the table. With millions of rows, this is unacceptably slow.

-- Without an index: sequential scan O(n)
SELECT * FROM orders WHERE customer_id = 12345;
-- Seq Scan on orders  (cost=0.00..25000.00 rows=50 width=120)
--   Filter: (customer_id = 12345)

-- With an index: index scan O(log n)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- Index Scan using idx_orders_customer_id  (cost=0.42..8.44 rows=50 width=120)

---

B-tree Indexes (Default)

B-tree is the default index type. It supports equality (=) and range (<, >, BETWEEN, <=, >=) queries.

-- Single-column B-tree
CREATE INDEX idx_orders_date ON orders (order_date);

-- Multi-column B-tree (column order matters!)
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

Multi-Column Index Order