Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Check index usage statistics
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan        AS times_used,
  idx_tup_read    AS tuples_read,
  idx_tup_fetch   AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Find unused indexes (candidates for removal)
SELECT indexname, tablename
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND tablename NOT IN (SELECT tablename FROM pg_tables WHERE tableowner = 'postgres');

-- Bloat check — indexes much larger than needed
SELECT
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild an index (locks writes briefly)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Manual VACUUM and ANALYZE
VACUUM ANALYZE orders;

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;