public inbox for [email protected]
help / color / mirror / Atom feedRe: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
2+ messages / 2 participants
[nested] [flat]
* Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
@ 2024-11-08 13:39 David Mullineux <[email protected]>
2024-11-09 03:45 ` Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16 ravi k <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: David Mullineux @ 2024-11-08 13:39 UTC (permalink / raw)
To: ravi k <[email protected]>; +Cc: Ramakrishna m <[email protected]>; pgsql-general <[email protected]>
Just spotted a potential problem. The indexed column is a bigint. Are you,
in your prepared statement passing a string or a big int ?
I notice your plan is doing an implicit type conversion when you run it
manually.
Sometimes the wrong type will make it not use the index.
On Fri, 8 Nov 2024, 03:07 ravi k, <[email protected]> wrote:
> Hi ,
>
> Thanks for the suggestions.
>
> Two more observations:
>
> 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are
> accurate in postgres 16) if parameter sniffing happens the possibility of
> going to sequence scan is more right.
>
> 2) no blockings or IO issue during the time.
>
> 3) even with limit clause if touch all partitions also it could have been
> completed in milliseconds as this is just one record.
>
> 4) auto_explain in prod we cannot enable as this is expensive and with
> high TPS we may face latency issues and lower environment this issue cannot
> be reproduced,( this is happening out of Million one case)
>
> This looks puzzle to us, just in case anyone experianced pls share your
> experience.
>
> Regards,
> Ravi
>
> On Thu, 7 Nov, 2024, 3:41 am David Mullineux, <[email protected]> wrote:
>
>> 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.
>>>
>>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
2024-11-08 13:39 Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16 David Mullineux <[email protected]>
@ 2024-11-09 03:45 ` ravi k <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: ravi k @ 2024-11-09 03:45 UTC (permalink / raw)
To: David Mullineux <[email protected]>; +Cc: Ramakrishna m <[email protected]>; pgsql-general <[email protected]>
Sorry, it was typo. Bind variable is bigint only.
Thanks
On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, <[email protected]> wrote:
> Just spotted a potential problem. The indexed column is a bigint. Are you,
> in your prepared statement passing a string or a big int ?
> I notice your plan is doing an implicit type conversion when you run it
> manually.
> Sometimes the wrong type will make it not use the index.
>
> On Fri, 8 Nov 2024, 03:07 ravi k, <[email protected]> wrote:
>
>> Hi ,
>>
>> Thanks for the suggestions.
>>
>> Two more observations:
>>
>> 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are
>> accurate in postgres 16) if parameter sniffing happens the possibility of
>> going to sequence scan is more right.
>>
>> 2) no blockings or IO issue during the time.
>>
>> 3) even with limit clause if touch all partitions also it could have been
>> completed in milliseconds as this is just one record.
>>
>> 4) auto_explain in prod we cannot enable as this is expensive and with
>> high TPS we may face latency issues and lower environment this issue cannot
>> be reproduced,( this is happening out of Million one case)
>>
>> This looks puzzle to us, just in case anyone experianced pls share your
>> experience.
>>
>> Regards,
>> Ravi
>>
>> On Thu, 7 Nov, 2024, 3:41 am David Mullineux, <[email protected]> wrote:
>>
>>> 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.
>>>>
>>>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-09 03:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-08 13:39 Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16 David Mullineux <[email protected]>
2024-11-09 03:45 ` ravi k <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox