PostgreSQL Index Strategy: Matching the Index to the Query, Not the Other Way Around

A query that ran in 20ms against a 10,000-row table can take 4 seconds once that table hits 10 million rows, and the SQL hasn't changed at all. What changed is that PostgreSQL now has to read every row to find the handful that match, instead of jumping straight to them. That's the core problem an index solves: it's a separate structure maintained alongside the table that stores a sorted or otherwise organized reference to rows, so the planner can locate matches without scanning the whole thing. Each index type below includes a runnable CREATE INDEX example so you can match the shape to your query.

The hard part isn't knowing that indexes help — it's picking the right one. PostgreSQL gives you several index access methods, and each one is built around a different shape of query: equality lookups, ranges, multi-valued columns like arrays and JSONB, geometric containment, sparse hierarchical data, or physically ordered append-only logs. Pick based on how your query filters, not habit. Below is a working decision framework, runnable CREATE INDEX statements for each type, and the EXPLAIN ANALYZE technique to prove your choice actually did something instead of just assuming it did.

The default: B-Tree

If you're not sure what to reach for, start here. B-Tree is PostgreSQL's balanced-tree default, and it's the right answer for the large majority of indexing decisions. It handles equality and range comparisons on sortable data — the planner will consider a B-Tree index for =, <, >, <=, >=, and BETWEEN. It can also return rows in sorted order, which lets PostgreSQL skip a separate sort step when your query has an ORDER BY on the indexed column. And it works for LIKE pattern matching, but only when the pattern is anchored to the start of the string, like 'smith%' — a pattern like '%smith' or '%smith%' can't use a B-Tree index because there's no fixed prefix to seek to.

A good candidate column for a B-Tree index is one with high cardinality — lots of distinct values — that shows up in WHERE, JOIN, or ORDER BY clauses. Foreign key columns used in joins and lookups are good candidates too, since they're routinely filtered or joined on.

-- Equality and range lookups on order date
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Supports: WHERE created_at > '2026-01-01'
--           WHERE created_at BETWEEN '2026-01-01' AND '2026-02-01'
--           ORDER BY created_at DESC

-- Anchored prefix search works, trailing wildcard search does not
CREATE INDEX idx_users_email_prefix ON users (email);
-- Helps: WHERE email LIKE 'damian%'
-- Does NOT help: WHERE email LIKE '%@gmail.com'

Hash: equality only, rarely worth it

A Hash index supports only equality comparisons — no ranges, no sorting. Hash indexes are WAL-logged and crash-safe in modern PostgreSQL releases. In practice, a B-Tree already handles equality queries well and additionally supports range and sort operations that a Hash index can't, so there's rarely a compelling reason to reach for Hash over B-Tree unless you've specifically measured a difference on your workload.

CREATE INDEX idx_sessions_token_hash ON sessions USING hash (session_token);
-- Supports only: WHERE session_token = 'abc123...'

GIN: when a single row holds multiple values

This is where B-Tree stops being the answer. If your column holds an array, a JSONB document, or a full-text search vector, you're not querying "does this column equal X" — you're querying "does this column contain X among possibly many values." That's a GIN index's job. GIN (Generalized Inverted Index) is built for multi-value columns like arrays, JSONB, and full-text vectors, and it's optimized around containment and existence operators: @>, <@, ?, and &&.

The tradeoff is build and write cost. GIN indexes are slower to build and update than B-Tree indexes, in exchange for much faster reads on containment and search queries. By default, new entries don't go straight into the main index structure — they're buffered in a pending list, controlled by the gin_pending_list_limit setting, and when that list flushes it can stall writes and temporarily slow lookups. If you're bulk-loading data into a GIN-indexed table, that pending-list behavior is worth knowing about before you're debugging an unexplained write stall.

-- JSONB containment: find users with a specific metadata key/value
CREATE INDEX idx_users_metadata ON users USING gin (metadata);
-- Supports: WHERE metadata @> '{"plan": "pro"}'

