PostgreSQL Indexing and Query Optimization: A Practical Guide

The incident where a slow query took down the checkout API

During last year's pre-11.11 sale, the checkout API's P99 jumped from 80ms to 2.3s. CPU was pegged, the connection pool drained. We assumed it was application code and dug through business logic for ages — until EXPLAIN ANALYZE revealed a SQL query filtering orders by user_id + created_at range was doing a sequential scan (Seq Scan) over 8 million rows.

One composite index later, P99 was back to 60ms. Since then my habit is: for any slow query, first ask whether it's using an index — then talk about anything else.

Let's go deep on PostgreSQL indexes, focused on "which one when, and why."

1. What indexes does PostgreSQL have

Not all indexes look alike. PostgreSQL ships several access methods:

Type	Use case	Underlying
B-tree	equality, range, sort, prefix	balanced tree (default)
GIN	multi-value / inverted: arrays, `jsonb`, full-text	inverted
GiST	geometry, ranges, similarity, full-text	generalized search tree
BRIN	large tables stored physically in order (e.g. time-series)	block-range summary
Hash	pure equality (mostly superseded by B-tree)	hash buckets

Most business workloads are fine with B-tree. We'll focus on B-tree, then cover GIN and partial/expression indexes — two underused power tools.

2. How B-tree works

A B-tree keeps the indexed column sorted in a balanced tree. Looking up a value descends the tree to a leaf: O(log n). It supports:

