On Fri, 2024-03-01 at 22:07 +0530, Harish Harish wrote: > I need help. We are on Postgres 10. and have a feeling there are some indexes missing wich is causing performance issue > > Please help me find the missing index within PostgreSQl 10.
Three pointers:
1. upgrade to v16
2. activate pg_stat_statements and look for the statements that consume most time:
SELECT total_exec_time, calls, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
3. Look for frequent large sequential scans
SELECT relid::regclass AS table_name, seq_scan AS sequential_scans, seq_tup_read / seq_scan AS scan_size FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY least(seq_scan, seq_tup_read / seq_scan) DESC LIMIT 10;