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.

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

Loading plan...

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

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

Loading plan...

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

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

Loading plan...