Advanced indexes

Draft

After the first index, the next lesson is shape. Useful indexes depend on how selective the filter is and how the query is written.

Low selectivity

An index on status exists, but paid matches most rows, so a scan can still be cheaper.

Loaded Database:advanced-indexes.database-init

SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid';

paid_orders
10800

Aggregate  (cost=277.00..277.01 rows=1 width=8)
  ->  Seq Scan on orders  (cost=0.00..250.00 rows=10800 width=0)
        Filter: (status = 'paid'::text)

Composite index

This query matches the status, created_at index shape: filter first, then order.

Loaded Database:advanced-indexes.database-init

SELECT order_number, status, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 5;

order_number	status	created_at
ORD-11990	pending	2026-01-09T07:50:00.000Z
ORD-11980	pending	2026-01-09T07:40:00.000Z
ORD-11970	pending	2026-01-09T07:30:00.000Z
ORD-11960	pending	2026-01-09T07:20:00.000Z
ORD-11950	pending	2026-01-09T07:10:00.000Z

Limit  (cost=0.29..1.70 rows=5 width=22)
  ->  Index Scan using orders_status_created_at_idx on orders  (cost=0.29..305.54 rows=1080 width=22)
        Index Cond: (status = 'pending'::text)

Partial index

A partial index stores only failed orders, which keeps a rare lookup small.

Loaded Database:advanced-indexes.database-init

SELECT order_number, status, total_cents
FROM orders
WHERE status = 'failed'
  AND order_number = 'ORD-9000';

order_number	status	total_cents
ORD-9000	failed	10000

Index Scan using orders_failed_order_number_idx on orders  (cost=0.14..8.16 rows=1 width=18)
  Index Cond: (order_number = 'ORD-9000'::text)

What We Learned

Selectivitydescribes how much a filter narrows the table.
Composite indexindexes multiple columns in a specific order.
Partial indexindexes only rows that satisfy a predicate.
Planner costis why PostgreSQL can ignore an index when a scan is cheaper.