Postgres Indexing: How to Pick the Right Index and Prove It Works

Most “slow query” tickets have the same root cause: PostgreSQL is reading every row in a table when it only needs a handful. The fix is usually an index — but the wrong index type, or an index in the wrong place, can cost you more than it saves. This article gives you the reasoning to choose correctly, not just a list of CREATE INDEX syntax to memorize.

Here’s the direct answer: an index is a supplementary data structure that stores a subset of your table’s columns alongside pointers back to the full rows, so PostgreSQL can narrow its search instead of scanning everything — conceptually the same trick as a book’s index or a phone book’s alphabetical tabs. For most columns, a B-Tree index is the right default. For arrays, JSONB, and full-text search, GIN usually wins. For queries that only ever touch a slice of a large table, a partial index can outperform both while staying small. The rest of this article explains why, and how to confirm it with EXPLAIN ANALYZE rather than taking it on faith.

What a Postgres Index Physically Is

To reason about indexes instead of memorizing index types, you need a mental model of how Postgres stores data without one.

A table lives on disk as a heap — a collection of files (capped at 1GB each by default), with each file divided into fixed-size 8KB pages. As rows get inserted, Postgres appends new pages to hold them. Without an index, the only way to find a row matching some condition is a sequential scan: read every page, check every row, discard what doesn’t match. That’s fine for a few thousand rows. It’s painful at a few million, because the work scales linearly with table size regardless of how selective your WHERE clause is.

An index entry is a tuple of (value, pointer), where the pointer is a ctid — a reference to a specific page and offset inside the heap. Instead of scanning every page, Postgres can look up the value in the index, follow the pointer straight to the matching row (or rows), and skip everything else. This is exactly the phone-book analogy from the top of this article in concrete form: the index holds a sorted, searchable subset of the data plus a pointer back to the full record, so lookups narrow the search space instead of requiring a full scan.

That single idea — value plus pointer, sorted or structured for fast lookup — underlies every index type Postgres supports. They differ in how they organize the values and what kinds of queries they can answer efficiently.

B-Tree: The Default, and Usually the Right Call

If you run CREATE INDEX without specifying a method, you get a B-Tree. It’s a self-balancing tree structure, which gives it logarithmic search time — the number of comparisons needed grows much more slowly than the number of rows, which is why B-Tree indexes stay fast as tables grow from thousands to millions of rows. B-Trees support equality checks, range queries (<, <=, >, >=, BETWEEN), and ORDER BY — because the tree keeps values in sorted order, it can often satisfy ORDER BY without a separate sort step.

-- Equality and range lookups on a timestamp column
CREATE INDEX idx_orders_created_at ON orders (created_at);

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

B-Tree is the right starting point for primary keys, foreign keys, timestamps, status enums with reasonable cardinality, and basically anything you’d filter, sort, or join on. When in doubt, start here.

Hash Indexes: Equality Only, Rarely the Right Choice

Hash indexes store a hash of the column value rather than the value itself, which means they only support equality (=) comparisons — no ranges, no sorting, no BETWEEN. If your query ever needs anything other than an exact match, a hash index can’t help, and a B-Tree index handles equality just as well while also supporting range queries and sorting. That’s the practical reason hash indexes are rare in production schemas: a B-Tree is strictly more flexible for a very similar equality use case, so most engineers default to B-Tree unless they have a specific, measured reason not to.

-- Equality-only lookup on a session token
CREATE INDEX idx_sessions_token_hash ON sessions USING hash (token);

I’d reach for this only after confirming with EXPLAIN ANALYZE that a B-Tree equality lookup is meaningfully slower on your actual data — in most schemas, it won’t be.

GIN: Arrays, JSONB, and Full-Text Search

B-Tree is built around values that can be ordered and compared, which fits scalar columns well. That assumption fits less neatly for columns that hold multiple values per row — a JSONB document, a text array, or a tsvector for full-text search. This is where GIN (Generalized Inverted Index) earns its place.

GIN is an inverted index: instead of mapping one row to one value, it maps each individual key or element — a word, an array entry, a JSON key — to the list of rows that contain it. That’s conceptually the same structure a search engine uses: look up a term once, get every document containing it. The tradeoff is asymmetric. GIN indexes are very fast for reads, but comparatively expensive to maintain, because every insert or update has to update potentially many entries in the index rather than one. If you have a write-heavy table with a GIN index, expect that overhead to show up in your write latency — measure it before you ship.

