public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Mullineux <[email protected]>
To: Ramakrishna m <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: [email protected]
Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Date: Wed, 6 Nov 2024 22:11:00 +0000
Message-ID: <CAGsyd8WqPEgoAkNO0Q7rpQpOWOZ-Z6wCM7xh5d6nXCxLH_GM_A@mail.gmail.com> (raw)
In-Reply-To: <CAG-eXHJ+KbQ8_k-jKSGZU9V6HkLKU2Jqz7nYMYGhHuC-Zqm7qQ@mail.gmail.com>
References: <CAG-eXHJ+KbQ8_k-jKSGZU9V6HkLKU2Jqz7nYMYGhHuC-Zqm7qQ@mail.gmail.com>
It might be worth eliminating the use of cached plans here. Is your app
using prepared statements at all?
Point is that if the optimizer sees the same prepared query , 5 times, the
it locks the plan that it found at that time. This is a good trade off as
it avoids costly planning-time for repetitive queries. But if you are
manually querying, the a custom plan will be generated anew.
A quick analyze of the table should reset the stats and invalidate any
cached plans.
This may not be your problem just worth eliminating it from the list of
potential causes.
On Wed, 6 Nov 2024, 17:14 Ramakrishna m, <[email protected]> wrote:
> Hi Team,
>
> One of the queries, which retrieves a single record from a table with 16
> hash partitions, is taking more than 10 seconds to execute. In contrast,
> when we run the same query manually, it completes within milliseconds. This
> issue is causing exhaustion of the application pools. Do we have any bugs
> in postgrs16 hash partitions? Please find the attached log, table, and
> execution plan.
>
> size of the each partitions : 300GB
> Index Size : 12GB
>
> Postgres Version : 16.x
> Shared Buffers : 75 GB
> Effective_cache : 175 GB
> Work _mem : 4MB
> Max_connections : 3000
>
> OS : Ubuntu 22.04
> Ram : 384 GB
> CPU : 64
>
> Please let us know if you need any further information or if there are
> additional details required.
>
>
> Regards,
> Ram.
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
In-Reply-To: <CAGsyd8WqPEgoAkNO0Q7rpQpOWOZ-Z6wCM7xh5d6nXCxLH_GM_A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox