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