Indexes

Without an index, finding the rows where email = 'x' means reading the whole table. That is a sequential scan, and it is not always wrong: on a small table, reading everything beats any alternative.

A btree index is a sorted structure that maps a column's values to the rows that hold them. Looking a value up is a handful of page reads instead of a full pass.

CREATE INDEX idx_customers_email ON customers (email);

What it costs

An index is not free. Every insert, update and delete has to maintain it, and it occupies disk and cache that the table itself would otherwise use. A btree over one bigint column on ten million rows is a couple of hundred megabytes. Indexing every column is how a write-heavy table becomes slow.

Three ways to lose an index

A function on the column. The index stores email, not lower(email):

WHERE lower(email) = 'a@b.com'   -- cannot use an index on email

Index the expression instead, with CREATE INDEX ... ON customers (lower(email)).

A leading wildcard. LIKE '%ana' cannot use a btree: there is no prefix to seek to. A prefix such as LIKE 'ana%' can in principle, but in PostgreSQL only if the index compares bytes — built with the C collation or the text_pattern_ops operator class. An ordinary index under a language collation such as en_US.utf8, which is what this sandbox uses, is not used for it at all:

1CREATE TABLE customers (2  id    int PRIMARY KEY,3  name  text NOT NULL,4  email text NOT NULL5);6INSERT INTO customers7SELECT g, 'Customer ' || g, 'user' || g || '@example.com'8FROM generate_series(1, 20000) AS g;9CREATE INDEX idx_customers_email ON customers (email);10ANALYZE customers;

1EXPLAIN2SELECT id3FROM customers4WHERE email LIKE 'user123%';

A type mismatch. MySQL and SQL Server compare a text column with a number by converting the column on every row, which is a function on the column again. PostgreSQL refuses the comparison instead: there is no text = integer operator, and the query fails.

Composite indexes

An index on (country, created_at) serves a query filtering on country, and one filtering on country and created_at. It does not serve a query filtering on created_at alone, for the same reason a phone book sorted by surname does not help you find everyone called Nino.

Reading a plan

EXPLAIN ANALYZE SELECT * FROM customers WHERE country = 'GE';

Read it inside out. What you want to see is the node at the bottom: an Index Scan, Index Only Scan or Bitmap Index Scan means it used one, a Seq Scan means it read the table. The numbers you care about are rows estimated against rows actual — when those diverge by a lot, the planner is working from bad statistics and every decision above that node is built on a wrong guess.

Try it

The same equality lookup, first on fifty rows, then on twenty thousand:

1CREATE TABLE customers (2  id      int PRIMARY KEY,3  name    text NOT NULL,4  country text NOT NULL5);6INSERT INTO customers7SELECT g, 'Customer ' || g, CASE WHEN g % 3 = 0 THEN 'GE' ELSE 'DE' END8FROM generate_series(1, 50) AS g;9CREATE INDEX idx_customers_country ON customers (country);10ANALYZE customers;

1EXPLAIN2SELECT count(*) AS georgian_customers3FROM customers4WHERE country = 'GE';

1CREATE TABLE customers (2  id    int PRIMARY KEY,3  name  text NOT NULL,4  email text NOT NULL5);6INSERT INTO customers7SELECT g, 'Customer ' || g, 'user' || g || '@example.com'8FROM generate_series(1, 20000) AS g;9CREATE INDEX idx_customers_email ON customers (email);10ANALYZE customers;

1EXPLAIN2SELECT id3FROM customers4WHERE email = 'user123@example.com';

Common mistakes

• Wrapping the indexed column. lower(email), CAST(created_at AS date), price * 1.2 > 100: each asks about a value the index does not store. Move the work to the other side of the comparison, or index the expression.
• Putting the wrong column first in a composite index. (created_at, country) cannot seek on country alone. Lead with the column every query filters on by equality.
• Indexing every column "just in case". Each one slows every write and competes for memory; most are never chosen by the planner.
• Reading Seq Scan as a bug. On a small table, or a filter that keeps most of the rows, it is the right plan, as the fifty-row example shows.

What interviewers ask

• When will the database ignore an index you created? When the filter keeps a large share of the rows, the table is small, or the predicate wraps the column or does not match its leading column.
• What is a covering index? One that holds every column the query needs, so the answer comes from the index without visiting the table (an Index Only Scan in PostgreSQL).
• How do you choose the column order of a composite index? Equality columns first, then the range or sort column; the index serves any leftmost prefix of its columns.