-- JSONB containment queries
CREATE INDEX idx_products_attrs ON products USING gin (attributes);
-- Speeds up: WHERE attributes @> '{"color": "red"}'

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('english', body));
-- Speeds up: WHERE to_tsvector('english', body) @@ to_tsquery('postgres & indexing')

-- Array containment
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
-- Speeds up: WHERE tags @> ARRAY['postgres']

If your full-text search needs are modest — simple keyword matching on a moderate-sized table — GIN plus tsvector will likely get you there without reaching for a separate search engine. That’s a judgment call based on query complexity and data volume, not a rule.

GiST and SP-GiST: Specialized Structures for Specialized Data

GiST (Generalized Search Tree) is less an index type and more a framework for building custom indexing strategies on data that doesn’t fit neatly into “sortable scalar” or “set of discrete keys.” It’s the index type behind geospatial queries in PostGIS, range type overlap checks, and fuzzy/trigram similarity text search. If you’re storing polygons, points, or tstzrange columns and querying for overlaps or containment, GiST is usually what you want, not B-Tree.

SP-GiST (space-partitioned GiST) is a related but distinct structure suited to data that can be recursively partitioned into non-overlapping regions — think radix-tree-style partitioning for text prefixes, quadtree or k-d tree partitioning for point data, and similar structures for ranges. The textbook use cases are prefix-based string search and spatial point data, where the data naturally splits into partitions rather than sorting linearly. If you’re doing prefix matching on hierarchical identifiers or indexing point-like data, SP-GiST is worth evaluating against GiST and B-Tree specifically — it’s a narrower tool, but it’s the right one for that shape of data.

-- Geospatial queries (requires PostGIS)
CREATE INDEX idx_locations_geom ON locations USING gist (geom);

-- Speeds up prefix matching such as WHERE path ^@ '/docs/postgres/'
CREATE INDEX idx_pages_path_spgist ON pages USING spgist (path);

BRIN: Minimal Footprint for Append-Only, Ordered Data

BRIN (Block Range Index) takes a fundamentally different approach: instead of indexing individual rows, it indexes ranges of heap pages, storing summary information (like min/max values) per block range rather than a full entry per row. That makes BRIN indexes tiny — roughly 1–10% the size of a comparable B-Tree on the same column. The tradeoff is that BRIN only pays off when the indexed column correlates with physical row order on disk, which is naturally the case for append-only, time-ordered tables like event logs or time-series data, where rows are inserted in roughly chronological order and timestamps stay correlated with block position.

-- Time-series data inserted in roughly chronological order
CREATE INDEX idx_events_created_at_brin ON events USING brin (created_at);

Don’t reach for BRIN on a table with no natural ordering — if the indexed values are scattered randomly across pages, block-range summaries stop being selective and the index stops paying for itself.

Partial Indexes: Index Only the Rows You Actually Query

This is the technique most engineers underuse. A partial index only indexes rows matching a specified WHERE condition, rather than the entire table — which makes it smaller and faster to maintain than a full-table index, and it’s a natural fit when most rows will never be queried through that index.

The canonical example: a users table where 95% of accounts are inactive, but every query that matters filters on WHERE is_active = true.

-- Index only rows matching the predicate.
CREATE INDEX idx_active_users_email ON users (email) WHERE is_active = true;

A partial index is built over a subset of the table’s rows defined by a conditional expression (the predicate), so queries whose WHERE clause includes that same condition are generally the ones it can serve.

This index contains only rows where is_active is true, so it is smaller than a full index on email. The same pattern applies to soft-deleted rows (WHERE deleted_at IS NULL), queued jobs (WHERE status = 'pending'), or any column where your access pattern is heavily skewed toward a subset.

Expression and Functional Indexes

Sometimes the thing you filter on isn’t a raw column — it’s a function of one. If you routinely query WHERE lower(email) = 'someone@example.com', an index on email won’t help, because the planner can’t match a raw-column index to a function-wrapped predicate. The fix is to index the expression itself:

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Speeds up: WHERE lower(email) = 'someone@example.com'

This applies to any query predicate that is a function or expression of a column rather than a bare column reference.

Composite Indexes and the Leftmost-Prefix Rule

A composite (multi-column) index combines several columns into one index to serve frequent multi-column queries efficiently. The rule that trips people up is the leftmost-prefix rule: Postgres can use a composite index when the query filters on the leading column(s), but generally not when it only filters on a trailing column while skipping the leading ones.

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Uses the index: filters on leading column customer_id
-- WHERE customer_id = 42

