Postgres Indexes and Query Times: A Practical Walkthrough

Adding an index is easy. Knowing which one, on which columns, in which order, and when not to bother is the part that matters.

Hafeez BaigHafeez Baig•May 26, 2026•12 min read

Introduction

A query gets slow. Someone adds an index. The query gets faster, or it does not, and either way nobody is quite sure why. Postgres indexing gets treated as a folk remedy applied by pattern matching, rather than a mechanism with rules you can reason about. The rules are knowable.

Everything below uses one schema, so the examples build on each other:

sql
CREATE TABLE users ( id bigserial PRIMARY KEY, email text NOT NULL, country text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), is_active boolean NOT NULL DEFAULT true, profile jsonb ); CREATE TABLE orders ( id bigserial PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id), status text NOT NULL, total_cents integer NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );

Assume users has a few million rows and orders rather more. All plan output below is illustrative, written to show plan shapes rather than measured on a real machine.

1. What an Index Actually Is

An index is a separate data structure, stored on disk, holding a copy of some of your data in a defined order along with pointers back to the rows in the table. Not a setting you switch on: a second object Postgres builds and maintains.

That framing makes the trade-off obvious.

  • It costs disk. An index on a wide column can be a substantial fraction of the table's own size.
  • It slows writes. Every INSERT adds an entry to every index on the table, and every UPDATE touching an indexed column updates the index too.
  • It has to be maintained. Indexes bloat and consume vacuum work you do not directly see.

An index is a purchase: write throughput and storage spent on read speed for specific query shapes. Usually a good trade, and still a trade.

2. Reading EXPLAIN and EXPLAIN ANALYZE

The planner is the component deciding how to execute a query. EXPLAIN asks it to show that decision.

sql
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';

EXPLAIN plans the query but does not run it. You get an estimate: the intended plan, and guesses at cost and row counts.

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ada@example.com';

EXPLAIN ANALYZE actually executes the query and reports real timings and row counts alongside the estimates. Two consequences. It runs the statement, harmless on a SELECT but data changing on an UPDATE or DELETE, so wrap those in a transaction you roll back. And the gap between estimated and actual rows becomes visible. A planner expecting 12 rows and getting 400,000 chose on bad information.

The habit worth forming: read the rows= estimate and the actual rows= value side by side. Large divergence there explains more bad plans than anything else.

3. The Four Scan Types to Recognise

Most plan reading comes down to identifying which access method Postgres chose.

Sequential scan. Read every row in the table, top to bottom.

Code
Seq Scan on users  (cost=0.00..48250.00 rows=1 width=84)
  Filter: (email = 'ada@example.com'::text)
  Rows Removed by Filter: 1999999

Index scan. Walk the index to find matching entries, then fetch each matching row from the table.

Code
Index Scan using users_email_idx on users  (cost=0.43..8.45 rows=1 width=84)
  Index Cond: (email = 'ada@example.com'::text)

Index only scan. Answer from the index alone. Possible only when every column the query needs is in the index.

Code
Index Only Scan using users_email_idx on users  (cost=0.43..4.45 rows=1 width=32)
  Index Cond: (email = 'ada@example.com'::text)
  Heap Fetches: 0

Bitmap heap scan. Used when many rows match. Postgres builds a bitmap of which pages hold matching rows, then reads those pages in physical order. It appears as a pair of nodes:

Code
Bitmap Heap Scan on orders  (cost=215.00..8940.00 rows=12000 width=40)
  Recheck Cond: (status = 'pending'::text)
  ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..212.00 rows=12000 width=0)
        Index Cond: (status = 'pending'::text)

The bitmap exists for a good reason. An index scan fetches rows one at a time in index order, effectively random access on disk. Fine for ten rows. For fifty thousand it is slower than reading pages sequentially.

4. Why a Sequential Scan Is Often Correct

A sequential scan is not automatically a bug. Sometimes reading everything genuinely is cheapest. Two cases dominate:

  1. The table is small. If it fits in a handful of pages, the index lookup plus the table fetch is more work than reading the pages.
  2. The query has low selectivity. Selectivity is what fraction of rows the condition keeps. A condition matching 40% of a table is low selectivity: using an index means heavy random access, and you read most of the table anyway.
sql
-- If most users are active, no index will help this. SELECT * FROM users WHERE is_active = true;

Ask whether the plan is wrong before assuming an index is missing. A sequential scan on a fast query needs no fixing.

5. B-tree: The Default

