Re: Missing indexes

[Bart Jonk <bart.jonk@xxxxxxx>]

Just wanted to add a thank you for Laurenz for his query. 

It works on 15.5 as well and pointed me in the right direction solving a high I/O issue we perceived. 

Cheers,
Bart

On Mon, 4 Mar 2024 at 08:02, Harish Harish <hpt3009@xxxxxxxxx> wrote:

Dear Admin,

Thank you very much everyone for your valuable suggestions .

I will try them.

Appreciate your help and support.

Best regards,

Hari

On Sat, Mar 2, 2024 at 3:54 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:

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