-- Uses the index: filters on both columns in order
-- WHERE customer_id = 42 AND status = 'shipped'

-- Does NOT use this index effectively:
-- WHERE status = 'shipped'

If you query by status alone just as often as by customer_id, you likely need a second, separate index on status — a composite index doesn’t substitute for both access patterns.

Unique Indexes and Constraints

A unique index enforces that no two rows share the same value (or combination of values) across the indexed columns, and it’s the mechanism underlying UNIQUE and PRIMARY KEY constraints. Every primary key you define is, physically, a unique B-Tree index. This matters for reasoning about performance: a UNIQUE constraint on email isn’t just a data-integrity guarantee — it’s also a fully functional index that speeds up equality lookups on that column, so you rarely need a second plain index on the same column.

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Proving It With EXPLAIN ANALYZE

None of the above matters if you can’t verify it on your actual data. EXPLAIN ANALYZE runs the query for real and reports the execution plan Postgres chose, along with actual timing — not just an estimate. The core workflow:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE country = 'DE';

Before an index exists, expect to see a Seq Scan node with a relatively high execution time, scaling with table size. After adding the index:

CREATE INDEX idx_orders_country ON orders (country);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE country = 'DE';

You should now see an Index Scan or Bitmap Heap Scan node, with execution time dropping substantially — the planner is following pointers straight to matching rows instead of reading the whole heap. Always run this before-and-after comparison rather than assuming an index helped — indexes add overhead to every INSERT, UPDATE, and DELETE on the table, so an index that doesn’t measurably improve a real query is pure cost. Watch for the gap between estimated and actual row counts, since a large gap usually means stale statistics and a case for running ANALYZE on the table.

When Not to Index

Indexing is not free, and the instinct to index every WHERE clause column is a common source of write-performance regressions. A few situations where I’d skip it:

• Low-cardinality columns. A boolean or a status column with three possible values gives the planner little to narrow down — a B-Tree on it often isn’t selective enough to beat a sequential scan, since matching rows can still be scattered across most of the table.
• Small tables. If a table comfortably fits in memory and a sequential scan takes microseconds anyway, an index adds write overhead for no measurable read benefit.
• Write-heavy tables with marginal read gains. Every index on a table is maintained on every insert and update — stacking indexes “just in case” on a hot write path is a tradeoff, not a free win.
• Columns you don’t actually query on. It sounds obvious, but auditing pg_stat_user_indexes for unused indexes periodically is worth the five minutes — unused indexes are pure write tax.

Conversely, foreign key columns and anything driving a JOIN or ORDER BY are strong default candidates, since an existing index can help the planner with joins and ordering.

Choosing Quickly: A Decision Summary

Index Type	Best For	Avoid When
B-Tree	Equality, range, sorting on scalar columns — the default choice	Query only needs set/array containment or full-text matching
Hash	Pure equality lookups	Any range, sort, or ORDER BY requirement
GIN	Arrays, JSONB containment, full-text search	Write-heavy tables where insert latency is critical
GiST	Geospatial data, range types, similarity search	Simple scalar equality/range — B-Tree is simpler and sufficient
SP-GiST	Prefix/hierarchical data, IP lookups	Data without a natural partitioned or prefix structure
BRIN	Large, sequentially-ordered, append-only tables (time-series, logs)	Data with no correlation to physical row order
Partial	Queries that only ever touch a known subset of rows	Access patterns span most of the table anyway

The Takeaway

Index selection is a judgment call grounded in two questions: how is this data physically organized on the heap, and what shape are the queries that actually hit this table? B-Tree is the right default for most scalar columns. GIN takes over when a column holds multiple values per row. Partial indexes shrink the problem when your real traffic only touches a slice of the table. GiST, SP-GiST, and BRIN are narrower tools for geospatial, hierarchical, and time-ordered data respectively. None of it is worth shipping on faith — run EXPLAIN ANALYZE before you add an index and after, on your real data, and let the numbers decide.

FAQ

Does PostgreSQL pick the index automatically, or do I have to tell it which one to use? The query planner chooses which index (if any) to use for a given query based on its cost estimates and table statistics — you create the index, but you don’t force its use directly in normal querying. If an index isn’t being used when you expect it to be, check EXPLAIN ANALYZE first; stale statistics or low selectivity are common culprits.

How do I know if an existing index is actually being used? Run EXPLAIN ANALYZE on your real, representative queries and look for Index Scan, Index Only Scan, or Bitmap Heap Scan nodes rather than Seq Scan.