SQL: an index makes reads faster and every write slower

Each index is a second structure the database must keep current. An INSERT into a table with six indexes is seven writes, so indexing every column is how a write-heavy table stalls.

Code

-- Find the ones nobody uses (PostgreSQL):
SELECT relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

-- And the duplicates: (a) is redundant when (a, b) exists
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

Output

INSERT throughput on one table:
  1 index   -> 42,000 rows/s
  6 indexes ->  9,800 rows/s

-- An index on (tenant_id) is redundant if (tenant_id, created_at) exists:
-- the composite serves every query the single-column one would.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25