CREATE INDEX with no type specified gives a B-tree, a balanced tree keeping entries in sorted order. It is the right answer for the large majority of cases. Because it is ordered, it supports:

  • Equality: WHERE email = 'ada@example.com'
  • Range comparisons: WHERE created_at >= '2026-01-01', WHERE total_cents < 5000
  • Sorting: ORDER BY created_at DESC reads the index in order rather than sorting the result set
  • IN lists and BETWEEN
  • Prefix LIKE: WHERE email LIKE 'ada%'

That last one has a limit. A prefix match works because sorted order lets Postgres jump to the right part of the tree. A leading wildcard has nothing to jump to:

sql
-- Can use a B-tree index on email WHERE email LIKE 'ada%' -- Cannot. There is no ordered range to seek to. WHERE email LIKE '%example.com'

One caveat: in a non-C collation, prefix LIKE needs an index built with a text_pattern_ops operator class to be usable for the pattern match.

6. Composite Indexes and the Leftmost Prefix Rule

The most misunderstood part of Postgres indexing, and simple once you picture the structure. A composite index covers more than one column:

sql
CREATE INDEX orders_user_status_idx ON orders (user_id, status);

The index is sorted by user_id first, then within each user_id by status: a phone book sorted by surname, then first name. The leftmost prefix rule follows. The index is usable when the query constrains a prefix of the column list, starting from the left.

Query filters onIndex on (user_id, status)
user_idUsable, seeks straight to the range
user_id and statusUsable, the best case
status aloneGenerally not a good fit

Why does status alone fail. Entries for a given status are scattered throughout the index, once under every user_id value, so there is no contiguous range to seek to. The phone book again: knowing someone is called "Ada" tells you nothing about which page to open.

One precision point, because "cannot be used" overstates it. Postgres may choose a full index scan, reading the whole index and filtering, if the index is much narrower than the table. That is not the seek you wanted. If you filter on status alone, index status first.

Column order rule of thumb: equality columns first, range columns last.

sql
-- Query: WHERE user_id = 42 AND created_at >= '2026-01-01' CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);

This works because user_id = 42 narrows to one contiguous block, and within it created_at is sorted, so the range is another contiguous slice. Reverse the order and there is no single block to seek to.

7. Covering Indexes, INCLUDE, and the Visibility Map

An index only scan avoids the table entirely, which requires the index to hold every column the query touches. INCLUDE adds columns for that purpose without making them part of the sort key:

sql
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at) INCLUDE (total_cents);

total_cents sits in the index leaf pages but takes no part in the ordering, so SELECT user_id, created_at, total_cents FROM orders WHERE user_id = 42 can be answered from the index alone.

One important qualifier. Postgres indexes do not store row visibility information, so the database cannot tell from the index alone whether a row version is visible to your transaction. It consults the visibility map, a compact per table structure marking which pages hold only rows visible to all transactions. If the page is marked all visible, the scan skips the table fetch. Otherwise it goes and checks.

That is what Heap Fetches counts in the plan output. A high number means the scan is doing table lookups anyway, usually because the table was written to recently and vacuum has not caught up. Index only scans depend on VACUUM keeping the map current, which makes autovacuum health part of read performance.

8. Partial Indexes

A partial index covers only the rows matching a predicate:

sql
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

These are underrated. If pending orders are a small fraction of the table and the hot query only looks at pending orders, the index is far smaller than the full one and takes write cost only on matching rows.

The condition for use: the planner must be able to prove your WHERE clause implies the index predicate. WHERE status = 'pending' AND created_at > ... qualifies. status = 'shipped' does not. The pattern fits anywhere the interesting rows are a minority: soft deleted records, unprocessed jobs, open tickets.

9. Expression Indexes and the Function Wrapping Mistake

An index on a column is not used when you wrap that column in a function in the WHERE clause.

sql
CREATE INDEX users_email_idx ON users (email); -- Uses the index SELECT * FROM users WHERE email = 'ada@example.com'; -- Does not. Sequential scan. SELECT * FROM users WHERE lower(email) = 'ada@example.com';

The index stores email values, not lower(email) values. Postgres cannot seek lower(email) in a structure sorted by email, so it computes the function for every row. The fix is an expression index, storing the result of the expression:

sql
CREATE INDEX users_email_lower_idx ON users (lower(email));

The same applies to date(created_at) and any other transformation. Either index the expression, or rewrite the query so the bare column faces the comparison.

