indexes

Your query is correct but takes 40 seconds. Nine times out of ten, the fix is an index. This topic is about what they actually are, so you know when to add one and when not to.

what an index is

Think of the index at the back of a textbook. Want the page about "joins"? You don't read the whole book, you check the index, it says page 142, you go straight there.

Without an index the database does a sequential scan, reads every single row and checks it. Fine for 8 customers. Terrible for 80 million.

An index is a separate sorted structure (usually a B-tree) that points to where rows live. With it, finding email = 'x' takes a few jumps instead of reading everything.

creating one

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Now WHERE customer_id = 3 and joins on customer_id can use it.

Primary keys and UNIQUE constraints get an index automatically. Foreign keys do not in postgres (mysql does). So orders.customer_id needs one manually, and this is a super common missing index.

what indexes help with

• WHERE col = value
• WHERE col > value, ranges, BETWEEN
• JOIN ... ON col = ...
• ORDER BY col (already sorted, no extra sort needed)
• WHERE col LIKE 'abc%' (starts-with only, not '%abc')

composite indexes (more than one column)

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Column order matters a lot. It's sorted by customer_id first, then order_date inside each customer. Like a phone book sorted by last name then first name.

This index helps:

• WHERE customer_id = 1
• WHERE customer_id = 1 AND order_date > '2024-03-01'
• WHERE customer_id = 1 ORDER BY order_date

It doesn't really help:

• WHERE order_date > '2024-03-01' alone, because you can't use a phone book sorted by last name to find everyone named "Rahul".

Rule of thumb: equality columns first, range/sort column last.

covering indexes

If the index has every column the query needs, the database doesn't even look at the table. "Index only scan".

CREATE INDEX idx_orders_cust_amount ON orders (customer_id) INCLUDE (amount);
-- now this can be answered from the index alone:
SELECT customer_id, SUM(amount) FROM orders WHERE customer_id = 1 GROUP BY customer_id;

partial indexes

Index only some rows:

CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';

Small and fast if you're always querying pending orders.

when indexes DON'T get used

• functions on the column, WHERE LOWER(email) = '...' can't use an index on email (see expression indexes topic)
• leading wildcard, LIKE '%gmail.com'
• type mismatch, comparing an int column to a text value
• small tables, scanning 8 rows is faster than using an index, the planner knows this
• the query returns a big chunk of the table anyway, reading everything is cheaper

the cost of indexes

They're not free:

• every INSERT/UPDATE/DELETE has to update every index on that table
• they take disk space
• too many on a write-heavy table makes writes slow

So don't index every column "just in case". Index what your actual slow queries filter, join, and sort on.

seeing what's there

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
DROP INDEX idx_orders_pending;

Use EXPLAIN to check if your index is being used, that's the next topic.

stuff that trips people up

• forgetting foreign keys aren't auto indexed in postgres.
• wrong column order in composite indexes.
• wrapping indexed columns in functions.
• adding 15 indexes and wondering why inserts got slow.

practice

Check queries.sql.

-- indexes
-- run sample-data.sql first
-- note: with only 10 rows postgres will usually still pick a seq scan, it's cheaper.
-- the generate_series bit below makes a big table so you can actually see indexes kick in.

-- examples ---------------------------------------------------------

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- make a big table to play with
DROP TABLE IF EXISTS big_orders;
CREATE TABLE big_orders AS
SELECT
  g AS order_id,
  (random() * 10000)::int AS customer_id,
  DATE '2024-01-01' + (random() * 365)::int AS order_date,
  (random() * 5000)::numeric(10,2) AS amount
FROM generate_series(1, 500000) g;
ANALYZE big_orders;

-- before index
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 42;

CREATE INDEX idx_big_cust ON big_orders (customer_id);

-- after index, look for "Index Scan" or "Bitmap Index Scan" and compare the time
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 42;


-- practice ---------------------------------------------------------

-- 1. on big_orders, check how long WHERE order_date = '2024-05-01' takes. add an index. check again.
-- 2. you often run: WHERE customer_id = ? AND order_date >= ? ORDER BY order_date.
--    what index would you create?
-- 3. does an index on (customer_id, order_date) help WHERE order_date = '2024-05-01'? test it.
-- 4. create a partial index for orders with amount > 4500 and check it's used
-- 5. clean up: drop all the indexes you made and the big_orders table


-- answers ----------------------------------------------------------

-- 1
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE order_date = '2024-05-01';
CREATE INDEX idx_big_date ON big_orders (order_date);
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE order_date = '2024-05-01';

-- 2
CREATE INDEX idx_big_cust_date ON big_orders (customer_id, order_date);
-- equality column first, range/sort column second

-- 3
DROP INDEX idx_big_date;
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE order_date = '2024-05-01';
-- mostly no, it can't jump to a date without knowing the customer_id first.
-- postgres might still scan the index but it's way less efficient.

-- 4
CREATE INDEX idx_big_large ON big_orders (amount) WHERE amount > 4500;
EXPLAIN SELECT * FROM big_orders WHERE amount > 4800;

-- 5
DROP INDEX IF EXISTS idx_big_cust, idx_big_cust_date, idx_big_large, idx_big_date;
DROP INDEX IF EXISTS idx_orders_customer_id, idx_orders_customer_date;
DROP TABLE big_orders;