pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMissing indexes
7+ messages / 6 participants
[nested] [flat]
* Missing indexes
@ 2024-03-01 16:37 Harish Harish <hpt3009@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Harish Harish @ 2024-03-01 16:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Missing indexes
@ 2024-03-01 17:40 Jean-Paul POZZI <jp.pozzi@izzop.net>
parent: Harish Harish <hpt3009@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Jean-Paul POZZI @ 2024-03-01 17:40 UTC (permalink / raw)
To: Harish Harish <hpt3009@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Missing indexes
@ 2024-03-01 17:42 Alex Balashov <abalashov@evaristesys.com>
parent: Jean-Paul POZZI <jp.pozzi@izzop.net>
0 siblings, 0 replies; 7+ messages in thread
From: Alex Balashov @ 2024-03-01 17:42 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Missing indexes
@ 2024-03-01 17:48 Erik Wienhold <ewie@ewie.name>
parent: Harish Harish <hpt3009@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Erik Wienhold @ 2024-03-01 17:48 UTC (permalink / raw)
To: Harish Harish <hpt3009@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Missing indexes
@ 2024-03-02 10:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Harish Harish <hpt3009@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-03-02 10:24 UTC (permalink / raw)
To: Harish Harish <hpt3009@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Missing indexes
@ 2024-03-04 07:01 Harish Harish <hpt3009@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Harish Harish @ 2024-03-04 07:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Missing indexes
@ 2024-03-06 08:14 Bart Jonk <bart.jonk@voys.nl>
parent: Harish Harish <hpt3009@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bart Jonk @ 2024-03-06 08:14 UTC (permalink / raw)
To: Harish Harish <hpt3009@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-03-06 08:14 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-01 16:37 Missing indexes Harish Harish <hpt3009@gmail.com>
2024-03-01 17:40 ` Jean-Paul POZZI <jp.pozzi@izzop.net>
2024-03-01 17:42 ` Alex Balashov <abalashov@evaristesys.com>
2024-03-01 17:48 ` Erik Wienhold <ewie@ewie.name>
2024-03-02 10:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-04 07:01 ` Harish Harish <hpt3009@gmail.com>
2024-03-06 08:14 ` Bart Jonk <bart.jonk@voys.nl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox