An introduction to indexes

Indexes are mainly used for performance reasons: they give PostgreSQL a faster path to rows that match a query.

Let's pretend we have two versions of the same users dataset. They use the same seed data, but one migration creates an email index and the other does not.

Migration

As you can see, the only difference is the index.

Without Index

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  city TEXT NOT NULL
);

With Index

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  city TEXT NOT NULL
);


-- Add an index so PostgreSQL can find rows by email
-- without scanning the whole table.
CREATE INDEX users_email_idx ON users (email);

Seed

Both databases use the same seed data, then analyze the table so the planner has statistics.

INSERT INTO users (name, email, city)
SELECT
  'User ' || value,
  'user' || value || '@example.com',
  CASE value % 4
    WHEN 0 THEN 'London'
    WHEN 1 THEN 'Paris'
    WHEN 2 THEN 'Berlin'
    ELSE 'Madrid'
  END
FROM generate_series(1, 10000) AS value;

ANALYZE users;

Query

This is the same email lookup query on both databases.

SELECT id, name, email, city
FROM users
WHERE email = 'user9000@example.com';

Result

The query returns the same row either way.

Explain

The result is the same, but the plan is different.

EXPLAIN SELECT id, name, email, city
FROM users
WHERE email = 'user9000@example.com';

Without Index

Loading plan...

With Index

Loading plan...

Without an index, PostgreSQL has to read through the users table and check each row until it finds the email. That is what Seq Scan means. With the index, PostgreSQL has a separate lookup structure ordered by email, so it can find user9000@example.com by searching that smaller structure first. That is the Index Scan.

The estimated cost changes from 0.00..218.00 to 0.29..8.30. The startup cost is a little higher because PostgreSQL has to enter the index first, but the total cost is about 26x lower because it expects to touch far fewer rows.

The tradeoff is that the index is extra data PostgreSQL has to store and keep updated. Reads that filter by email get faster, but inserts, updates, and deletes can become a little slower because PostgreSQL must maintain the table and the index.

Note: A primary key is a special case: PostgreSQL automatically creates a unique index for it. So we do not need to add another index on id. But the table itself is not stored in primary-key order; the index is the ordered lookup structure.

Write Query

This update changes the indexed email value, so PostgreSQL has to change the table row. In the indexed database, it also has to keep the email index correct.

UPDATE users
SET email = 'renamed9000@example.com'
WHERE email = 'user9000@example.com';

Write Explain

The index still helps PostgreSQL find the row, but the write now has extra work after the row is found: the email index entry must be updated too.

EXPLAIN UPDATE users
SET email = 'renamed9000@example.com'
WHERE email = 'user9000@example.com';

Without Index

Loading plan...

With Index

Loading plan...

Both plans start with Update on users because both queries change a row in the same table. The difference is the step underneath it: without the index, PostgreSQL uses a Seq Scan and checks rows until it finds the matching email. With the index, it uses users_email_idx to find the row first.

This cost still drops from 218.00 to 8.30 because the plan is measuring how PostgreSQL finds the row to update. The index helps that part. The extra write work happens after the row is found: PostgreSQL must remove the old email from the index and add the new one. That write overhead is real, but it is not obvious from this simple EXPLAIN cost.

What We Learned

CREATE INDEXcreates an index, which gives PostgreSQL another way to find matching rows without scanning the whole table.
CASEbuilds conditional values inside a SQL statement. In this lesson we use it while seeding rows into different cities.