PostgreSQL indexes and EXPLAIN

Without an index, finding rows means reading the whole table (a sequential scan). An index is a separate, sorted structure that lets PostgreSQL jump to matching rows, much like the index at the back of a book. Indexes speed up reads but cost disk space and slow down writes a little, so add them for real query patterns.

B-tree (default): equality and ranges (=, <, BETWEEN, ORDER BY, LIKE 'abc%').
GIN: values with many parts: jsonb, arrays, full-text.
GiST / SP-GiST / BRIN / hash: geometry and ranges, huge naturally ordered tables, and pure equality.
Multi-column (a, b) helps filters on a, or on a and b, but not on b alone.
Partial (WHERE status = 'pending') indexes only some rows. Expression indexes, like lower(email), index a computed value. Unique indexes enforce uniqueness.

EXPLAIN shows the plan the optimizer picked; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. Read plans from the most indented line outwards. On tiny tables PostgreSQL often prefers a sequential scan even when an index exists, because it’s genuinely cheaper, so the examples build a bigger table.

Foreign key columns are not indexed automatically. Indexing them (e.g. orders.customer_id) is one of the most common fixes for slow joins.

Syntax

CREATE INDEX [CONCURRENTLY] name ON table (col);
CREATE INDEX name ON table USING gin (jsonb_col);
EXPLAIN ANALYZE SELECT ...;

Examples

Edit the code and run it. Output is printed the way psql prints it.

23.1Build a table with 100,000 rows

DROP TABLE IF EXISTS events;
CREATE TABLE events AS
SELECT g AS id,
       (random() * 5000)::int AS user_id,
       (ARRAY['view','click','buy','signup'])[1 + (random() * 3)::int] AS kind,
       timestamp '2024-01-01' + (random() * 365) * interval '1 day' AS created_at,
       jsonb_build_object('device', (ARRAY['ios','android','web'])[1 + (random()*2)::int]) AS meta
FROM generate_series(1, 100000) AS g;

ANALYZE events;
SELECT count(*) FROM events;

CREATE TABLE ... AS SELECT creates and fills a table in one go. generate_series produces 100,000 rows of random data. ANALYZE collects the statistics the planner uses to estimate costs.

23.2Before the index: sequential scan

1
2

EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 42;

Look for Seq Scan on events and “Rows Removed by Filter”: PostgreSQL read every row to find a handful. Note the execution time.

23.3After the index: index scan

CREATE INDEX IF NOT EXISTS events_user_idx ON events (user_id);

EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 42;

Now the plan shows a Bitmap Index Scan or Index Scan using events_user_idx, touching only matching rows, and the time drops sharply.

23.4Multi-column, partial and expression indexes

CREATE INDEX IF NOT EXISTS events_user_time_idx ON events (user_id, created_at);
CREATE INDEX IF NOT EXISTS events_buy_idx ON events (created_at) WHERE kind = 'buy';
CREATE INDEX IF NOT EXISTS events_device_idx ON events ((meta ->> 'device'));

EXPLAIN SELECT * FROM events
WHERE kind = 'buy' AND created_at >= '2024-12-01';

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'events';

The partial index only contains purchases, so it is small and exactly fits “recent purchases” queries. pg_indexes lists every index on a table.

23.5GIN index for jsonb

CREATE INDEX IF NOT EXISTS events_meta_gin ON events USING gin (meta);

EXPLAIN ANALYZE
SELECT count(*) FROM events WHERE meta @> '{"device": "ios"}';

A GIN index supports @>, ? and friends. Because about a quarter of rows match here, the planner may still choose a sequential scan; indexes win when a query selects a small fraction of the table. Try '{"device": "tv"}' instead.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 23.1

Create an index on orders(customer_id) (any name).

Hint

CREATE INDEX orders_customer_idx ON orders (customer_id);

Solution

CREATE INDEX orders_customer_idx ON orders (customer_id);