equality: WHERE status = 'paid'
range: WHERE created_at > '2026-01-01'
sort: ORDER BY id DESC
prefix LIKE: WHERE email LIKE 'ada%' (note trailing '%ada' can't use it)

Composite indexes and the leftmost prefix

A composite index (a, b, c) is sorted by a, then b, then c. So it's only useful when the query hits the leftmost prefix:

-- can use (user_id, created_at)
WHERE user_id = 5 AND created_at > '2026-01-01';
WHERE user_id = 5;
WHERE user_id = 5 AND created_at = '2026-01-01';

-- cannot (skips user_id)
WHERE created_at > '2026-01-01';

That's exactly why, in our incident, an index on created_at alone did nothing — the query filtered by user_id first.

3. Reading EXPLAIN ANALYZE

Building indexes isn't enough; you must read plans. EXPLAIN estimates; EXPLAIN ANALYZE actually executes (it really runs — for writes use EXPLAIN (ANALYZE, BUFFERS) with care).

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 5 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

Watch three things:

Seq Scan vs Index Scan: the former is full-table and the #1 suspect for slowness.
cost and actual time: in (cost=0.00..123.45 rows=10) the actual time=0.02..1.20 is milliseconds. Read actual time, don't be fooled by estimates.
Buffers: Buffers: shared hit=3 read=120 means 120 disk blocks read, only 3 cached. Low hit rate means data isn't in memory — index isn't covering, or shared_buffers is too small.

A classic bad plan:

Seq Scan on orders  (cost=0.00..182345.00 rows=812 width=64)
  Filter: ((user_id = 5) AND (created_at > '2026-01-01'::date))
  Rows Removed by Filter: 7999999

Rows Removed by Filter near the full row count means row-by-row filtering — the signature of a missing index.

4. Three rules for composite index design

Equality columns first, range columns last. A B-tree can't exploit ordering past the first range condition. (user_id, created_at) is the right order (user_id equality first).
Put higher-selectivity columns first (among equality conditions). High cardinality filters fast.
Fold the ORDER BY column into the index to avoid an extra Sort node. WHERE user_id=5 ORDER BY created_at DESC using (user_id, created_at) reads in order directly — no Sort.

Covering index (INCLUDE)

If a query only needs columns already in the index, PostgreSQL never touches the table — "Index Only Scan." Use INCLUDE to attach columns you SELECT but don't filter on:

CREATE INDEX idx_orders_user_time
  ON orders (user_id, created_at)
  INCLUDE (status, total);

Now SELECT status, total FROM orders WHERE user_id=5 AND created_at > ... returns purely from the index, no heap fetch.

5. Partial and expression indexes

Partial index

If you only frequently query "unpaid" orders, indexing the whole table is wasteful. Index only the rows you care about:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Smaller, cheaper to maintain, and precise for WHERE status = 'pending'.

Expression index

When a query wraps a column in a function, a plain index won't help. Like case-insensitive email lookup:

CREATE INDEX idx_users_lower_email ON users (lower(email));

-- hits the index only this way
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

Another example: aggregation by month often writes date_trunc('month', created_at), so build CREATE INDEX ... ON orders (date_trunc('month', created_at)). The index expression and the query expression must match verbatim to be used.

6. GIN: arrays, jsonb, and full-text search

When one value maps to many elements, GIN is the right tool.

Fields inside jsonb

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

SELECT * FROM events WHERE payload @> '{"type": "click"}';

@> is the jsonb contains operator, which GIN handles efficiently.

Array containment

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT * FROM posts WHERE tags @> ARRAY['go','postgres'];

Full-text search

CREATE INDEX idx_articles_body ON articles USING gin (to_tsvector('zhcfg', body));

SELECT * FROM articles
WHERE to_tsvector('zhcfg', body) @@ to_tsquery('zhcfg', '数据库 索引');

Note the config name in to_tsvector('zhcfg', ...) must match the index exactly, or the index won't be used.

7. Statistics and autovacuum: why indexes "suddenly stop working"

We hit another trap: a query that ran fine for half a year suddenly slowed, its plan regressing from Index Scan to Seq Scan. The SQL hadn't changed — the statistics were stale.

The planner relies on data distribution in pg_statistic to estimate row counts. After heavy writes without a timely ANALYZE, estimates go wrong and the planner may miscalculate and drop the index.

autovacuum also runs ANALYZE, but may lag after bulk loads.
Trigger manually: ANALYZE orders; or VACUUM ANALYZE orders;
Inspect: SELECT * FROM pg_stats WHERE tablename = 'orders';

Lesson: after bulk ETL / deletions, always run ANALYZE explicitly. Also, if a column is extremely skewed (e.g. 99% status='done'), the planner knows querying 'done' is nearly a full table and skips the index — in that case a partial index (only the 1% non-done) is the real fix.

8. Slow-query optimization checklist

My order when debugging:

EXPLAIN ANALYZE — is it a Seq Scan?
Check Rows Removed by Filter; is the estimate wildly off (vs actual rows)?
Check whether WHERE / ORDER BY / JOIN columns are indexed, and the composite order is right.
Function-wrapped column? lower(), date_trunc() etc. need expression indexes.
Does it hit the leftmost prefix?
Are statistics fresh (ANALYZE)?
Is the column too low-selectivity? Consider a partial index.
Can a covering index avoid the heap fetch?

9. Walkthrough: optimizing an order query

Original SQL (slow, 2.1s):

SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
  AND status IN ('paid', 'shipped')
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

8M rows; plan was Seq Scan.

Step 1: build a composite index with equality columns user_id and status first, range created_at last. status is IN (multi-value) in the middle, but B-tree still exploits the user_id ordering prefix:

CREATE INDEX idx_orders_user_status_time
  ON orders (user_id, status, created_at)
  INCLUDE (total);

Step 2: ANALYZE orders; to refresh stats.

Step 3: re-check the plan — confirm Index Scan, small Buffers: shared hit, actual time in the 1ms range.

Why IN can sit in the middle: for (user_id, status, created_at), user_id=42 narrows to that user; status IN (...) narrows further within that range, still preserving created_at order. So ORDER BY created_at DESC LIMIT 50 reads the first 50 rows in reverse index order — no sort, no full scan. This single index took it from 2.1s to single-digit milliseconds.

FAQ

Q1: Is more indexes always better?

Quite the opposite. Indexes slow writes (every INSERT/UPDATE/DELETE maintains them), eat disk, and can confuse the planner. On a heavily-written table, watch out past 5–6 indexes. Every index must answer "which real query does it speed up."

Q2: I built an index but the query still doesn't use it?

Most common: function-wrapped column, missed leftmost prefix, stale stats, too-low selectivity, or the query returns most of the table (then Seq Scan is actually cheaper). Verify with EXPLAIN ANALYZE; don't guess.

Q3: Can LIKE use an index?

'abc%' leading-wildcard can use B-tree (it's ordered). '%abc' trailing-wildcard cannot. For trailing/contained matching, consider a pg_trgm GIN trigram index or full-text search.

Q4: Does a UNIQUE constraint auto-create an index?

Yes. PRIMARY KEY and UNIQUE both auto-create a B-tree index; no need to add one manually.

Q5: How do I see a table's indexes and their sizes?

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';

Recommended tools

When writing SQL and converting results to/from JSON, these ToolsKu tools come in handy:

SQL formatter — tidy messy slow-query SQL for EXPLAIN analysis
JSON → SQL — turn JSON results into INSERTs for load-test data
JSON diff — compare field differences between before/after query results

The essence of index tuning is helping the planner "read less data." Every trick — composite order, partial index, covering index — points at the same thing: let one query finish within the index and very few blocks.