-- Array containment: find posts tagged with 'postgres'
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
-- Supports: WHERE tags @> ARRAY['postgres']

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('english', body));

GiST: geometric, range, and nearest-neighbour data

GiST (Generalized Search Tree) is less a single index type and more infrastructure for building several — it supports geometric data types, range types, and nearest-neighbour searches through different operator classes. If you're storing geographic coordinates, date ranges, or doing "find the 10 closest points" queries, GiST is the mechanism underneath. One wrinkle: GiST indexes are lossy, meaning a lookup can return candidate rows that aren't actually true matches, and PostgreSQL filters those out with a recheck step against the heap. That recheck is invisible to you as a query-writer, but it does mean GiST index-only scans only work for some operator classes rather than universally.

-- Range overlap queries, e.g. booking date ranges
CREATE INDEX idx_bookings_period ON bookings USING gist (stay_period);
-- Supports: WHERE stay_period && daterange('2026-06-01', '2026-06-10')

SP-GiST: sparse, non-balanced data

SP-GiST supports non-balanced, disk-based structures like quadtrees, k-d trees, and radix tries, which fit hierarchical or sparse data — IP address ranges are a commonly cited example. The "non-balanced" part is the key distinction from GiST: SP-GiST is a better fit when your data naturally partitions unevenly, rather than clustering around a center the way geometric data often does.

-- IP range containment lookups
CREATE INDEX idx_access_log_ip ON access_log USING spgist (client_ip inet_ops);

BRIN: huge, append-only, physically ordered tables

BRIN (Block Range Index) takes a completely different approach: instead of indexing individual rows, it stores only the minimum and maximum value per block range, which makes it far smaller than an equivalent B-Tree on the same column. The catch is that BRIN only pays off when physical row order correlates with the indexed column's value — which is naturally true for an append-only log table where rows are inserted in timestamp order, and much less true for a table that gets updated and reshuffled out of order.

CREATE INDEX idx_events_logged_at_brin ON events USING brin (logged_at);
-- Ideal for: append-only event/log tables filtered by time range
-- WHERE logged_at > now() - interval '7 days'

Index type comparison

Index type	Best for	Supported comparisons	Index-only scan support
B-Tree	Equality, range, sort, anchored prefix match	=, <, >, <=, >=, BETWEEN, prefixLIKE	Always supported
Hash	Equality only	=	Not supported
GIN	Multi-valued columns: arrays, JSONB, full text	@>, <@, ?,&&	Not supported
GiST	Geometric, range, nearest-neighbour	Operator-class dependent	Supported for some operator classes
SP-GiST	Hierarchical/sparse data (IP ranges, tries)	Operator-class dependent	Supported for some operator classes
BRIN	Large, physically ordered, append-only tables	Range queries on correlated columns	Not supported

Composite indexes: order is not cosmetic

A composite index on multiple columns sorts rows by the first column, then by the second within each value of the first, and so on — this is the leftmost-prefix rule. The practical consequence: an index on (a, b, c) supports queries filtering on a alone, on a and b together, or on all three, but it does not help a query that filters only on b, only on c, or on b and c without a. If your query patterns genuinely need to filter on b without a, you need a separate index for that — the composite index on (a,b,c) won't be reused for it.

Column order within the composite index matters for a second reason beyond the leftmost-prefix rule: a common rule of thumb is to put the high-selectivity equality column first and range-filtered columns after it. Put the column that narrows the result set most aggressively up front, so the range scan on the later column only has to walk a small slice of the index.

-- Query: WHERE tenant_id = 42 AND created_at > '2026-01-01'
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at);
-- tenant_id (equality, high selectivity) first, created_at (range) second

Partial indexes: index only the rows you actually query

