B-tree and composite indexes

Without indexes, every query reads EVERY row (a sequential scan). An index is like a book's index: instead of reading every page, you look up the page number directly. The right index can turn a 5-second query into a 5-millisecond query.

Index: from sequential scan to index scan

Without an index, PostgreSQL reads every row in the table (sequential scan). An index is like a book index: a sorted structure (B-tree by default) that maps values to row locations. Instead of scanning 99k rows, the database jumps directly to matching rows.

before_index.sql
sql
-- Before index: see the Seq Scan
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_status = 'processing';

EXPLAIN ANALYZE shows the actual query plan and execution time. Without an index, you will see "Seq Scan", which means it is reading every row.

Now let us add an index and re-run the same query. Watch how the plan changes from Seq Scan to an Index Scan.

create_index.sql
sql
-- Create an index and re-run
CREATE INDEX idx_orders_status ON orders(order_status);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_status = 'processing';

-- Now you should see "Index Scan" or "Bitmap Index Scan"

After creating the index, the same query uses an Index Scan. The execution time drops dramatically.

A composite index covers multiple columns. The column order matters because the index works like a phone book sorted by last name, then first name.

composite_index.sql
sql
-- Composite index: column order matters!
CREATE INDEX idx_orders_status_date
  ON orders(order_status, order_purchase_timestamp);

-- This index helps:
SELECT * FROM orders WHERE order_status = 'delivered'
  AND order_purchase_timestamp > '2018-01-01';

-- This index also helps (leftmost prefix):
SELECT * FROM orders WHERE order_status = 'delivered';

-- This index does NOT help:
SELECT * FROM orders
  WHERE order_purchase_timestamp > '2018-01-01';

Composite indexes work left-to-right. The index on (status, date) helps queries filtering on status or (status + date), but NOT date alone.

Quiz: Quiz

Loading practice…