Managing Indexes

REINDEX TABLE orders;
REINDEX TABLE CONCURRENTLY orders;

REINDEX INDEX orders_cust_idx;
REINDEX INDEX CONCURRENTLY orders_cust_idx;

REINDEX SCHEMA public;

REINDEX DATABASE shop;            -- requires being connected to shop
REINDEX SYSTEM shop;              -- system catalogs only (single-user)

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
  FROM pg_stat_user_indexes
 ORDER BY idx_scan DESC LIMIT 20;

SELECT s.schemaname, s.relname, s.indexrelname,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
 ORDER BY pg_relation_size(s.indexrelid) DESC;

SELECT array_agg(indexname) AS dups, array_agg(indexdef)
  FROM pg_indexes
 GROUP BY tablename, regexp_replace(indexdef, ' INDEX [^ ]+', '')
HAVING count(*) > 1;

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('orders_cust_idx');
-- or use pg_stat_user_indexes + pg_class for size deltas