PostgreSQL Index Usage and Optimization

PostgreSQL Index Usage and Optimization

Indexing is the single biggest lever in SQL performance, and it is also the category where most of the bad advice lives. "Add an index" solves a narrow class of problems. "Add the right index, in the right shape, for the right query, and drop the ones you don't need" is the actual job — and it's more design work than most teams expect.

This article is the second in the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series. We assume you can already read EXPLAIN output. The running dataset is the same 500,000-row sim_bp_orders / 200,000-row sim_bp_users / 50,000-row sim_bp_products tables on Neon Postgres 17.8; every EXPLAIN block below is captured from a real run against that database.

We'll cover: when the planner actually uses an index, the four index design choices that matter most (column selection, order, covering, partial), the less-common index types and when they beat btrees, how to find unused indexes, and four cases where not adding an index is the correct call.

When the planner picks an index

An index is a data structure; "using an index" is a planner decision. PostgreSQL looks at the available indexes on a table, estimates the cost of each candidate plan — sequential scan, index scan, index-only scan, bitmap scan — and picks the cheapest. Three things drive that choice:

Selectivity. The estimated fraction of rows the query will return. If the filter returns 0.1% of rows, an index scan is almost always cheaper. If the filter returns 30%, it depends on the rest of the query shape, including whether an ORDER BY or LIMIT interacts with the access path. If the filter returns 70%, the planner will almost always choose a sequential scan because visiting most of the heap sequentially costs less than reading index pages plus random heap I/O.

Correlation. If the rows matching the filter are physically clustered on disk, the planner's random-access penalty shrinks and an index scan becomes more attractive. If they're scattered, random I/O dominates and seq scan wins. The pg_stats.correlation column (range -1 to 1) tells you how clustered each column's values are. Time-series tables (created_at) often have near-1 correlation because they're append-mostly; status columns usually hover near 0.

Cost parameters. random_page_cost (default 4.0) vs seq_page_cost (default 1.0). On SSD-backed storage those defaults are too conservative; lowering random_page_cost to 1.5 or 2.0 makes the planner reach for indexes more readily. Setting it below seq_page_cost is almost always wrong — it implies random I/O is faster than sequential, which isn't true on any real storage, and the planner will make odd choices. If you're tempted to go there, you probably want to raise effective_cache_size instead.

The MyDBA analyzer rule no_index_usage fires when a plan has at least one Seq Scan, no index-type nodes (nothing with Index in the node type — so no Index Scan, Index Only Scan, or Bitmap Index Scan), and more than two nodes total. That last condition filters out the trivial Seq Scan → Aggregate kind of plan where no index is needed. The rule is a signal, not a verdict — some queries genuinely don't want an index — but when it fires, it's usually worth checking whether an obvious filter or join column is un-indexed.

Lookup on a primary key — the boring case that matters

The cheapest index in any database is the primary-key btree:

SELECT * FROM sim_bp_users WHERE user_id = 12345;
Index Scan using sim_bp_users_pkey on sim_bp_users
  (cost=0.42..8.44 rows=1 width=51) (actual time=8.683..8.686 rows=1 loops=1)
  Index Cond: (sim_bp_users.user_id = 12345)
  Buffers: shared read=4
 Execution Time: 9.700 ms

Four shared-buffer reads for a 200,000-row table: some combination of btree-descent pages plus the heap page for the matching row. You can't deduce the exact split from the Buffers line alone — it's the aggregate for the whole node — but "small enough to fit in a handful of pages" is the signal you're looking for. The 9.7 ms execution time is likely dominated by the cold-cache reads against Neon's networked storage; on a warm-cache benchmark this drops to sub-millisecond. This is the shape every OLTP single-row lookup should have.

A lookup that requires four reads instead of four hundred is the single biggest win in the indexing toolbox, and it is almost always available for every query that filters on a unique column. The problems start as soon as filters, joins, and ordering get more interesting.

The four design choices that matter

1. Column selection — matching the query shape

