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%' can use a btree, because the index is sorted and the query is a prefix. LIKE '%ana' cannot: there is no prefix to seek to.

A type mismatch. Comparing a text column to a number makes the database cast one side, and a cast is a function.

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 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

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;

1SELECT count(*) AS georgian_customers2FROM customers3WHERE country = 'GE';