/postgres

Index Optimization

Identify Unused Indexes

Query to find unused indexes:

-- indexes with 0 scans (check pg_stat_reset / pg_postmaster_start_time first)
SELECT
   s.schemaname,
   s.relname AS table_name,
   s.indexrelname AS index_name,
   pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
 FROM pg_catalog.pg_stat_user_indexes s
 JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
 WHERE s.idx_scan = 0
   AND 0 <> ALL (i.indkey)       -- exclude expression indexes
   AND NOT i.indisunique          -- exclude UNIQUE indexes
   AND NOT EXISTS (               -- exclude constraint-backing indexes
     SELECT 1 FROM pg_catalog.pg_constraint c
     WHERE c.conindid = s.indexrelid
   )
 ORDER BY pg_relation_size(s.indexrelid) DESC;

Identify Duplicate Indexes

Indexes with identical definitions (after normalizing names) on the same table are duplicates:

SELECT
  schemaname || '.' || tablename AS table,
  array_agg(indexname) AS duplicate_indexes,
  pg_size_pretty(sum(pg_relation_size((schemaname || '.' || indexname)::regclass))) AS total_size
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname, tablename,
  regexp_replace(indexdef, 'INDEX \S+ ON ', 'INDEX ON ')
HAVING count(*) > 1;

Warning: Confirm with a human before dropping duplicate indexes. Some "duplicates" differ in practical use (operator classes, collations, predicates, sort order) and may be required for critical workloads.

Identify Invalid Indexes

Failed CREATE INDEX CONCURRENTLY builds leave INVALID indexes maintained on every write but never used for reads. CREATE INDEX CONCURRENTLY IF NOT EXISTS silently succeeds if an invalid index already exists — always check and drop before retrying.

SELECT indexrelname FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid WHERE NOT i.indisvalid;

Warning: Confirm with a human before dropping invalid indexes. Validate index health and workload impact first, then drop/rebuild during a controlled window.

Per-table Index Count Guidelines

SELECT relname AS table, count(*) as index_count
FROM pg_stat_user_indexes
GROUP BY relname
ORDER BY count(*) DESC;

Index Bloat Detection

VACUUM removes dead tuples but does not reclaim empty index page space — only REINDEX or pg_repack compacts pages. Detect with pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT avg_leaf_density FROM pgstatindex('my_index');

Below 70% = significant bloat, healthy = 80-90%+. Remediation: REINDEX CONCURRENTLY (PG 12+) for index-only bloat; pg_repack for table+index (requires PK and ~2x disk space).

HOT Update Monitoring

HOT updates skip all index maintenance when no indexed column value changes and free space exists on the same heap page. Target >90% on frequently updated tables.

SELECT relname, round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY n_tup_upd DESC;

Key levers: set fillfactor = 70-80 on write-heavy tables, never index frequently-updated columns (status, updated_at) unless query-critical, use partial indexes to reduce scope. PG 16+: BRIN indexes excluded from HOT eligibility checks.

Write Amplification

Each additional index adds write-path overhead because every INSERT/UPDATE/DELETE must maintain more index entries. In a Percona PG 17.4 over-indexing benchmark, moving from 7 to 39 indexes showed a 58% throughput drop.

To reduce WAL volume from this extra write activity, enable wal_compression (available before PG 15; lz4 and zstd options are PG 15+). Tune max_wal_size separately to reduce checkpoint frequency under sustained write load.

Planner Tuning

• SSD storage: random_page_cost = 1.1 (default 4.0 assumes spinning disk)
• effective_cache_size: ~75% of total RAM
• Correlated columns: CREATE STATISTICS (dependencies, ndistinct, mcv) then ANALYZE
• Skewed distributions: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500-1000