Lab: measure what an index buys and costs

Before you start

You need a PostgreSQL you can throw away: a local install, a Docker container, or a scratch database. This lab creates a few tables of up to a million rows (about 300 MB in total) and installs the pgstattuple extension, which ships with PostgreSQL.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
\timing on

Write your numbers down as you go. Each step shows ours for comparison, from PostgreSQL 18.3 on an Apple Silicon laptop. Your page counts should be close; your times will not be.

1. What a lookup reads

Build the table from the first page and look one row up, before and after adding an index.

CREATE TABLE users (id bigint, email text, created_at timestamptz);
INSERT INTO users
SELECT g, 'user' || g || '@example.com', now() - g * interval '1 second'
FROM generate_series(1, 1000000) g;
VACUUM ANALYZE users;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'user777777@example.com';
CREATE INDEX users_email_idx ON users (email);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'user777777@example.com';

Add up shared hit and shared read on the top node’s Buffers line.

	Our result	Yours
Pages read without the index	8,334
Pages read with the index	4

2. What the index weighs

SELECT pg_size_pretty(pg_relation_size('users'))           AS table_size,
       pg_size_pretty(pg_relation_size('users_email_idx')) AS index_size;
SELECT tree_level, internal_pages, leaf_pages, avg_leaf_density
FROM pgstatindex('users_email_idx');

	Our result	Yours
Table	65 MB
Index on email	39 MB
tree_level (levels below the root)	2

An index on one text column weighs more than half as much as the whole table here, because each entry carries the full email address plus a row pointer.

3. What each index costs on writes

This step inserts the same 200,000 rows into a fresh table with 0, 1, 3 and 5 indexes, and measures both time and WAL: the bytes PostgreSQL writes to its log for crash safety. WAL is a steadier measure of work than time on a laptop.

CREATE TABLE orders_src AS
SELECT g AS id, (random() * 100000)::int AS customer_id, 'user' || g || '@example.com' AS email,
       now() - g * interval '1 second' AS created_at,
       (ARRAY['paid', 'pending', 'refunded'])[1 + g % 3] AS status
FROM generate_series(1, 200000) g;

CREATE FUNCTION insert_cost(n_indexes int) RETURNS TABLE (indexes int, ms numeric, wal_mb numeric)
LANGUAGE plpgsql AS $$
DECLARE cols text[] := ARRAY['id', 'email', 'customer_id', 'created_at', 'status'];
        t0 timestamptz; l0 pg_lsn;
BEGIN
  DROP TABLE IF EXISTS orders;
  CREATE TABLE orders (LIKE orders_src);
  FOR i IN 1..n_indexes LOOP EXECUTE format('CREATE INDEX ON orders (%I)', cols[i]); END LOOP;
  CHECKPOINT;
  l0 := pg_current_wal_insert_lsn(); t0 := clock_timestamp();
  INSERT INTO orders SELECT * FROM orders_src;
  indexes := n_indexes;
  ms := round(extract(epoch FROM clock_timestamp() - t0) * 1000);
  wal_mb := round(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), l0) / 1048576.0, 1);
  RETURN NEXT;
END $$;

SELECT * FROM insert_cost(0) UNION ALL SELECT * FROM insert_cost(1)
UNION ALL SELECT * FROM insert_cost(3) UNION ALL SELECT * FROM insert_cost(5);

CHECKPOINT needs a superuser or the pg_checkpoint role. On a managed database where you have neither, delete that line; the numbers get noisier but the trend holds.

Run the last statement three times and keep the middle value of each row. Ours:

Indexes	Time	WAL	Yours
0	248 ms	20 MB
1	463 ms	33 MB
3	1,916 ms	69 MB
5	2,478 ms	97 MB

Updates pay too. An UPDATE that cannot be done as a heap-only update needs a new entry in every index on the table, including indexes on columns it did not change.

4. Break it: random keys

Insert one million keys into two indexed tables: one with random UUIDs, one with time-ordered UUIDs. Both column types are 16 bytes; only the order of the values differs.

CREATE TABLE u_v4 (k uuid); CREATE INDEX ON u_v4 (k);
CREATE TABLE u_v7 (k uuid); CREATE INDEX ON u_v7 (k);
INSERT INTO u_v4 SELECT uuidv4() FROM generate_series(1, 1000000);
INSERT INTO u_v7 SELECT uuidv7() FROM generate_series(1, 1000000);

SELECT 'uuidv4' AS keys, leaf_pages, avg_leaf_density, pg_size_pretty(index_size) FROM pgstatindex('u_v4_k_idx')
UNION ALL
SELECT 'uuidv7', leaf_pages, avg_leaf_density, pg_size_pretty(index_size) FROM pgstatindex('u_v7_k_idx');

uuidv7() is new in PostgreSQL 18. On an older version, compare gen_random_uuid() with a bigint column filled from generate_series instead.

Keys	Leaf pages	Leaves full	Index size	Yours
uuidv4(), random	4,837	71.4%	38 MB
uuidv7(), time-ordered	3,832	90.0%	30 MB

Same number of keys, same key size, 26% more index for the random ones. Every one of those extra pages has to be cached, vacuumed and backed up.

What you measured

• An index turned an 8,334-page scan into a 4-page lookup.
• It cost a lot of disk: 39 MB for one column of a 65 MB table.
• Each index made every insert more expensive; five meant about 5× the WAL and 10× the time.
• The order keys arrive in changed the index size by a quarter.

Keep these numbers. The next page asks you to review an index change the way you would on a real pull request.

Clean up

DROP TABLE IF EXISTS users, orders, orders_src, u_v4, u_v7;
DROP FUNCTION IF EXISTS insert_cost(int);

Check yourself

1. In EXPLAIN (ANALYZE, BUFFERS) output, what does the Buffers line count, and why is it a better comparison than execution time?

   Show answer

   The pages the query touched, split into those already in memory (hit) and those read in (read). Page counts are stable from run to run; time depends on the machine, the cache and whatever else is running.

   I could explain this without looking

2. Why did adding indexes 2 and 3 cost more than adding the first one?

   Show answer

   Every index adds work to every insert. Indexes 2 and 3 were on email and customer_id, whose values arrive in random order, so they split pages all over the tree instead of appending at the right edge.

   I could explain this without looking

3. Two tables have UUID primary keys of the same type. One index is 26% larger. What is the most likely difference?

   Show answer

   The order the keys arrived in: random UUIDs (version 4) split leaves in the middle and leave them about 70% full, while time-ordered UUIDs (version 7) append and fill leaves to the 90% fillfactor.

   I could explain this without looking