Section 3 of 3 · 7 minutes

Measure benefit and cost

Your goal

What you will be able to do

Verify an index changes the target plan and record its storage and write cost.

Why this matters at work

The practical reason

An index is justified only when its workload benefit outweighs the ongoing cost.

Learn

The idea in plain English

Compare the target query plan before and after the index. Use representative parameter values and data volume. A small training table may prefer a table scan even when the index is correct.

Every INSERT, DELETE, and relevant UPDATE must maintain the index. Record the index size, expected write rate, and whether another index already covers the workload.

Remember these points

Validate with a representative query plan.
Measure index size and write overhead.
Remove redundant indexes only after checking other workloads and constraints.

See it in SQL

Measure index size and usage

Combine storage size with observed scan counts.

SELECT
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'training_orders'
ORDER BY pg_relation_size(indexrelid) DESC;

`idx_scan` counts uses since the statistics reset; it is not a lifetime count. Record the reset window before judging an index unused.

Large size and low scan count are reasons to investigate, not proof that deletion is safe.

What you should see

One row per training_orders index with scan count and current disk size.

Useful words

Important terms

Redundant index: An index whose useful workload is already covered by another index.

Quick check · Not graded

Check your understanding

What evidence is needed before keeping a new index?

Choose one answerOnly confirmation that CREATE INDEX succeeded.Only the index name and owner.A better representative plan plus an acceptable storage and write cost.

Your progress is saved to your signed-in account.