Prove a database index will help before adding it

“Add an index” is often offered as if an index were a speed switch. It is a second data structure with its own ordering, storage and maintenance cost. The database may use it to avoid reading most of a table—or ignore it because the query needs too much data, the predicate does not match the index, or the statistics predict a sequential scan is cheaper.

Capture the exact slow statement and parameters

Use the query emitted by the application, including representative values. A test with one convenient customer ID may have completely different selectivity from production. For PostgreSQL, begin without executing writes:

EXPLAIN (COSTS, VERBOSE)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 12345
  AND created_at >= DATE '2026-01-01'
ORDER BY created_at DESC
LIMIT 100;

For a read-only statement in a safe environment, collect actual measurements:

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 12345
  AND created_at >= DATE '2026-01-01'
ORDER BY created_at DESC
LIMIT 100;

ANALYZE executes the statement. Do not attach it casually to UPDATE, DELETE or operationally expensive queries.

Read the plan as a flow of rows

Look beyond “Seq Scan” versus “Index Scan.” Compare estimated rows with actual rows, loops, rows removed by filters, buffer hits and reads, sort method, and total execution time. A severe estimate error often points to stale statistics or correlated columns rather than a missing index.

Plan evidence	Interpretation	Investigation
Many rows removed by filter	Too much data reached the filter	Align index keys with selective predicates
Explicit sort before a small LIMIT	Ordering is not supplied cheaply	Consider index order matching filter and sort
Estimate far from actual	Planner lacks an accurate model	Run ANALYZE; inspect data skew/statistics
Sequential scan returns much of table	Sequential access may be rational	Challenge the query scope before forcing an index

Design column order from the access pattern

For equality on customer, a time range, descending order and a small limit, this is a reasonable candidate:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (id, total);

The leading equality column narrows the tree, and the following timestamp can support the range and ordering. Included columns may allow an index-only scan when visibility conditions permit, but they increase index size. They do not participate in search ordering.

The “most selective column first” slogan is incomplete. Column order must reflect the operator sequence, reusable leftmost prefixes, sort requirement and actual workload. An index on (customer_id, created_at) can directly serve customer-only predicates; the reverse order may not.

Understand why a B-tree helps

A PostgreSQL B-tree is a multi-level ordered structure. Internal pages guide traversal toward leaf pages; leaf entries point toward table rows. This reduces the search space for equality and ordered-range operations, while the ordered leaves can satisfy compatible ORDER BY clauses without a separate sort.

But retrieving a large fraction of a table through scattered index pointers can cost more than reading table pages sequentially. An unused index is not proof of a defective planner.

Measure before and after under comparable conditions

SELECT pg_size_pretty(pg_relation_size('idx_orders_customer_created'));

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 12345
  AND created_at >= DATE '2026-01-01'
ORDER BY created_at DESC
LIMIT 100;

Test multiple representative parameter values. Cache state affects timings, so compare buffer activity and repeated runs rather than presenting one warm-cache number as universal. Keep the original plan with the deployment record.

Account for production build and write cost

Every insert may add an index entry; updates to indexed columns may update the structure; vacuum and backups have more work; memory and storage pressure rise. PostgreSQL’s CREATE INDEX CONCURRENTLY avoids blocking normal writes for the entire build, but it takes longer, performs multiple scans, waits for transactions and can leave an invalid index if it fails. Monitor it and verify validity:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'idx_orders_customer_created'::regclass;

Remove experiments deliberately

If the candidate does not improve the target workload and serves no constraint, document the result and remove it through the approved change path:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

Do not remove a unique or constraint-supporting index based only on low scan counts. Usage statistics can reset, replicas can have different workloads, and month-end queries may be rare but essential.

An index is successful when the measured workload improves enough to justify its ongoing write, storage and operational cost.

Sources: the foundational Stack Overflow explanation (question and accepted answer by Xenph Yan, CC BY-SA), PostgreSQL’s Indexes chapter, B-tree implementation documentation, and CREATE INDEX reference.