Re: Missing indexes

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;

Yours,
Laurenz Albe