Summary#

For queries shaped like WHERE user_id = ? AND created_at >= ... ORDER BY created_at DESC LIMIT N, prefer a composite B-tree index whose leading column is the equality predicate and whose next column is the recency/order key, e.g. (user_id, created_at DESC).

DESC is a useful way to document the intended newest-first access pattern, but for a single trailing sort column PostgreSQL can also scan a normal B-tree index backward, so (user_id, created_at) may also satisfy ORDER BY created_at DESC. Validate with EXPLAIN (ANALYZE, BUFFERS) on the real workload.

Key Points#

• Equality predicate column first: user_id should lead the index for this query shape.
• Range filter and ordering column second: created_at should follow so the planner can scan rows for one user in timestamp order.
• (user_id, created_at DESC) matches the newest-first intent directly and can avoid an extra sort while allowing early stop for LIMIT N.
• (user_id, created_at) is often also viable for this exact single-column descending order because PostgreSQL can scan B-tree indexes backward; DESC is not strictly required unless mixed sort directions or more complex ordering semantics make index order matter.
• A single-column index on only created_at or only user_id is usually weaker for this pattern because it cannot satisfy both the user filter shape and the requested order as efficiently.

Example#

CREATE INDEX CONCURRENTLY idx_events_user_id_created_at_desc
ON events (user_id, created_at DESC);

Alternative that may also work for the same query shape:

CREATE INDEX CONCURRENTLY idx_events_user_id_created_at
ON events (user_id, created_at);

Caveats#

• Always confirm with EXPLAIN (ANALYZE, BUFFERS) because data distribution, table size, correlation, statistics, and selectivity can change the chosen plan.
• SELECT * usually prevents a true index-only scan unless all referenced columns are covered and heap pages are all-visible, but the composite index can still greatly narrow the scan and preserve order.
• If this query is extremely hot and always returns the same small column subset, a covering index with INCLUDE (...) may help further.
• Indexes improve reads but add write, storage, and maintenance cost; avoid adding both ASC and DESC variants unless plans prove both are needed.

Related#

• personal_vault/knowledge/dev/postgresql-advanced.md

Sagwan Revalidation 2026-06-05#

• verdict: revise
• note: 핵심 복합 B-tree 권고는 유지하되, DESC가 단일 trailing sort column에서는 필수 조건이 아니라는 caveat를 본문에 반영한다.