10. Beyond B-tree: GIN, GiST, BRIN

Three other types cover what B-tree does not.

GIN (Generalized Inverted Index) suits values containing multiple searchable items: array elements, JSONB keys and values, full text lexemes.

sql
CREATE INDEX users_profile_gin ON users USING gin (profile); -- Supports containment queries such as: SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; CREATE INDEX users_bio_fts ON users USING gin (to_tsvector('english', profile->>'bio'));

GIN is fast to search and comparatively slow to update, which suits data read far more often than written.

GiST is a framework for types where "close to" is the question rather than "equal to": geometric data, ranges, nearest neighbour searches, PostGIS.

BRIN (Block Range Index) stores summaries of ranges of physical pages, such as the minimum and maximum value in each range. It is tiny next to a B-tree and works when the column correlates with physical row order, the classic case being an append only created_at on a large log table. Without that correlation it is not useful.

11. Why Your Index Is Not Being Used

When the plan ignores an index you created, the cause is usually one of five things.

  1. Function wrapping. lower(email) will not use an index on email.
  2. Type mismatch. Comparing a column against a value the planner treats as a different type can prevent index use.
  3. Low selectivity. The condition matches too much of the table and a sequential scan is cheaper. The planner is right.
  4. Stale statistics. The planner is costing plans against an outdated picture of your data.
  5. Leading wildcard LIKE. '%foo' has no ordered prefix to seek.

For the statistics case, ANALYZE resamples the table and updates the planner's picture of it.

sql
ANALYZE users;

Autovacuum normally runs it for you, but after a bulk load or a large migration the statistics can lag badly. If a plan looks inexplicable and the estimates are wildly off, run ANALYZE before anything else.

12. Creating Indexes Without Taking Production Down

CREATE INDEX takes a lock that blocks writes to the table for the whole build. Reads continue. Writes queue up. On a large, busy table that build can take minutes, with every application write waiting behind it. The alternative:

sql
CREATE INDEX CONCURRENTLY orders_user_created_idx ON orders (user_id, created_at);

CONCURRENTLY builds the index without blocking writes. The trade-offs are real:

  • It is slower overall and requires more than one pass over the table.
  • It cannot run inside a transaction block, which matters because many migration tools wrap migrations in transactions by default.
  • It can fail and leave an invalid index behind, which then has to be dropped and recreated. Check \d for invalid indexes after a failure.

I would use CONCURRENTLY by default on any table large enough to matter. The extra build time is not a cost you notice. A write outage is.

13. Finding Indexes You Do Not Need

Indexes accumulate. Someone adds one for a query that later gets rewritten, and the index stays, taking write cost forever for no reads. Postgres tracks usage:

sql
SELECT relname AS table_name, indexrelname AS index_name, idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

An idx_scan of zero means the index has not been used for a scan since statistics were last reset. Before dropping, check that the counters have accumulated long enough to mean something, that the index is not backing a unique or primary key constraint, and that no rare but important path such as a monthly report needs it. Counters are per instance, so an index unused on the primary may be serving a replica.

14. The Problem That Is Usually Not an Index Problem

The N+1 query problem is code running one query to fetch a list, then one more per item in that list. Fetch 100 orders, then the user for each one, and you have made 101 round trips.

Code
SELECT * FROM orders WHERE status = 'pending';   -- 1 query
SELECT * FROM users WHERE id = ?;                -- 100 more

Every one may be individually fast and perfectly indexed. The page is still slow, because the cost is 101 round trips, not the query plans. No index fixes this. A join or a batched IN lookup does:

sql
SELECT o.*, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.status = 'pending';

An ORM makes this easy to introduce by accident: the loop reads as ordinary application code and the queries are invisible. Before hunting for a missing index, count the queries the slow endpoint issues.

Conclusion

Three ideas carry most of the weight. An index is a separate ordered structure costing storage and write throughput, so each one should exist for a reason you can name. The planner tells you what it believes about your data, so read EXPLAIN ANALYZE and compare estimated to actual rows before changing anything. And the index must match the query shape: leftmost prefix, equality before range, expression indexes for wrapped columns.

For a slow query, the sequence I would follow: read the plan, check the row estimates, run ANALYZE if they are off, count the queries the request makes, and only then decide what index serves it. Adding an index first and reasoning later occasionally works, and teaches you nothing about the next slow query.

The planner is usually right. Worth finding out why before overruling it.

Subscribe to my newsletter

Coming soon.

||