Most tables have skewed data — a status column where 95% of rows are 'completed' and you only ever query the rare 'pending' ones, or an is_deleted flag where soft-deleted rows outnumber live ones but nobody queries them. A partial index, built with a WHERE clause on the index definition itself, covers only rows matching that predicate, which keeps it smaller and more focused than indexing the whole column. This is a particularly good fit for skewed distributions where the common value is never queried directly and the rare value is queried constantly.

-- Only index active (non-deleted) users, since that's the only thing queried
CREATE INDEX idx_users_active_email ON users (email) WHERE deleted_at IS NULL;

-- Only index pending orders — the "completed" bucket doesn't need to be in here
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

Covering indexes and index-only scans

Normally, even after an index points PostgreSQL at the right rows, it still has to visit the underlying table (the heap) to fetch columns that aren't in the index. A covering index avoids that extra hop by including every column the query needs — in PostgreSQL, via the INCLUDE clause — which lets the planner satisfy the query entirely from the index in what's called an index-only scan. Columns added through INCLUDE are stored in the index's leaf pages but aren't part of the sort key, so they add to the index's size without bloating the internal tree structure used for seeking. Whether an index-only scan actually happens also depends on the visibility map; if a page isn't marked all-visible, PostgreSQL still has to check the heap, which is one more reason routine vacuuming matters on busy tables.

-- Query: SELECT user_id, plan FROM subscriptions WHERE tenant_id = 42
CREATE INDEX idx_subs_tenant_covering ON subscriptions (tenant_id) INCLUDE (plan);

Expression indexes: index the computed value, not the raw column

If your WHERE clause wraps a column in a function — most commonly lower(email) = 'x' for case-insensitive lookups — a plain B-Tree index on email won't help, because the index stores the raw value, not the transformed one. An expression index solves this by indexing the output of the function itself.

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Supports: WHERE lower(email) = 'damian@example.com'

Clustered vs non-clustered: how rows actually sit on disk

An index is a separate structure from the table data, and whether rows are physically ordered by the indexed column affects how well range-oriented indexes like BRIN work.

Specialized extension index types

A few PostgreSQL extensions add index support for workloads the built-in types don't cover well. These aren't built-in access methods, so check the extension docs before you conclude a query shape has no good index solution.

Verify it: don't guess, run EXPLAIN ANALYZE

Creating an index is not the same as proving it helped. Run the query through EXPLAIN ANALYZE before and after creating the index, and compare the plan:

EXPLAIN ANALYZE
SELECT id, created_at FROM orders
WHERE tenant_id = 42 AND created_at > '2026-01-01';

Look for two things in the output. First, the scan type: Seq Scan means PostgreSQL read the whole table, while Index Scan or Index Only Scan means it used your index. Second, the actual time reported next to each plan node — that's measured execution time, not an estimate, so it tells you whether the index scan is genuinely faster rather than just theoretically applicable. If you still see a sequential scan after adding an index you expected to be used, check that the query's filter actually matches the leftmost-prefix rule for composite indexes, or the predicate for partial indexes — a mismatch there is the most common reason a freshly created index gets ignored.

Frequently asked questions

Do I always need to create indexes manually, or does PostgreSQL add them automatically?

PostgreSQL does not add indexes on your behalf based on query patterns — you decide which columns to index based on how your queries filter, join, and sort data.

Can I have too many indexes on one table?

Yes in effect, if not by a hard rule — every index adds write overhead because it must be updated on every insert, update, and delete, so indexing columns that are rarely queried trades write performance for a benefit you never collect.

Why would an index exist but still not get used?

Common causes include the query not matching the leftmost-prefix rule on a composite index, the query falling outside a partial index's WHERE predicate, or the table being small enough that a sequential scan is genuinely faster — confirm with EXPLAIN ANALYZE rather than assuming.

Should I default to GIN whenever I use JSONB columns?

Only if you're actually querying containment or existence within the JSONB structure — if you only ever fetch the whole document by a separate key, a B-Tree on that key is simpler and cheaper to maintain.