Choosing the right index, and the ones never used

The previous lesson was mechanics. This one is judgement: which indexes to actually create, and — harder, and more valuable — which to delete.

The framing that matters: an index is a trade. You are buying read speed with write speed, disk and memory. Most teams have too many indexes, not too few, and nobody ever notices because a redundant index does not break anything. It just quietly taxes every write.

What an index costs

Writes. Measured on the 500,000-row table, inserting 200,000 rows:

About 8×. Every INSERT, UPDATE of an indexed column, and DELETE must maintain every relevant index.

Disk. Same table:

Table alone:          29 MB
Table plus indexes:   46 MB

The indexes are 60% of the table's size. It is common for a heavily indexed table to have more index than data.

Memory, which is the one that bites. Indexes compete with data for shared_buffers. Ten indexes you never use evict the pages you do, and the symptom is everything getting slower with no obvious cause.

Planning time. More indexes means more alternatives to cost. Usually negligible, but measurable at a few dozen indexes on one table.

HOT updates lost. PostgreSQL can sometimes update a row without touching indexes at all — a "heap-only tuple" update — but only if no indexed column changed. Index a column that updates frequently and you forfeit that optimisation for every update, which is a bigger deal than it sounds.

A procedure

Not a rulebook. Six steps, in order.

1. Index primary keys and foreign keys

The primary key is automatic. Foreign keys are not, and should be — see the previous lesson for the query that finds the ones you have missed. This alone fixes most beginner schemas.

2. Index the columns you filter on, driven by data

Not by guessing. Run:

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

EXPLAIN the top few. Index what they filter and join on.

Start from the queries, never from the schema. A column that looks obviously index-worthy and is never filtered on is pure cost.

3. Prefer one composite index to several single-column ones

For WHERE status = 'active' AND created_at > X, a single (status, created_at) beats two separate indexes: PostgreSQL can combine two with a BitmapAnd, but that is slower than one index that answers the whole condition.

Then remove the redundancy: (a, b) makes (a) redundant.

4. Reach for partial indexes

The most underused tool here. If you only ever query a minority of rows, index only those — measured at 320 kB against 3568 kB, an 11× reduction, plus no write cost for the excluded rows.

Candidates: WHERE returned_on IS NULL, WHERE status = 'pending', WHERE deleted_at IS NULL.

5. Verify, do not assume

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Before and after. Compare Buffers, not just time — time varies with cache state, buffers do not. An index you created that the planner ignores is worse than no index, because you are paying for it and believing it helps.

6. Delete what is unused

Which is the rest of this lesson.

Finding indexes to delete

SELECT relname AS table_name, indexrelname AS index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

idx_scan = 0 means the index has never been used since statistics were last reset.

Four things to check before you drop one:

How long have the statistics been collecting? A week of production traffic is a minimum. SELECT stats_reset FROM pg_stat_database WHERE datname = current_database() tells you — and a NULL there means they have never been reset, not that there are no statistics.

Does it back a constraint? A unique index enforcing a business rule shows idx_scan = 0 and is essential. pg_index.indisunique and indisprimary tell you; those are not candidates.

Is it for a monthly or quarterly job? A report that runs on the first of the month looks unused for 29 days.

Are you looking at a replica? Read replicas serve different queries, and each keeps its own statistics. Check every server.

Redundant indexes

Distinct from unused — these are used, but another index would serve just as well. Any index whose key columns are a leading prefix of another's is redundant: (member_id) when (member_id, borrowed_on) exists.

Getting this right in SQL needs more care than it looks, because three things make two similar indexes genuinely different and must be excluded:

SELECT DISTINCT ON (a.indexrelid)
       a.indrelid::regclass   AS table_name,
       a.indexrelid::regclass AS redundant,
       b.indexrelid::regclass AS covered_by,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS reclaimable
FROM pg_index a
JOIN pg_index b
  ON  b.indrelid    = a.indrelid
  AND b.indexrelid <> a.indexrelid
  AND b.indnkeyatts >= a.indnkeyatts
  AND (b.indkey::int2[])[0:a.indnkeyatts-1]    = (a.indkey::int2[])[0:a.indnkeyatts-1]
  AND (b.indclass::oid[])[0:a.indnkeyatts-1]   = (a.indclass::oid[])[0:a.indnkeyatts-1]
  AND (b.indoption::int2[])[0:a.indnkeyatts-1] = (a.indoption::int2[])[0:a.indnkeyatts-1]
  AND a.indpred IS NULL AND b.indpred IS NULL
  AND NOT a.indisprimary
  AND (NOT a.indisunique OR b.indisunique)
JOIN pg_class c     ON c.oid = a.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
                   AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY a.indexrelid, b.indnkeyatts;

Read the three exclusions, because they are the whole point:

• indclass — the operator class. (tag) and (tag text_pattern_ops) index the same column in different orders and serve different queries. Not redundant.
• indoption — sort direction and NULLS placement. (member_id, made_on) and (member_id, made_on DESC) are different indexes.
• indpred — the partial-index condition. A partial index is never redundant against a full one; it is a different set of rows.

Plus NOT a.indisprimary, and (NOT a.indisunique OR b.indisunique) so a unique index is only ever flagged as covered by another unique one — otherwise you would be told to drop a constraint.

I had a shorter version of this query that used a string LIKE on indkey, and it confidently told me to drop a partial index, a text_pattern_ops index and a DESC index, none of which were redundant. Do not trust an index-advice query you have not read.

One thing the output will show you: an exactly duplicated pair appears twice, each naming the other.

 table_name |   redundant    |   covered_by   | reclaimable
