Postgres Pro
Downloads
Home   >   mailing lists

Thread: Missing indexes

Missing indexes

From
Harish Harish
Date:
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

RE: Missing indexes

From
Jean-Paul POZZI
Date:
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

Re: Missing indexes

From
Alex Balashov
Date:
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

Re: Missing indexes

From
Erik Wienhold
Date:
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

Re: Missing indexes

From
Laurenz Albe
Date:
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

Re: Missing indexes

From
Harish Harish
Date:
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@cybertec.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

Re: Missing indexes

From
Bart Jonk
Date:
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@gmail.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.albe@cybertec.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