An index only helps queries whose filters (WHERE), joins (ON), or ordering (ORDER BY) can use it. A composite index on (user_id, created_at) helps:

It does not help WHERE created_at > ? in isolation, because the index is organised first by user_id — to find all recent rows regardless of user, PostgreSQL would have to read every leaf page. This is the leftmost-prefix rule: a btree composite index can answer queries that use a contiguous prefix of its columns, starting with the leading one. Skip-scan (using a non-leading column without an equality on the leading one) is not efficient on PostgreSQL btrees for reasonable-cardinality leading columns.

Rule of thumb for multi-column btrees: leading columns should be equality predicates, trailing columns range predicates or sort keys. (tenant_id, created_at), not (created_at, tenant_id).

2. Partial indexes — when 80% of the table is irrelevant

When a query always filters on a specific predicate that eliminates most of the table, a partial index restricted to the matching rows is smaller, faster to scan, and cheaper to maintain:

CREATE INDEX idx_bp_orders_pending_recent
    ON sim_bp_orders (created_at)
    WHERE status = 'pending';

The index only contains rows where status = 'pending', so it's roughly one-fifth the size of a full index on created_at (pending is 20% of sim_bp_orders). The planner will use this index for any query whose WHERE clause implies status = 'pending' — it proves this by theorem-proving over the predicates, so WHERE status = 'pending' AND created_at > now() - interval '1 day' works, but WHERE status IN ('pending', 'shipped') AND created_at > now() - interval '1 day' doesn't (because the IN predicate doesn't imply the partial predicate).

Partial indexes have two gotchas. First, they're fragile to query rewording: a function on the indexed column, a cast, or a change of predicate can stop the implication proof from succeeding even when the rewritten query is logically equivalent. Second, they pay write cost on every insert, update, and delete that matches the partial predicate — so a partial index on pending still has to be maintained when orders transition into pending from null/unset states.

3. Covering indexes — eliminating heap fetches

INCLUDE tucks non-key columns into the leaf pages of the index. The columns are not part of the index's key order, so they don't affect sort behaviour or selectivity — they just ride along so that Index Only Scan can serve the query without touching the heap:

CREATE INDEX idx_bp_orders_pending_by_amount
    ON sim_bp_orders (total_amount_cents DESC)
    INCLUDE (order_id, user_id, created_at)
    WHERE status = 'pending';

A query that SELECTs any combination of order_id, user_id, total_amount_cents, created_at from this index can be served entirely from index pages — provided the visibility map marks the relevant heap pages as all-visible. On a write-heavy table where autovacuum can't keep up, you may still see non-zero Heap Fetches: in the EXPLAIN output, which defeats most of the benefit. Vacuum-aware autovacuum_vacuum_insert_scale_factor tuning (PostgreSQL 13+) helps keep the visibility map fresh on append-mostly tables.

INCLUDE columns cannot be used for index conditions — you can't filter on them via the index. If you need that, put the column in the key. The rule is: put columns used for filtering, joining, or ordering in the key; put columns you're only retrieving in INCLUDE.

4. Expression indexes — indexing computed values

A btree on email can't serve WHERE lower(email) = ? or WHERE lower(email) LIKE 'prefix%' because the indexed value (email) isn't the value the predicate is comparing (lower(email)). Concretely, a case-insensitive prefix search on a 200k-row table:

Gather  (cost=1000.00..5841.09 rows=1000 width=25) (actual time=0.553..122.758 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on sim_bp_users
        Filter: (lower((email)::text) ~~ 'user12%'::text)
        Rows Removed by Filter: 94444
 Execution Time: 122.833 ms

Parallel seq scan, 94,000 rows filtered per worker, 122 ms. The fix is an expression index on the exact computed value the query uses — and for prefix LIKE to work through the index, the index needs an operator class that orders text the way LIKE compares it:

CREATE INDEX idx_bp_users_email_lower
    ON sim_bp_users (lower(email) text_pattern_ops);

For equality on lowercased email, a plain CREATE INDEX ... (lower(email)) is enough. For prefix LIKE, text_pattern_ops is needed because PostgreSQL can only rewrite LIKE 'prefix%' into an index range scan when the index orders text by byte value (which text_pattern_ops does) rather than by the database's default collation rules (which usually do locale-aware comparison that doesn't align with byte order). The citext extension is a different solution specifically for case-insensitive equality but not prefix search.

The related case is the existing idx_sim_bp_users_email_pattern index on email text_pattern_ops, which makes non-case-sensitive prefix queries fast:

Index Only Scan using idx_sim_bp_users_email_pattern on sim_bp_users
  (cost=0.42..29.87 rows=20 width=8) (actual time=0.057..24.729 rows=20 loops=1)
  Index Cond: ((email ~>=~ 'user12'::text) AND (email ~<~ 'user13'::text))
  Filter: ((email)::text ~~ 'user12%'::text)
  Heap Fetches: 0
 Execution Time: 24.757 ms

The Index Cond uses ~>=~ and ~<~ — these are real PostgreSQL operators from text_pattern_ops that do byte-order comparisons, distinct from the default locale-aware >= and <. PostgreSQL has rewritten LIKE 'user12%' into a bounded range scan using those operators. 24.7 ms vs 122.8 ms — five times faster, and that gap widens on larger tables.

Index types beyond btree

GIN — when equality becomes containment

Generalised Inverted Indexes are for values that have internal structure — arrays, JSONB, full-text search vectors, trigrams. A btree on a JSONB column can only answer "equal to this exact document"; a GIN index can answer "contains this key", "contains this value", "contains this key-value pair":

CREATE INDEX idx_events_data_gin
    ON events USING gin (event_data jsonb_path_ops);

-- Now this is sargable:
SELECT * FROM events WHERE event_data @> '{"type": "purchase"}';

jsonb_path_ops is a more specialised operator class than the default jsonb_ops: it indexes only the @> operator (containment) but produces a significantly smaller and faster index. Use it unless you need the other JSONB operators.

GIN with pg_trgm turns substring LIKE queries (LIKE '%needle%') into index-backed scans — the index stores trigrams (three-character substrings) of the column, and PostgreSQL decomposes the query pattern the same way to find candidate rows. Requires the pg_trgm extension, which sim_bp_users already has installed.

BRIN — when the data is physically ordered

Block Range INdexes are cheap summaries of row-ranges rather than per-row pointers. For each page range (128 pages by default), BRIN stores the minimum and maximum values in the range. On highly correlated data — a created_at column on an append-only sim_bp_orders — a BRIN index is orders of magnitude smaller than a btree and nearly as fast for range queries, because the summary lets the planner skip entire ranges of pages whose min/max don't overlap the filter:

CREATE INDEX idx_bp_orders_created_at_brin
    ON sim_bp_orders USING brin (created_at);

For our 500,000-row orders table, a BRIN index is ~24 kB; a btree on the same column is ~5 MB. The BRIN loses effectiveness immediately if the data isn't correlated — on a shuffled table, the min/max of every page range overlaps the whole value domain and the planner can't skip anything. BRIN is effectively useless on uncorrelated columns and brilliant on time-series data.

GiST / SP-GiST — non-1D data

Geometric types (PostGIS), ranges (int4range, tsrange), and fuzzy matching use GiST or SP-GiST indexes. The one DBA insight: GiST is a pluggable general-purpose structure; different operator classes give you different behaviours (R-trees for spatial, range-trees for ranges, trigram matching for text). SP-GiST is for data that partitions naturally (quadtrees, kd-trees, radix trees) — most applications don't need it. If you're using PostGIS, you're almost certainly using GiST. If you're not, you probably don't need it.

Hash indexes — usually not

Hash indexes only support equality (=). They were crash-unsafe until PostgreSQL 10 and have historically been slower than btrees even for point lookups. There are narrow cases where a hash index beats a btree for equality (specifically, wide keys where tree depth matters), but the default answer is "use a btree" and you should need a specific measurement to justify a hash.

When NOT to add an index

1. Write-heavy, read-light tables

Every index is write cost: every INSERT, UPDATE (on indexed columns), and DELETE has to maintain every matching index. A table updated a million times a day with seven unused indexes is spending a lot of write budget for nothing.

2. Low selectivity on low-traffic queries

A btree on a boolean is_active column, where 90% of rows are active, is never going to be used — 90% selectivity isn't cheaper via index scan. Even if the query is "find inactive users" (10% selectivity), a partial index WHERE is_active = false is almost always better than a full btree on the column.

3. Queries that need most of the table anyway

Reports that aggregate over large time windows or return most of the rows are best served by a seq scan (possibly parallel). An index would just add random-access overhead.

4. Redundant indexes

Every btree on (a, b, c) subsumes the leftmost-prefix queries that would be served by (a, b) and (a). If all three exist, the shorter ones are usually wasted write cost. Index consolidation — audit, drop the prefixes, keep the longest — is a reliable performance win on mature tables.

Finding unused indexes

The pg_stat_user_indexes view tracks scan counts per index since the last stats reset. Unused indexes are a pure write-cost tax with no read benefit:

SELECT
    s.indexrelname AS index_name,
    s.relname AS table_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
    s.idx_scan
FROM pg_stat_user_indexes s
WHERE s.schemaname = 'public'
  AND s.idx_scan = 0
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint c
      WHERE c.conindid = s.indexrelid AND c.contype IN ('p', 'u', 'x')
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

On our running database, a real result looked like:

| index_name | size | idx_scan | |---|---|---| | idx_sim_bp_users_username_pattern | 6184 kB | 0 | | idx_sim_bp_users_email_pattern | 7960 kB | 1 |

One 6 MB index with zero scans is a straightforward drop. The NOT EXISTS clause on pg_constraint skips primary-key and unique-constraint indexes — those should never be "dropped as unused" even if no explicit query ever looks them up, because they enforce integrity and are used internally for FK validation.

Two caveats on idx_scan = 0. First, the counter resets when pg_stat_reset() is called, which happens on major version upgrades and manual resets — an index with idx_scan = 0 might just have been reset recently. Check the timestamp of the stats snapshot before acting. Second, a replica's stats only count scans that happened on that replica; dropping an index from the primary based on replica stats alone is dangerous.

MyDBA's Index Advisor page automates this workflow — it combines pg_stat_user_indexes, size estimates, and foreign-key coverage checks to produce a ranked list of recommendations, with the specific DROP INDEX statements.

A complete example — adding the right index

Back to the pillar's running example. The dashboard query was:

SELECT order_id, user_id, total_amount_cents, created_at
FROM sim_bp_orders
WHERE status = 'pending'
ORDER BY total_amount_cents DESC
LIMIT 50;

The question isn't "should we add an index?" — the plan shows a 51 ms sequential scan over 500k rows with a top-n heapsort, which is obviously improvable. The question is which index. Three plausible candidates:

1. CREATE INDEX ON sim_bp_orders (status) — cheapest to add, most general-purpose, but the planner would still need a sort step. Would reduce the seq scan to a bitmap index scan of ~100k rows but leave the top-n heapsort intact. 2. CREATE INDEX ON sim_bp_orders (status, total_amount_cents DESC) — solves both the filter and the sort. The sort is free because the index is already ordered on the trailing column within each status group. All 500k rows are in the index. 3. CREATE INDEX ON sim_bp_orders (total_amount_cents DESC) WHERE status = 'pending' — only the 100k pending rows are indexed. Smaller, faster to maintain, but only helps pending queries.

The pillar article in this series went with option 3 plus INCLUDE (order_id, user_id, created_at) for Index Only Scan. That was the right call for this specific query, but if the same dashboard later adds filters for status IN ('pending', 'processing'), we'd need option 2 instead. Design indexes for the query you have, but re-read the plans every six months to notice when the query has changed underneath you.

Next steps

Indexing intersects every category of PostgreSQL optimisation — joins, aggregates, WHERE clauses. The next three articles make that explicit: