Indexes and Query Planning

B-tree — the default index#

PostgreSQL's default index type is a B-tree, and it's the right choice for the overwhelming majority of columns — equality and range queries (=, <, >, BETWEEN, sorting) on ordinary scalar types (integers, text, dates):

CREATE INDEX idx_orders_user_id ON orders (user_id);

But B-tree isn't the only index type PostgreSQL offers, and for certain column types — arrays, JSONB, full-text search vectors — a different index structure is dramatically more effective.

GIN — for JSONB and arrays#

A GIN (Generalized Inverted Index) index is built for columns containing multiple values per row — it indexes each individual element (each key in a JSONB object, each element in an array) rather than the column's value as a whole, making "does this contain X" queries fast.

CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- This containment query can now use the GIN index
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';

Without that index, the containment check above requires scanning and inspecting every row's JSONB blob. With it, PostgreSQL can look up which rows contain the relevant key/value pair directly, the same way a B-tree index avoids scanning a plain column.

The same applies to array columns:

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT * FROM products WHERE tags @> ARRAY['electronics'];

A plain B-tree index on a JSONB or array column technically can be created, but it can only support equality on the entire value — not "does it contain this key" or "does it contain this element," which is almost always the query you actually want against these types. That's the specific reason GIN exists as a separate index type.

GiST — for geometric, range, and full-text data#

GiST (Generalized Search Tree) is a more general, extensible index structure, most commonly used for range types (overlap queries, as in the booking example on the previous page), geometric/geospatial data (via PostGIS), and full-text search. Where GIN excels at fast, static lookups on largely append-only data, GiST supports a broader class of "nearest neighbor" and overlap queries more naturally, generally trading a bit of lookup speed for that flexibility.

CREATE INDEX idx_bookings_during ON bookings USING GIST (during);

-- Overlap queries benefit from the GiST index
SELECT * FROM bookings WHERE during && '[2026-03-03, 2026-03-07)';

EXPLAIN ANALYZE#

EXPLAIN alone shows PostgreSQL's planned execution strategy without running the query. EXPLAIN ANALYZE actually executes it and reports real timing and row counts alongside the plan — essential for confirming an index is genuinely helping, not just theoretically available.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Before an index:

Seq Scan on orders  (cost=0.00..189.50 rows=12 width=48) (actual time=0.021..1.842 rows=12 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 9470
Planning Time: 0.089 ms
Execution Time: 1.869 ms

Seq Scan (sequential scan) means PostgreSQL read every row in the table and discarded the ones that didn't match — Rows Removed by Filter: 9470 makes the cost concrete. After CREATE INDEX idx_orders_user_id ON orders (user_id);:

Index Scan using idx_orders_user_id on orders  (cost=0.29..8.45 rows=12 width=48) (actual time=0.015..0.028 rows=12 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.112 ms
Execution Time: 0.045 ms

Index Scan confirms PostgreSQL used the index directly, and the actual execution time dropped from ~1.9ms to ~0.05ms on this small example — the gap widens enormously as table size grows. cost=0.29..8.45 are the planner's own estimated cost units (start-up cost and total cost, not milliseconds); the actual time=... figures next to them are real measured milliseconds from ANALYZE actually running the query.

Partial indexes#

A partial index indexes only the rows matching a condition — smaller, faster to maintain, and often exactly matches the real query pattern when a query always filters on some known condition anyway:

-- Most queries only ever look at pending orders
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

Because the index only contains pending rows, it stays small and cheap to maintain even if the orders table itself grows to contain millions of shipped and cancelled historical rows that this particular index never needs to touch. Compare this to a full index on (status, created_at), which would work too but stores entries for every status value, most of which this specific query never needs.

Common mistakes#

• Creating a plain B-tree index on a JSONB or array column and expecting containment queries (@>) to use it — B-tree can't accelerate that operator on those types; GIN is required.
• Reading EXPLAIN output without ANALYZE and trusting the estimated row counts as ground truth — the planner's estimates are based on table statistics and can be stale or simply wrong, especially after a large bulk load without an ANALYZE refresh.
• Adding a GIN index to a JSONB column that's updated very frequently, without accounting for GIN's slower write/update cost relative to B-tree — GIN indexes are optimized for read-heavy, containment-style querying, not high-churn columns.
• Ignoring partial indexes when a table's queries consistently filter on the same condition (like status = 'pending'), instead paying to index rows that are never actually queried that way.