An index is a separate ordered structure that lets PostgreSQL find the rows it needs without reading the whole table. You design an index for a specific query: composite when there is an equality filter plus a sort, partial when you query a small fraction of rows, expression when the condition applies a function, GIN for jsonb and full-text search. Every index slows down INSERT and UPDATE and takes disk space, so confirm the effect with EXPLAIN and drop indexes nobody uses.
After this you will be able to
- explain how a B-tree finds rows and why an index helps some queries and is useless for others
- build a composite index with the right column order and drop the one it absorbs
- choose a partial, expression or GIN index for a specific
WHEREcondition - confirm the effect with
EXPLAIN (ANALYZE, BUFFERS)and estimate what the index costs on writes
Know beforehand
Verified with
PostgreSQL 17 (17.11, postgres:17-alpine)Docker 29
Checked against docs: October 11, 2026
Contents
What an index is and how a B-tree works
The PostgreSQL documentation compares an index to the index at the back of a book: to find a term you do not read every page, you look it up and jump to the right one. Without an index, WHERE customer_id = 4242 has to look at every row in the table. That is a sequential scan, shown in a plan as Seq Scan.
By default CREATE INDEX builds a B-tree: a balanced tree whose leaves hold the column values in order together with row addresses. A lookup descends from the root to a leaf in a few steps and then walks neighbouring leaves. That makes a B-tree a fit for equality and ranges (=, <, >, BETWEEN, IN), for IS NULL, and for returning rows already sorted, so ORDER BY can skip a separate sort.
Every PostgreSQL index is secondary: it lives apart from the table's main storage, which the docs call the heap. A plain Index Scan first finds the entry in the index and then fetches the row from the heap. If every column the query needs is in the index, an Index Only Scan can skip the table entirely.
B-tree (default)
- Use it for
- Equality, ranges, sorting,
LIKE 'abc%' - Example
created_at >= now() - interval '1 day'
Hash
- Use it for
- Equality only
- Example
token = '…'
GIN
- Use it for
- Values made of many parts: arrays, jsonb, tsvector
- Example
attrs @> '{"promo": "AUTUMN25"}'
GiST / SP-GiST
- Use it for
- Geometry, ranges, nearest-neighbour search
- Example
ORDER BY location <-> point '(10,20)'
BRIN
- Use it for
- Huge tables whose values correlate with the physical row order
- Example
- an event log ordered by insert time
The test bench: a million orders in Docker
The documentation warns that conclusions about indexes drawn from tiny test data are worthless: a hundred rows fit on one page, and nothing beats reading that page sequentially. So the bench is an orders table with a million rows: 50,000 customers, 1% of orders in the new status, mixed-case emails, and a jsonb column holding the sales channel and a rare promo code. setseed makes the data identical on every run.
docker run -d --rm --name kb-pg-indeksy -e POSTGRES_PASSWORD=test postgres:17-alpine
docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 01-seed.sql-- Training orders table: 1,000,000 rows, reproducible data (setseed)
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
email text NOT NULL,
total numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL,
attrs jsonb NOT NULL DEFAULT '{}'
);
SELECT setseed(0.42);
INSERT INTO orders (customer_id, status, email, total, created_at, attrs)
SELECT c,
CASE WHEN r < 0.01 THEN 'new' -- 1% of orders are waiting to be processed
WHEN r < 0.05 THEN 'cancelled'
ELSE 'done' END,
'User' || c || '@Example.com', -- mixed case, the way people type it
round((500 + random() * 49500)::numeric, 2),
timestamptz '2025-01-01' + random() * interval '365 days',
jsonb_build_object('channel', (ARRAY['site','app','phone','marketplace'])[1 + floor(random() * 4)::int])
|| CASE WHEN random() < 0.005 THEN '{"promo": "AUTUMN25"}'::jsonb ELSE '{}'::jsonb END
FROM (SELECT 1 + floor(random() * 50000)::int AS c, random() AS r
FROM generate_series(1, 1000000)) AS g;
ANALYZE orders;
SELECT count(*) AS rows,
pg_size_pretty(pg_relation_size('orders')) AS table_size
FROM orders;
SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 01-seed.sql
INSERT 0 1000000
ANALYZE
rows | table_size
---------+------------
1000000 | 108 MB
(1 row)
status | count
-----------+--------
done | 949989
cancelled | 39986
new | 10025
(3 rows)Always run ANALYZE after loading. It collects statistics on how values are distributed, and the planner uses them to estimate how many rows a condition will return. Without them, the documentation calls examining index usage a lost cause.
When an index is used: measuring before and after
Take an account-page query: a customer's ten most recent orders. Every plan is captured with EXPLAIN (ANALYZE, BUFFERS, COSTS OFF). ANALYZE runs the query and reports real time and row counts, BUFFERS shows how many 8 kB pages were read, and COSTS OFF hides cost estimates to keep the plan short. Reading a whole plan is covered in the query optimisation lesson.
-- A customer's last 10 orders: a typical account-page query
\echo '=== BEFORE: no index'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;
CREATE INDEX orders_customer_idx ON orders (customer_id);
\echo '=== Index on customer_id'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;
-- Composite index: equality first, sort second
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
DROP INDEX orders_customer_idx; -- now redundant: the composite index starts with the same column
\echo '=== Composite index (customer_id, created_at)'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;
\echo '=== Same index, but the condition is on the second column only'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*)
FROM orders
WHERE created_at >= '2025-12-31';$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 02-btree.sql
=== BEFORE: no index
Limit (actual time=20.527..23.919 rows=10 loops=1)
Buffers: shared hit=13832 read=107
-> Gather Merge (actual time=20.525..23.916 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13832 read=107
-> Sort (actual time=14.853..14.854 rows=5 loops=3)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13832 read=107
-> Parallel Seq Scan on orders (actual time=3.019..14.720 rows=5 loops=3)
Filter: (customer_id = 4242)
Rows Removed by Filter: 333328
Buffers: shared hit=13758 read=107
Planning Time: 0.485 ms
Execution Time: 23.987 ms
=== Index on customer_id
Limit (actual time=0.069..0.070 rows=10 loops=1)
Buffers: shared hit=16 read=3
-> Sort (actual time=0.068..0.068 rows=10 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=16 read=3
-> Bitmap Heap Scan on orders (actual time=0.023..0.056 rows=16 loops=1)
Recheck Cond: (customer_id = 4242)
Heap Blocks: exact=16
Buffers: shared hit=16 read=3
-> Bitmap Index Scan on orders_customer_idx (actual time=0.016..0.017 rows=16 loops=1)
Index Cond: (customer_id = 4242)
Buffers: shared read=3
Planning Time: 0.192 ms
Execution Time: 0.093 msWithout an index PostgreSQL runs a parallel sequential scan: three processes read almost 14,000 pages and throw away 333,000 rows each to find 16 orders. With an index on customer_id the plan becomes Bitmap Index Scan + Bitmap Heap Scan: the index returns the addresses of 16 rows and the table is read selectively, 19 pages instead of 13,939.
An index goes unused when the condition does not match what the index stores, or when the planner decides a sequential read is cheaper:
The condition returns a large share of the table
- Why the index does not help
- Reading almost every page in order is cheaper than jumping around through the index
- What to do
- Nothing: the planner is right
A function on the column: lower(email) = …
- Why the index does not help
- The index stores
email, notlower(email) - What to do
- An expression index
LIKE '%abc', or LIKE under a non-C locale
- Why the index does not help
- A B-tree only searches by the start of a string, and only with a suitable operator class
- What to do
text_pattern_ops; for substrings,pg_trgmor full-text search
A condition on the second column of a composite index only
- Why the index does not help
- The index order is driven by the first column
- What to do
- Change the column order or add a separate index
Stale statistics
- Why the index does not help
- The planner gets row counts wrong
- What to do
ANALYZE, check autovacuum
Composite indexes and column order
The plan with the customer_id index still has a Sort node: the rows are found quickly but still have to be sorted by date. A composite index on (customer_id, created_at) keeps entries sorted by customer and, within each customer, by date. A query with equality on the first column and a sort on the second reads the index from the end (Index Scan Backward) and stops at the tenth row.
=== Composite index (customer_id, created_at)
Limit (actual time=0.034..0.048 rows=10 loops=1)
Buffers: shared hit=10 read=3
-> Index Scan Backward using orders_customer_created_idx on orders (actual time=0.033..0.047 rows=10 loops=1)
Index Cond: (customer_id = 4242)
Buffers: shared hit=10 read=3
Planning Time: 0.186 ms
Execution Time: 0.082 ms
=== Same index, but the condition is on the second column only
Finalize Aggregate (actual time=18.555..21.725 rows=1 loops=1)
Buffers: shared hit=13854 read=11
-> Gather (actual time=18.423..21.718 rows=3 loops=1)
Buffers: shared hit=13854 read=11
-> Partial Aggregate (actual time=16.150..16.152 rows=1 loops=3)
Buffers: shared hit=13854 read=11
-> Parallel Seq Scan on orders (actual time=0.007..16.089 rows=954 loops=3)
Filter: (created_at >= '2025-12-31 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 332379
Buffers: shared hit=13854 read=11
Planning Time: 0.089 ms
Execution Time: 21.770 msThe rule from the documentation: conditions on the leading (leftmost) columns narrow the scan best, namely equality on the first columns plus an inequality on the first column without an equality. Conditions on columns further right are checked inside the index but do not shrink the part of the index that is scanned. That is why WHERE created_at >= '2025-12-31' without customer_id falls back to Seq Scan: the dates in this index are scattered across 50,000 per-customer folders.
Equality columns first
The ones in
WHERE col = …: customer, status, tenant.Then the range or the sort
A date in
ORDER BYorcreated_at >= …goes after the equality columns.No more than three columns
The docs warn that indexes wider than three columns rarely pay off unless the workload is very uniform.
Drop the absorbed index
The
(customer_id)index is now redundant:(customer_id, created_at)starts with the same column and serves the same queries.
Partial and expression indexes
A partial index covers only the rows that satisfy its WHERE clause. The classic case is a queue: operators need new orders, and those are 1% of a million. There is no reason to index 950,000 completed orders for that.
-- 1. Partial index: the queue of new orders (1% of the table)
\echo '=== New-order queue: BEFORE'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'new'
ORDER BY created_at
LIMIT 50;
CREATE INDEX orders_status_created_idx ON orders (status, created_at); -- regular, for comparison
CREATE INDEX orders_new_created_idx ON orders (created_at) WHERE status = 'new'; -- partial
SELECT indexrelid::regclass AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
DROP INDEX orders_status_created_idx; -- keep only the partial one
\echo '=== New-order queue: partial index'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'new'
ORDER BY created_at
LIMIT 50;
-- 2. Expression index: case-insensitive email lookup
\echo '=== lower(email): BEFORE'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';
CREATE INDEX orders_email_lower_idx ON orders (lower(email));
\echo '=== lower(email): expression index'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';
\echo '=== Prefix LIKE: the default operator class does not help (non-C locale)'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) LIKE 'user4242%';
DROP INDEX orders_email_lower_idx;
CREATE INDEX orders_email_lower_idx ON orders (lower(email) text_pattern_ops);
\echo '=== Prefix LIKE: text_pattern_ops'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) LIKE 'user4242%';
\echo '=== Equality works with text_pattern_ops too'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 03-partial-expression.sql
index_name | size
-----------------------------+--------
orders_status_created_idx | 30 MB
orders_customer_created_idx | 30 MB
orders_pkey | 21 MB
orders_new_created_idx | 240 kB
(4 rows)
=== New-order queue: partial index
Limit (actual time=0.020..0.077 rows=50 loops=1)
Buffers: shared hit=50 read=2
-> Index Scan using orders_new_created_idx on orders (actual time=0.019..0.073 rows=50 loops=1)
Buffers: shared hit=50 read=2
Planning Time: 0.140 ms
Execution Time: 0.091 msA partial index is used only when the planner can prove that the query's condition implies the index predicate. WHERE status = 'new' qualifies; WHERE status = $1 with a parameter does not when the plan is built in advance without knowing the value. The docs also warn against a set of partial indexes as a substitute for partitioning: a dozen WHERE category = N indexes are worse than one index on (category, data).
An expression index stores the already computed result of a function. WHERE lower(email) = 'user4242@example.com' cannot use an index on email, but an index on lower(email) turns it into an ordinary point lookup: 13,865 pages without the index, 19 with it. The price is that the expression is computed on every insert and on every update that cannot be done as HOT (a heap-only tuple update, one that leaves the indexes untouched).
=== Prefix LIKE: the default operator class does not help (non-C locale)
Gather
Workers Planned: 2
-> Parallel Seq Scan on orders
Filter: (lower(email) ~~ 'user4242%'::text)
=== Prefix LIKE: text_pattern_ops
Bitmap Heap Scan on orders
Filter: (lower(email) ~~ 'user4242%'::text)
-> Bitmap Index Scan on orders_email_lower_idx
Index Cond: ((lower(email) ~>=~ 'user4242'::text) AND (lower(email) ~<~ 'user4243'::text))A B-tree can serve a prefix search (LIKE 'user4242%'), but if the database was not created with the C locale you need the text_pattern_ops operator class, which compares strings character by character and ignores the locale's collation rules. That index serves equality too, but not plain < and >; for those you need a second index with the default class.
GIN for jsonb and full-text search, briefly
GIN is an inverted index: for each component of a value (a jsonb key, a word in a document, an array element) it stores the list of rows where that component appears. It is the book-index idea again, applied to values made of many parts.
-- GIN for jsonb: orders with a promo code (0.5% of rows)
\echo '=== jsonb @>: BEFORE'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE attrs @> '{"promo": "AUTUMN25"}';
CREATE INDEX orders_attrs_gin ON orders USING gin (attrs jsonb_path_ops);
\echo '=== jsonb @>: GIN jsonb_path_ops'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE attrs @> '{"promo": "AUTUMN25"}';
\echo '=== Same meaning via ->>: GIN is not used'
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE attrs ->> 'promo' = 'AUTUMN25';
-- GIN for full-text search
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
body text NOT NULL,
search tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);
SELECT setseed(0.7);
INSERT INTO reviews (body)
SELECT (ARRAY['Fast delivery by courier', 'The courier was an hour late', 'Great coffee and pastries',
'The order was packed wrong', 'Convenient app', 'Delivered cold'])[1 + floor(random() * 6)::int]
|| ', order #' || n
FROM generate_series(1, 200000) AS n;
CREATE INDEX reviews_search_gin ON reviews USING gin (search);
ANALYZE reviews;
\echo '=== Full-text search: GIN on tsvector'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM reviews
WHERE search @@ websearch_to_tsquery('english', 'courier late');$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 04-gin.sql
=== jsonb @>: BEFORE
Finalize Aggregate (actual time=60.905..65.731 rows=1 loops=1)
-> Gather (actual time=60.760..65.723 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual time=55.209..55.210 rows=1 loops=3)
-> Parallel Seq Scan on orders (actual time=0.115..55.055 rows=1643 loops=3)
Filter: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
Rows Removed by Filter: 331690
Planning Time: 0.784 ms
Execution Time: 65.846 ms
=== jsonb @>: GIN jsonb_path_ops
Aggregate (actual time=4.888..4.889 rows=1 loops=1)
-> Bitmap Heap Scan on orders (actual time=1.043..4.672 rows=4930 loops=1)
Recheck Cond: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
Heap Blocks: exact=4107
-> Bitmap Index Scan on orders_attrs_gin (actual time=0.657..0.657 rows=4930 loops=1)
Index Cond: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
Planning Time: 0.214 ms
Execution Time: 4.952 ms
=== Same meaning via ->>: GIN is not used
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on orders
Filter: ((attrs ->> 'promo'::text) = 'AUTUMN25'::text)- The default
jsonb_opsclass supports?,?|,?&,@>,@?and@@;jsonb_path_opssupports only@>,@?and@@, but is usually much smaller and more selective. - GIN works with operators, not with value extraction:
attrs ->> 'promo' = 'AUTUMN25'does not use it. That condition needs a B-tree expression index on(attrs ->> 'promo'). - For full-text search the docs name GIN the preferred index type. It is built on a
tsvectorcolumn; in the example that is a generated columnto_tsvector('english', body). - GIN does not support
Index Only Scan: the row is still read from the table for a recheck (Recheck Cond).
What an index costs: writes, WAL and disk
An index is updated on every INSERT, on DELETE, and on any UPDATE of indexed columns. The documentation says it plainly: keeping the index in sync adds overhead to data changes, and indexes that are rarely used should be dropped. Let us measure it with the same batch of 200,000 rows going into a copy of the table with only a primary key and into orders with five indexes.
-- The write cost of indexes: the same batch of 200,000 rows goes
-- into a table with only a primary key and into orders with all its indexes
CREATE TEMP TABLE batch AS
SELECT customer_id, status, email, total, created_at, attrs
FROM orders
ORDER BY random()
LIMIT 200000;
DROP TABLE IF EXISTS orders_pk_only;
CREATE TABLE orders_pk_only (LIKE orders INCLUDING DEFAULTS INCLUDING IDENTITY);
ALTER TABLE orders_pk_only ADD PRIMARY KEY (id);
CHECKPOINT;
\timing on
SELECT pg_current_wal_lsn() AS lsn0 \gset
INSERT INTO orders_pk_only (customer_id, status, email, total, created_at, attrs)
SELECT * FROM batch;
SELECT pg_current_wal_lsn() AS lsn1 \gset
INSERT INTO orders (customer_id, status, email, total, created_at, attrs)
SELECT * FROM batch;
SELECT pg_current_wal_lsn() AS lsn2 \gset
\timing off
SELECT pg_size_pretty(pg_wal_lsn_diff(:'lsn1', :'lsn0')) AS wal_pk_only,
pg_size_pretty(pg_wal_lsn_diff(:'lsn2', :'lsn1')) AS wal_all_indexes;
-- How much space the indexes take and how often they were read
SELECT indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;
SELECT pg_size_pretty(pg_table_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 05-write-cost.sql
INSERT 0 200000
Time: 312.902 ms
Time: 0.222 ms
INSERT 0 200000
Time: 1673.356 ms (00:01.673)
Time: 0.166 ms
Timing is off.
wal_pk_only | wal_all_indexes
-------------+-----------------
40 MB | 150 MB
(1 row)The same insert with five indexes took five to six times longer and wrote 150 MB of WAL instead of 40 MB. WAL feeds replicas and backups, so every extra index multiplies replication traffic. The orders indexes add up to 96 MB next to the 130 MB table itself, and the composite index grew from 30 to 48 MB after inserts landed in random places, because B-tree pages split.
- The index serves a specific slow query, not a just-in-case one
- The effect is confirmed with
EXPLAIN (ANALYZE, BUFFERS)before and after - No index is fully absorbed by a composite one
- On a live table the index is built with
CREATE INDEX CONCURRENTLY: it does not block writes, but takes longer and cannot run inside a transaction - Once a quarter, check
pg_stat_user_indexes: indexes with zeroidx_scanover a long period are candidates for removal
Exercises
Next up is the query optimisation lesson. It covers plan costs, estimated versus actual rows, joins, and the usual fixes for when an index already exists and the query is still slow.
How this was verified3 · passedchecked 11 October 2026
“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.