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

The leftmost prefix rule: a multi-column index on (A, B, C) can be used for:

• Queries on A
• Queries on A, B
• Queries on A, B, C

It cannot efficiently serve queries on B alone or C alone.

-- This index: (customer_id, order_date)
-- Supports:
WHERE customer_id = 123                          -- yes
WHERE customer_id = 123 AND order_date > '2024-01-01'  -- yes
WHERE order_date > '2024-01-01'                  -- NOT efficiently (no leading column)

---

GIN Indexes (Generalised Inverted Index)

Ideal for multi-valued columns: arrays, JSONB, and full-text search.

-- JSONB containment queries
CREATE INDEX idx_products_attrs ON products USING gin (attributes);
SELECT * FROM products WHERE attributes @> '{"colour": "red"}';

-- Array overlap queries
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT * FROM posts WHERE tags && ARRAY['postgresql', 'sql'];

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', body));
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'index & performance');

When to Use GIN

Scenario	GIN?
JSONB containment (@>)	Yes
Array overlap (&&, @>)	Yes
Full-text search (@@)	Yes
Equality/range on scalar	No (use B-tree)

---

GiST Indexes (Generalised Search Tree)

Best for geometric, range, and nearest-neighbour queries.

-- Range queries (e.g. IP ranges, date ranges)
CREATE INDEX idx_reservations_period ON reservations USING gist (
  tsrange(check_in, check_out)
);
SELECT * FROM reservations
WHERE tsrange(check_in, check_out) && tsrange('2024-06-01', '2024-06-15');

-- Geometric data
CREATE INDEX idx_locations_point ON locations USING gist (coordinates);
SELECT * FROM locations
WHERE coordinates <-> point(51.5074, -0.1278) < 0.01
ORDER BY coordinates <-> point(51.5074, -0.1278)
LIMIT 10;

---

Hash Indexes

Optimised for equality-only lookups. Since PostgreSQL 10 they are WAL-logged and crash-safe.

CREATE INDEX idx_sessions_token ON sessions USING hash (session_token);

-- Fast for:
SELECT * FROM sessions WHERE session_token = 'abc123xyz';

-- Cannot use for:
-- WHERE session_token > 'abc'   (no range support)

Tip: B-tree also handles equality efficiently. Use hash indexes only when you are certain you will never need range queries on that column and want slightly lower storage overhead.

---

Partial Indexes

An index on a subset of rows, defined by a WHERE clause. Smaller, faster, and cheaper to maintain:

-- Only index active orders (most queries filter for active)
CREATE INDEX idx_orders_active ON orders (customer_id, order_date)
WHERE status = 'active';

-- Only index unprocessed items
CREATE INDEX idx_queue_pending ON job_queue (priority, created_at)
WHERE processed_at IS NULL;