Lab: covering indexes, overlapping indexes and foreign keys

Before you start

The test data: 20,000 customers and two million orders. Our numbers come from PostgreSQL 18.3 on a laptop, second run of each query, with SET max_parallel_workers_per_gather = 0;.

SELECT setseed(0.3);
CREATE TABLE customers (id int PRIMARY KEY, name text);
INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 20000) g;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers ON DELETE CASCADE,
  status      text NOT NULL,
  ordered_at  timestamptz NOT NULL,
  total       numeric(10,2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + (random() * 19999)::int,
       (ARRAY['paid','shipped','cancelled','refunded'])[1 + (random() * 3)::int],
       timestamptz '2024-01-01' + random() * interval '730 days',
       (random() * 500)::numeric(10,2)
FROM generate_series(1, 2000000) g;
VACUUM ANALYZE;

1. A covering index

“How much has customer 4242 spent?” needs total, which a plain index on customer_id does not have, so every matching row must be fetched from the table.

EXPLAIN (ANALYZE, BUFFERS) SELECT sum(total) FROM orders WHERE customer_id = 4242;

CREATE INDEX o_cust ON orders (customer_id);                          -- measure, then drop
CREATE INDEX o_cust_incl ON orders (customer_id) INCLUDE (total);
VACUUM orders;

The covering index answered from the index alone: 88 orders’ totals in 5 pages. But it is more than four times larger. Repeated customer ids are normally stored once by B-tree deduplication, and indexes with INCLUDE columns can never use it. A covering index is a trade: faster for this one query, heavier for every insert and in memory.

2. An index that adds nothing

Teams often add (customer_id) “for lookups” next to an existing (customer_id, ordered_at).

SELECT id, total FROM orders WHERE customer_id = 4242;

The two-column index serves a query on its first column just as well. A separate single-column index would cost another index’s worth of writes on every insert and gain nothing. Check for such pairs: an index that is a leading prefix of another is usually redundant.

3. The foreign key nobody indexed

orders.customer_id references customers. Deleting a customer has to find that customer’s orders (here, to cascade the delete), and PostgreSQL does not create an index on the referencing column automatically.

\timing on
BEGIN; DELETE FROM customers WHERE id BETWEEN 100 AND 149; ROLLBACK;
CREATE INDEX orders_customer_id ON orders (customer_id);
BEGIN; DELETE FROM customers WHERE id BETWEEN 100 AND 149; ROLLBACK;

About 330 times slower without it: for each deleted customer, PostgreSQL scanned all two million orders to find theirs. The same happens on ON DELETE RESTRICT checks and when a referenced key is updated.

Clean up

DROP TABLE orders, customers;

Check yourself

1. Why was the covering index four times bigger than the plain one?

   Show answer

   It stores total in every entry, and indexes with INCLUDE columns cannot use deduplication, so the repeated customer_id values are stored once per row instead of once per value.

   I could explain this without looking

2. You find indexes on (tenant_id) and (tenant_id, created_at). What do you do?

   Show answer

   Check that nothing needs (tenant_id) alone (for example a unique constraint), then drop it: the two-column index serves queries on tenant_id just as well.

   I could explain this without looking

3. Why did deleting customers take seconds without an index on orders.customer_id?

   Show answer

   Each delete has to find the orders that reference the customer. With no index on the referencing column, that is a full scan of the orders table for every deleted customer.

   I could explain this without looking