Postgres – Indexes

Every plan in this post was taken from a 400,000-row booking table, before and after creating the index being discussed. The timings are from one machine on one day and will differ on yours; the shape of the plan is the part that transfers.

What an index is

A separate structure holding the indexed values in sorted order, each with a pointer to its row. Postgres uses it when finding a few rows through it is cheaper than reading the whole table — and that comparison, not the existence of the index, is what decides. On a small table or a query returning most of the rows, a sequential scan genuinely is faster, and the planner choosing one is usually right.

B-tree, and column order

The default, and the answer for almost everything: equality, ranges, sorting, and LIKE 'prefix%'.

-- already on the booking table; drop it first if you are following along
DROP INDEX IF EXISTS ix_bookings_property_dates;
CREATE INDEX ix_bookings_property_dates ON bookings (property_id, check_in, check_out);

A composite index is usable left to right. That one serves:

• WHERE property_id = 42 — leading column, yes
• WHERE property_id = 42 AND check_in > '2024-06-01' — yes, and this is what it is for
• WHERE check_in > '2024-06-01' — no. The leading column is missing, so there is no range of the index to scan

The ordering rule that follows: equality columns first, then the one you range over or sort by. An index on (check_in, property_id) for the second query above would have to scan every entry in the date range and check property_id on each.

Partial indexes

An index over only the rows matching a condition. Smaller, and often dramatically faster.

SELECT id, total FROM bookings WHERE guests = 3 AND status = 'PENDING';

With only the existing index on status:

Bitmap Heap Scan on bookings (actual rows=6720 loops=1)
  Recheck Cond: (status = 'PENDING')
  Filter: (guests = 3)
  Rows Removed by Filter: 18762
  Heap Blocks: exact=4213
  ->  Bitmap Index Scan on ix_bookings_status (actual rows=25482 loops=1)
Execution Time: 37.070 ms

It finds 25,482 pending bookings, reads all of them, and throws away 18,762. Now the partial index:

CREATE INDEX ix_bookings_pending_guests ON bookings (guests) WHERE status = 'PENDING';

Bitmap Heap Scan on bookings (actual rows=6720 loops=1)
  Recheck Cond: ((guests = 3) AND (status = 'PENDING'))
  Heap Blocks: exact=1137
  ->  Bitmap Index Scan on ix_bookings_pending_guests (actual rows=6720 loops=1)
Execution Time: 1.481 ms

6,720 rows found, none discarded, and a quarter of the heap blocks touched. The index also covers only 6% of the table, so it is small and cheap to maintain.

This is the highest-value index shape in most applications, because most applications query one state of a workflow far more than the others — pending jobs, unsent messages, active users. The condition in the index must appear in the query for it to be used.

Expression indexes

An index on a column is no use when the query wraps the column in a function:

SELECT id FROM users WHERE lower(email) = 'guest42@stayhub.test';

Seq Scan on users (actual rows=1 loops=1)
Execution Time: 24.935 ms

Even though ix_users_email exists — it stores emails, not lowercased emails. Index the expression instead:

CREATE INDEX ix_users_lower_email ON users (lower(email));

Bitmap Heap Scan on users (actual rows=1 loops=1)
  ->  Bitmap Index Scan on ix_users_lower_email (actual rows=1 loops=1)
Execution Time: 0.055 ms

The expression in the query must match the one in the index exactly.

Covering indexes and index-only scans

If every column a query needs is in the index, Postgres can answer without touching the table at all:

CREATE INDEX ix_bookings_property_total ON bookings (property_id) INCLUDE (total);

Index Only Scan using ix_bookings_property_total on bookings (actual rows=20 loops=1)
  Heap Fetches: 0
Execution Time: 0.050 ms

INCLUDE columns are stored in the index but not sorted by it — they cannot be searched on, only returned. That keeps the index smaller than adding them as key columns.

Heap Fetches: 0 is the line to look for. An index-only scan still has to check row visibility, and it uses the visibility map to do so. If the table has been written to recently and not vacuumed, the map is stale, heap fetches climb, and the "index-only" scan reads the table anyway. An index-only scan that is not performing is usually a vacuum problem, not an index problem.

Why the index you added is not being used

Four reasons, in the order they are worth checking:

1. The query does not match the index. The column is wrapped in a function, the leading column of a composite index is missing, or the expression differs by a cast. This is most of them.
2. The index is not selective enough to be worth it. An index on a boolean, or on a status where 90% of rows share one value, saves nothing — reading most of the table through an index is slower than reading it directly, and the planner knows.
3. The statistics are stale. The planner works from a sample collected by ANALYZE. Right after a bulk load, it may believe the table is empty. Run ANALYZE bookings; and try again.
4. Types do not match. Comparing a bigint column to a numeric literal, or text to varchar across a join, can force a cast on the column side and lose the index.

To find out which, ask the planner rather than guessing — and read the next post, which is about nothing else.

The other index types

What an index costs

Reading the index list of a busy table is usually more productive than adding to it:

SELECT indexrelname AS index, idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  relname = 'bookings'
ORDER  BY idx_scan;

Every index is updated on every insert, and on every update that touches its columns. An index nobody reads is pure cost — disk, write amplification, and slower VACUUM. A table with twelve indexes is a table whose write throughput is a fraction of what it could be.

Three habits worth having:

• Index foreign keys. Postgres does not do it for you, and deleting a parent scans the whole child table without one.
• Drop indexes with idx_scan = 0 after a representative period — and check every replica, since the counter is per server.
• Build on a live table with CONCURRENTLY, which is the migrations post's subject.

CREATE INDEX CONCURRENTLY ix_bookings_guest ON bookings (guest_id);

One last thing that is easy to miss: a unique constraint already is an index. The booking table's public_id is unique, so it has a b-tree whether you asked for one or not, and adding another on the same column is pure waste. The same goes for a primary key. Check what is already there before creating anything:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'bookings' ORDER BY indexname;

Or \d bookings in psql, which prints the indexes and the constraints together — and shows you which of the indexes exist only because a constraint needed them.