Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16

From: David Mullineux <[email-masked]>
Date: Sat, 9 Nov 2024 10:10:39 +0000

Thanks for correction. At this point I would be trying to modify
plan_cache_mode
for the session which uses the bond variable. alter it so that
plan_cache_mode=force_custom_plan
One hypothesis is that, a bad plan got cached for that SQL pattern.
Obviously, when you run it *manually* you are always getting a *custom*
plan as it's not a prepared statement.

On Sat, 9 Nov 2024, 03:46 ravi k, <[email-masked]> wrote:

> Sorry, it was typo. Bind variable is bigint only.
>
> Thanks
>
> On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, <[email-masked]> 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-masked]> 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-masked]> 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-masked]> 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.
>>>>>
>>>>

---

From: ravi k <[email-masked]>
Date: Sat, 9 Nov 2024 17:32:54 +0530

Thanks for the advice!

I am planing to set session level!

but before that one more observations noticed i.e One more table has same
issue, which is having similar like hash partitions.

And I scheduled manual analyze for all parent hash tables(thus all stats
will update together).

After this change I didn't noticed the issue, not sure does this addressed
issue or not, just monitoring if this not works will set custom plan in
session level.

I have seen in SQL server  parameter sniffing regularly but in postgres I
never  experienced. I am still wondering does this sniffing or not as from
stats I didn't notice any sequence scan.

Best,

On Sat, 9 Nov, 2024, 3:40 pm David Mullineux, <[email-masked]> wrote:

>>>>> It might be worth eliminating the use of cached plans here. Is your
>>>>> app using prepared statements at all?
>>>>>> Please let us know if you need any further information or if there
>>>>>> are additional details required.
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Ram.
>>>>>>
>>>>>