Re: Missing indexes

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(at)gmail(dot)com> 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(dot)albe(at)cybertec(dot)at>
> 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
>>
>