Md Nayeem Hossain | Software Engineer

PostgreSQL Indexing Deep Dive: B-tree, GIN and Partial Indexes#

"Just add an index" is the most common advice for a slow query, and often the right one. But the wrong index wastes disk, slows down every write and still does not help the query. Choosing well requires knowing how PostgreSQL actually uses indexes.

Always start with EXPLAIN#

Never guess. Look at the plan:

sql

1EXPLAIN (ANALYZE, BUFFERS)2SELECT * FROM orders3WHERE customer_id = 'c0a8...' AND status = 'paid'4ORDER BY created_at DESC5LIMIT 20;

The things I look for: a Seq Scan on a large table, a big gap between estimated and actual rows (stale statistics), and expensive Sort steps that an index could avoid. ANALYZE runs the query for real, so be careful with writes.

B-tree: the default for a reason#

B-tree indexes handle equality and range queries (=, <, >, BETWEEN, ORDER BY). They are what you get with a plain CREATE INDEX.

Column order in composite indexes#

A composite index works left to right. An index on (customer_id, status, created_at) supports:

WHERE customer_id = ?
WHERE customer_id = ? AND status = ?
WHERE customer_id = ? AND status = ? ORDER BY created_at DESC

It does not efficiently support WHERE status = ? alone, because status is not the leading column.

The rule of thumb: equality columns first, then the range or sort column last.

sql

1CREATE INDEX CONCURRENTLY orders_customer_status_created2  ON orders (customer_id, status, created_at DESC);

For the query above, PostgreSQL can now read the 20 newest paid orders directly from the index with no sort at all.

Covering indexes#

If the index contains every column the query needs, PostgreSQL can answer from the index alone with an index-only scan. Use INCLUDE to add payload columns without making them part of the sort key:

sql

1CREATE INDEX orders_customer_covering2  ON orders (customer_id, created_at DESC) INCLUDE (total, status);

Partial indexes: index only what you query#

If most queries only touch a small subset of rows, index only that subset. A queue table where 99% of rows are done is a perfect example:

sql

1CREATE INDEX jobs_pending ON jobs (run_at) WHERE status = 'pending';

The index stays tiny, fits in memory and is cheap to maintain. Partial indexes are one of the most underused features in PostgreSQL.

They also enable conditional uniqueness — for example, only one active subscription per account:

sql

1CREATE UNIQUE INDEX one_active_subscription2  ON subscriptions (account_id) WHERE status = 'active';

Expression indexes#

If a query filters on an expression, index the expression itself. Case-insensitive email lookup is the classic case:

sql

1CREATE INDEX users_email_lower ON users (lower(email));2 3SELECT * FROM users WHERE lower(email) = lower($1);

The query must use exactly the same expression for the index to apply.

GIN: for values that contain many values#

B-trees index one value per row. GIN indexes handle columns that hold many values: arrays, jsonb documents and full-text search vectors.

sql

1-- Find posts tagged with "postgresql"2CREATE INDEX posts_tags_gin ON blog_posts USING gin (tags);3SELECT * FROM blog_posts WHERE tags @> ARRAY['postgresql'];4 5-- Query inside jsonb6CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);7SELECT * FROM events WHERE payload @> '{"type": "signup"}';

GIN indexes are slower to update than B-trees, so use them where the read benefit is clear.

The cost of every index#

Indexes are not free:

Every insert, update and delete must update every relevant index.
They consume disk and, more importantly, memory in the buffer cache.
Too many indexes on a write-heavy table can cut throughput noticeably.

Find and remove indexes that are never used:

sql

1SELECT relname AS table, indexrelname AS index, idx_scan2FROM pg_stat_user_indexes3WHERE idx_scan = 04ORDER BY pg_relation_size(indexrelid) DESC;

Check on the primary and every replica before dropping anything, since replicas may serve different queries.

Operational habits#

Create indexes with CONCURRENTLY on live tables, to avoid blocking writes.
Keep statistics fresh; autovacuum usually handles it, but run ANALYZE after big data loads.
Review slow query logs or pg_stat_statements weekly. The queries that cost the most in total are rarely the ones you expect.

Conclusion#

Good indexing is driven by real queries, not guesses. Read the plan, order composite columns for equality first and range last, use partial and expression indexes for targeted cases, reach for GIN when one column holds many values, and remove the indexes nothing uses. The result is faster reads without silently slowing your writes.