Most articles explain database indexing with the same book analogy: an index is like the index at the back of a book. That is true, and it is also where most explanations stop. It does not tell you why your query still takes 400 ms after you added an index, why an index on a boolean column does nothing, or why your nightly import got twice as slow after a “quick optimization”.
This guide takes a different route. We run the same query on the same PostgreSQL table, first without an index and then with one, and we read the actual EXPLAIN ANALYZE output line by line. Then we look at composite indexes, partial indexes, covering indexes, and the very real cost indexes add to every INSERT, UPDATE and DELETE.
What is database indexing?
Database indexing is the practice of building an additional, ordered data structure on top of a table so the database engine can locate rows without reading the whole table. The index stores the indexed column values plus a pointer to the physical row location, and it keeps those values sorted (in the case of a B-tree, the default in PostgreSQL). upsun.com makes the same point with more data.
Three consequences follow from that single sentence, and they drive every decision in this article:
- Reads get faster because the engine can jump straight to matching rows instead of scanning millions of them.
- Writes get slower because every insert, delete and (sometimes) update has to maintain the extra structure.
- Storage grows because the index is a real object on disk, often 10% to 40% of the table size per index.
An index is therefore not a setting you turn on. It is a trade you make, and the only way to know if the trade is worth it is to measure it.
Our sample table
Everything below was run on PostgreSQL 18 with a synthetic orders table of 5 million rows. You can reproduce it locally in about a minute.
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
country_code char(2) NOT NULL,
total_cents integer NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO orders (customer_id, status, country_code, total_cents, created_at)
SELECT
(random() * 100000)::int,
(ARRAY['pending','paid','shipped','cancelled'])[1 + (random() * 3)::int],
(ARRAY['FR','BE','CA','US','DE'])[1 + (random() * 4)::int],
(random() * 50000)::int,
now() - (random() * interval '900 days')
FROM generate_series(1, 5000000);
VACUUM ANALYZE orders;The table weighs roughly 450 MB. The only index that exists so far is the one PostgreSQL created automatically for the primary key.
Important: the primary key already gives you one index
A PRIMARY KEY or a UNIQUE constraint creates a B-tree index behind the scenes. That is why SELECT * FROM orders WHERE id = 4210993 is already instant. Foreign keys, on the other hand, do not create an index on the referencing column, which is one of the most common sources of slow deletes in production.
Step 1: the query without an index
We want all orders from one customer. Let us ask PostgreSQL what it actually does, with timings and buffer counts.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 84213;Gather (cost=1000.00..76432.10 rows=49 width=42) (actual time=0.395..212.884 rows=53 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1088 read=57063
-> Parallel Seq Scan on orders (cost=0.00..75427.20 rows=20 width=42)
(actual time=0.121..198.402 rows=18 loops=3)
Filter: (customer_id = 84213)
Rows Removed by Filter: 1666649
Buffers: shared hit=1088 read=57063
Planning Time: 0.094 ms
Execution Time: 212.943 msRead the important lines:
- Parallel Seq Scan: PostgreSQL reads the entire table, splitting the work across three processes. There is no shortcut available.
- Rows Removed by Filter: 1666649 per worker, so roughly 5 million rows examined to return 53.
- Buffers: shared read=57063: about 445 MB pulled through the buffer cache for 53 rows.
- Execution Time: 212.943 ms, and that is with a warm cache and two extra CPU workers helping.
The ratio here is the whole story of database indexing: we touched 5,000,000 rows to return 53. That is a selectivity of 0.001%. This is exactly the situation an index was designed for.
Step 2: the same query with a B-tree index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
ANALYZE orders;Same query, same data, same machine:
Index Scan using idx_orders_customer_id on orders
(cost=0.43..192.31 rows=49 width=42) (actual time=0.041..0.196 rows=53 loops=1)
Index Cond: (customer_id = 84213)
Buffers: shared hit=53 read=4
Planning Time: 0.158 ms
Execution Time: 0.241 msThe difference, side by side:
| Metric | No index | With B-tree index |
|---|---|---|
| Plan node | Parallel Seq Scan | Index Scan |
| Rows examined | ~5,000,000 | 53 |
| Buffers touched | 58,151 | 57 |
| Execution time | 212.9 ms | 0.24 ms |
| CPU workers used | 3 | 1 |
Roughly 880 times faster, and it frees two CPU workers for other sessions. On a busy API endpoint called 200 times per second, that is the difference between a healthy database and a saturated one.
What actually happens inside a B-tree index
A B-tree (balanced tree) index in PostgreSQL is a set of 8 KB pages organised in levels:
- The root page holds a small number of separator values and pointers to the level below.
- Internal pages repeat that pattern, narrowing the search range at each level.
- Leaf pages hold the actual indexed values in sorted order, each with a TID (tuple identifier: block number plus offset) pointing to the row in the table heap.
- Leaf pages are linked to their neighbours, which is why range scans such as
BETWEENorORDER BYare cheap once the entry point is found.
A 5 million row B-tree on an integer column is typically only 3 levels deep. Finding a value means reading 3 index pages plus 1 heap page: 4 page reads instead of 58,000. That is the entire magic, and it also explains what indexes are bad at. For the wider picture, see Indexing in Databases.
The two things that break the magic
- Low selectivity. If a value matches 30% of the table, the planner will walk the index and then jump back to the heap hundreds of thousands of times, in random order. A sequential scan is faster, and PostgreSQL will correctly choose it.
- Random heap access. The index gives you row locations, not rows. Unless the query can be satisfied from the index alone, PostgreSQL must still visit the heap. This is why
Bitmap Index Scanexists: it collects TIDs, sorts them by physical page, then reads the heap in order.
Watch this in action with a low selectivity predicate:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';
Seq Scan on orders (cost=0.00..97843.00 rows=1249367 width=42)
(actual time=0.028..401.117 rows=1250284 loops=1)
Filter: (status = 'paid'::text)
Execution Time: 448.902 msEven if you create an index on status, PostgreSQL will ignore it. One quarter of the table matches, so an index adds work instead of removing it. An index on a column with 4 distinct values spread evenly is almost always dead weight.
PostgreSQL index types and when to use them
B-tree is the default and covers the large majority of cases, but it is not the only option. The piece How Does Indexing Work makes a good next read.
| Type | Best for | Typical use case |
|---|---|---|
| B-tree | Equality, ranges, sorting, uniqueness | IDs, foreign keys, dates, prices, status filters combined with other columns |
| Hash | Equality only | Long text keys compared with =. Rarely worth it over B-tree |
| GIN | Values containing multiple items | JSONB fields, arrays, full text search, trigram search with pg_trgm |
| GiST | Geometric and range overlap | PostGIS geometries, tstzrange exclusion constraints, nearest neighbour |
| BRIN | Huge tables with naturally ordered data | Append only logs and time series on created_at. Tiny on disk |
| SP-GiST | Non balanced, partitioned data | IP address prefixes, phone prefixes, quadtrees |
A BRIN index on our created_at column is worth a mention because the size difference is dramatic:
CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at);
-- B-tree on created_at : ~107 MB
-- BRIN on created_at : ~48 kBBRIN only stores min and max values per block range, so it works when physical row order roughly matches the column order. Our synthetic data is randomly ordered, so BRIN would perform poorly here. On a real append only events table, it is often the best value per megabyte you will ever get.
Composite indexes: column order is not cosmetic
A very common query pattern in an orders table:
SELECT * FROM orders
WHERE customer_id = 84213 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;With only idx_orders_customer_id, PostgreSQL fetches all rows for that customer, filters them, then sorts:
Limit (cost=193.10..193.15 rows=13 width=42) (actual time=0.331..0.334 rows=13 loops=1)
-> Sort (cost=193.10..193.13 rows=13 width=42) (actual time=0.330..0.331 rows=13 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using idx_orders_customer_id on orders ...
Filter: (status = 'pending'::text)
Rows Removed by Filter: 40
Execution Time: 0.372 msNow the composite version:
CREATE INDEX idx_orders_cust_status_created
ON orders (customer_id, status, created_at DESC);Limit (cost=0.56..47.22 rows=13 width=42) (actual time=0.029..0.036 rows=13 loops=1)
-> Index Scan using idx_orders_cust_status_created on orders
(actual time=0.028..0.033 rows=13 loops=1)
Index Cond: ((customer_id = 84213) AND (status = 'pending'::text))
Execution Time: 0.058 msThe Sort node is gone and the filter is gone. The index delivers rows already in the requested order, so LIMIT 20 stops after 20 index entries.
The column order rule
Order the columns like this:
- Columns used with equality (
=,IN) first. - Then the column used for sorting, with the matching direction.
- Then the column used with a range (
>,<,BETWEEN) last.
A range condition stops the index from being usable for anything to its right, which is why range columns go at the end.
The leftmost prefix rule (and its PostgreSQL 18 exception)
An index on (customer_id, status, created_at) can serve queries filtering on:
customer_idcustomer_id + statuscustomer_id + status + created_at
Historically it was near useless for a query filtering only on status. PostgreSQL 18 introduced B-tree skip scan, which lets the planner skip through distinct values of the leading column when that column has low cardinality. It genuinely helps, but do not treat it as a licence to stop thinking about column order: skip scan is only efficient when the leading column has few distinct values. With 100,000 distinct customer_id values, the leftmost prefix rule still applies in practice.
Bonus: a composite index can replace a single column one
Since idx_orders_cust_status_created starts with customer_id, our original idx_orders_customer_id is now redundant. Dropping it saves 107 MB and one index to maintain on every write. Check for this pattern regularly.
Partial indexes: index only the rows you query
Partial indexes are the most underused feature in PostgreSQL and the easiest win in many applications. If your dashboard only ever queries pending orders, you do not need to index the other 3.75 million rows.
CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';| Index | Rows indexed | Size on disk | Maintained on every write? |
|---|---|---|---|
| Full index on (status, created_at) | 5,000,000 | ~150 MB | Yes, always |
| Partial index WHERE status = ‘pending’ | ~1,250,000 | ~27 MB | Only for matching rows |
Partial indexes shine in these situations:
- Soft deletes:
WHERE deleted_at IS NULL, when 90% of queries ignore deleted rows. - Job queues:
WHERE state IN ('queued','running'), where the pending set stays small while the table grows forever. - Conditional uniqueness:
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL, allowing a reused email after account deletion. - Rare flags:
WHERE is_flagged = truewhen only 0.2% of rows are flagged.
One caveat: the planner will only use the partial index if it can prove your query predicate implies the index predicate. Writing WHERE status = $1 with a parameter will usually not match WHERE status = 'pending'. Keep the literal in the query, or use a dedicated query path.
Covering indexes and index only scans
The fastest scan in PostgreSQL is the one that never touches the table. If every column the query needs is inside the index, you get an Index Only Scan.
CREATE INDEX idx_orders_cust_include
ON orders (customer_id) INCLUDE (total_cents, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT total_cents, created_at FROM orders WHERE customer_id = 84213;Index Only Scan using idx_orders_cust_include on orders
(actual time=0.022..0.031 rows=53 loops=1)
Index Cond: (customer_id = 84213)
Heap Fetches: 0
Buffers: shared hit=5
Execution Time: 0.049 msTwo details matter here:
INCLUDEcolumns are stored in leaf pages only, so they do not bloat the tree structure and cannot be used for filtering or sorting. Use them for payload columns, not for predicates.Heap Fetches: 0is the line to watch. If the visibility map is stale because autovacuum has not run, PostgreSQL must check the heap anyway and you lose most of the benefit. A highHeap Fetchesvalue on a heavily updated table means you need more aggressive autovacuum settings, not a different index.
When indexes are silently ignored
You created the index, the query is still slow, and EXPLAIN shows a sequential scan. Here are the usual suspects.
| Situation | Why the index is skipped | Fix |
|---|---|---|
WHERE lower(email) = '[email protected]' |
The index stores email, not lower(email) |
Create an expression index: CREATE INDEX ON users (lower(email)) |
WHERE created_at::date = '2026-09-01' |
Cast makes the column non sargable | Use a range: created_at >= '2026-09-01' AND created_at < '2026-09-02' |
WHERE name LIKE '%smith%' |
A B-tree cannot search from the middle of a string | GIN index with the pg_trgm extension |
| Query returns 30% of rows | Random heap access costs more than a sequential read | Nothing to fix, the planner is right. Consider a covering index |
Column is bigint, parameter is numeric |
Implicit cast on the column side blocks the index | Match the types in the application or ORM mapping |
| Table just loaded in bulk | Statistics are stale, the planner guesses wrong | Run ANALYZE table_name |
| Small table (a few thousand rows) | The whole table fits in a handful of pages | Leave it alone, a seq scan is genuinely faster |
The part nobody measures: what indexes cost your writes
Every index has to be updated when data changes. We ran the same bulk insert of 1,000,000 rows into copies of the orders table with a varying number of indexes:
| Indexes on the table | Insert time (1M rows) | Relative cost | Total disk used |
|---|---|---|---|
| 1 (primary key only) | 12.4 s | 1.0x | 112 MB |
| 3 | 27.9 s | 2.2x | 168 MB |
| 6 | 51.6 s | 4.2x | 259 MB |
| 9 | 78.1 s | 6.3x | 341 MB |
The cost is close to linear in the number of indexes, plus extra WAL traffic which also hits replication lag and backup size.
The hidden killer: broken HOT updates
This is the subtlety that turns a harmless index into a performance incident. PostgreSQL has an optimization called HOT (Heap Only Tuple) update: when you update a row and none of the indexed columns changed, and there is free space in the same page, PostgreSQL writes the new row version in the same page and does not touch any index at all.
Add an index on a column your application updates constantly, for example last_seen_at or updated_at, and HOT updates stop happening for that table. Every update now writes to every index, table bloat accelerates, and autovacuum has more work to do.
-- Check your HOT update ratio
SELECT relname,
n_tup_upd,
n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;If hot_pct is low on a write heavy table, look at which indexed columns are being updated. Indexing a hot counter or timestamp column is one of the most expensive mistakes in database indexing.
A practical decision checklist
Before creating an index, run through this list:
- Is the query actually slow and actually frequent? Use
pg_stat_statementsand sort bytotal_exec_time, not by the query that annoyed you this morning. - Does the predicate return less than roughly 5 to 10% of the table? If not, an index will probably not be used.
- Is there already an index whose leftmost columns cover this? Extend it instead of adding a new one.
- Can a partial index cover the same need at a fraction of the size?
- Does the query also sort or limit? Add the sort column to the index in the right direction.
- Is the table write heavy? Weigh the write penalty and the HOT update impact.
- Did you verify the result with
EXPLAIN (ANALYZE, BUFFERS)before and after? If the plan did not change, drop the index.
Finding indexes you should delete
Unused indexes are pure cost: disk, write amplification, longer vacuum, slower restores. PostgreSQL tracks usage for you.
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique
AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;Two warnings before you start dropping things:
- Counters reset when you reset statistics or restore a cluster. Make sure the stats cover at least one full business cycle, including monthly and quarterly reports.
- Check replicas as well. A read replica may use indexes the primary never touches.
PostgreSQL 18 also exposes per index timing in pg_stat_all_indexes, which helps distinguish an index used once per day for a critical report from one used once by a curious developer.
Creating and rebuilding indexes without downtime
CREATE INDEX CONCURRENTLYavoids locking the table against writes. It is slower and can fail, leaving anINVALIDindex that you must drop and recreate. Always use it in production.DROP INDEX CONCURRENTLYexists too, and should be your default in production.REINDEX INDEX CONCURRENTLYrebuilds a bloated index in place. Useful after heavy delete or update workloads.- For a bulk data load, dropping the indexes, loading, then recreating them is frequently several times faster than loading with indexes in place.
Key takeaways
- Database indexing is a read/write trade, never a free speedup. Measure both sides.
EXPLAIN (ANALYZE, BUFFERS)is the only reliable source of truth. WatchRows Removed by Filter,BuffersandHeap Fetches.- B-tree covers almost everything. Reach for GIN, GiST or BRIN only when the data shape demands it.
- In composite indexes, put equality columns first, sort columns next, range columns last.
- Partial indexes give you most of the benefit at a fraction of the size and write cost.
- Never index a column your application updates on every request unless you have proven the read gain outweighs losing HOT updates.
- Review unused indexes at least twice a year and delete without sentimentality.
If you want a second pair of eyes on a slow PostgreSQL workload, the team at coding4.net works on query tuning, schema design and index strategy every week. Bring your worst EXPLAIN ANALYZE output and we will read it with you.
Frequently asked questions about database indexing
What are the three main types of indexes?
At the conceptual level, the three most commonly cited types are clustered indexes (which define the physical order of the rows), non clustered or secondary indexes (a separate structure pointing back to the rows), and unique indexes (which also enforce a constraint). PostgreSQL has no clustered index in the SQL Server sense: all its indexes are secondary, and the CLUSTER command only reorders the table once, without maintaining that order afterwards. In terms of implementation, the three you will meet most often in PostgreSQL are B-tree, GIN and BRIN.
How do I create an index in SQL?
The basic syntax is CREATE INDEX index_name ON table_name (column_name);. In PostgreSQL you can add CONCURRENTLY to avoid blocking writes, UNIQUE to enforce uniqueness, USING gin or another method to change the index type, INCLUDE (...) to add payload columns, and a WHERE clause to make it partial. Always verify the result with EXPLAIN ANALYZE afterwards.
Does a SQL index start at 0 or 1?
This question usually mixes up two different meanings of the word index. A database index is not a numbered position, it is a lookup structure, so it has no starting number. If you mean positional functions in SQL, PostgreSQL arrays and string functions such as substring() and position() are 1 based, not 0 based.
Why is indexing important in SQL?
Without an index, the database must read every row to answer a query, which means query time grows linearly with table size. With a well chosen index, lookup cost grows logarithmically, so a table can grow from 100,000 to 100 million rows while the query stays in the sub millisecond range. Indexing is what keeps an application usable as its data grows.
How many indexes is too many on one table?
There is no hard limit, but as a rule of thumb: on a write heavy OLTP table, more than 5 or 6 indexes should trigger a review. On a read heavy reporting table that is loaded once per night, 10 or more can be perfectly reasonable. The right measure is not the count, it is whether every index earns its keep in pg_stat_user_indexes.
Do indexes speed up JOIN operations?
Yes, and it is often where the biggest gains are hiding. Indexing the foreign key column on the referencing side lets PostgreSQL use a nested loop with an index scan instead of a hash join over the whole table. It also prevents slow cascading deletes, since PostgreSQL has to look up referencing rows when the parent row is removed.
Does an index help with ORDER BY and LIMIT?
It does, provided the index column order and sort direction match the query. When they match, PostgreSQL reads rows already sorted and stops as soon as the LIMIT is reached, which removes the Sort node from the plan entirely. You can confirm this by checking that Sort Method no longer appears in your EXPLAIN ANALYZE output.