------------+----------------+----------------+-------------
 big        | idx_big_member | idx_big_m2     | 3568 kB
 big        | idx_big_m2     | idx_big_member | 3568 kB

That is two identical indexes on (member_id), which happens when two migrations add the same thing. Drop one, not both.

Dropping safely

BEGIN;
DROP INDEX idx_maybe_unused;
-- run your important queries, check the plans
ROLLBACK;                          -- or COMMIT if you are satisfied

DROP INDEX inside a transaction is reversible, which makes this a genuinely safe experiment — though it takes a lock, so do it in a maintenance window.

Or, non-destructively:

UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'idx_maybe'::regclass;

That hides it from the planner while keeping it maintained, so you can reverse it instantly. It is a hack — you are writing to a system catalogue — but it is a well-known one, and safer than a drop you might need to undo on a large table.

And in production: DROP INDEX CONCURRENTLY.

Bloat, the thing nobody tells you

Indexes degrade. Repeated updates and deletes leave dead entries, pages end up half full, and an index can grow to several times its necessary size while getting slower.

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('idx_loans_member');

A freshly built index measures avg_leaf_density of about 90% — that is your baseline. Well below 80%, with a high leaf_fragmentation, means bloat.

The fix:

REINDEX INDEX CONCURRENTLY idx_loans_member;

CONCURRENTLY (PostgreSQL 12 and later) rebuilds without blocking writes. Before that, the trick was to create a new index concurrently and drop the old one.

This is not routine maintenance. autovacuum handles the normal case. Reindex when you have measured bloat, typically on a table with a high update-and-delete churn.

Rules of thumb, with the reasoning

Under about 1,000 rows, do not bother. The whole table is one or two pages. A scan is faster than the index lookup.

More than 5–10 indexes on one table deserves a review. Not a hard limit, but past that the write cost is usually not being paid back.

Do not index a boolean or any column with very few distinct values — unless as a partial index on the rare value. WHERE active = true with 95% active will never use it.

Do not index a column you never filter, join or sort by. Obvious, and yet.

Be careful indexing frequently-updated columns — you lose HOT updates.

Write-heavy tables want fewer indexes; read-heavy tables can afford more. An audit log being appended to a thousand times a second wants the minimum.

The one thing to remember

Measure. Index. Measure again. Delete what does not pay.

Every index you cannot justify from a real query plan is a cost you are paying for nothing.

Check your work

What an index trades. Read speed for write speed, disk and memory.

The measured write cost of four indexes. About 8× on bulk insert.

The measured disk cost. Indexes at 60% of the table size.

The memory cost, and why it is sneaky. Unused indexes evict useful pages from shared_buffers; everything slows with no obvious cause.

What a HOT update is and how an index prevents it. An update that touches no index — lost if any indexed column changes.

Where to start when choosing indexes. The queries, from pg_stat_statements — never the schema.

Why one composite index beats two single-column ones. BitmapAnd works but is slower than one index answering the whole condition.

What to compare before and after. Buffers, because time varies with cache state.

How to find unused indexes. idx_scan = 0 in pg_stat_user_indexes.

Four checks before dropping one. Statistics age, constraint-backing, periodic jobs, replicas.

What makes an index redundant. Its key columns are a leading prefix of another index's — and the operator class, sort direction and partial condition all match.

Three things that make two similar indexes genuinely different. Operator class (indclass), sort direction (indoption), partial condition (indpred).

What to do with a duplicated pair that flags each other. Drop one, not both.

Two safe ways to test a drop. DROP inside a transaction then ROLLBACK, or setting indisvalid = false.

What index bloat is and how to measure it. Dead entries and half-full pages; pgstatindex, watching avg_leaf_density.

How to fix it without blocking. REINDEX INDEX CONCURRENTLY.

The row count below which indexes are pointless. About 1,000.

When a low-cardinality column is worth indexing. Only as a partial index on the rare value.

Practice

1. Time 100,000 inserts on a table with no indexes, then add four and repeat.
2. Compare pg_relation_size and pg_total_relation_size for a table of yours.
3. Add an index to a frequently-updated column and measure the update throughput before and after.
4. List your top twenty queries by total_exec_time and EXPLAIN the top three.
5. Replace two single-column indexes with one composite and compare the plans. Look for BitmapAnd in the first.
6. Create a text_pattern_ops index, a DESC index and a partial index alongside their plain equivalents. Run the redundancy query and confirm it flags none of them.
7. Create a genuinely duplicated index and confirm the query reports the pair twice.
8. Run the unused-index query. Check stats_reset before trusting it.
9. Check each candidate for indisunique and indisprimary before considering a drop.
10. Drop an index inside a transaction, check a plan, and ROLLBACK.
11. Set indisvalid = false on an index and confirm the planner stops using it. Set it back.
12. Install pgstattuple and read avg_leaf_density for your largest index.
13. Create bloat deliberately: insert 100,000 rows, delete 90% of them, and measure the index size before and after REINDEX.
14. Index a column on a 500-row table and check whether the planner ever uses it.
15. Index a boolean column where 95% of rows are true. Confirm it is never used for true. Then build a partial index on false.
16. Pick one table in a real project, justify every index on it from a query plan, and drop the ones you cannot.

Official documentation

• PostgreSQL — Examining index usage — The official version of this lesson's advice.
• PostgreSQL — The statistics collector views — pg_stat_user_indexes, pg_stat_user_tables and what each counter means.
• PostgreSQL — REINDEX — Including the CONCURRENTLY caveats.
• PostgreSQL — pgstattuple — Measuring bloat properly.
• PostgreSQL — Heap-only tuples — Why indexing a hot column costs more than it appears.

Next: the performance bug that is not the database's fault.