Обсуждение: Missing indexes

Well, you've come to the right place. This list loves "feelings-based" diagnoses. :)

Two suggestions:

1) Turn on your "slow query log":

ALTER SYSTEM SET log_min_duration_statement = 50;
SELECT pg_reload_conf();

Then look at the Postgres log and see which statements may be running slowly.

2) Have a look at `SELECT * FROM pg_stat_activity` and get your bearings.

-- Alex

> On 1 Mar 2024, at 12:40, Jean-Paul POZZI <jp.pozzi@izzop.net> wrote:
>
> Hello,
>   Some examples of the "bad" sql text should be interesting ...
>  Regards
>
> De: "Harish Harish" <hpt3009@gmail.com>
> À: "undefined" <pgsql-admin@lists.postgresql.org>
> Envoyé: vendredi 1 mars 2024 17:39
> Objet: Missing indexes
>
> Dear Admins,
>   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.
>   Your timely help and support is much appreciated.
>   Best Regards,
> Hari

--
Alex Balashov
Principal Consultant
Evariste Systems LLC
Web: https://evaristesys.com
Tel: +1-706-510-6800

On 2024-03-01 17:37 +0100, Harish Harish wrote:
> I need help. We are on Postgres 10.

General advice: Upgrade to a supported version because support for pg10
ended in November 2022.

> and have a feeling there are some indexes missing wich is causing
> performance issue

Are the table statistics up to date?  Run ANALYZE on the relevant tables
to be sure.

> Please help me find the missing index within  PostgreSQl 10.

You need to provide the queries in question and their execution plans[1]
with EXPLAIN (ANALYZE, BUFFERS).  Table definitions (including indexes)
are also helpful.

Also good to know: Is it a performance regression caused by recent
changes to those queries?  That may already give you hints on possibly
missing indexes.

[1] https://www.postgresql.org/docs/10/sql-explain.html

-- 
Erik

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