public inbox for [email protected]
help / color / mirror / Atom feedFrom: ravi k <[email protected]>
To: David Mullineux <[email protected]>
Cc: Ramakrishna m <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Date: Sat, 9 Nov 2024 17:32:54 +0530
Message-ID: <CAFL4M8HvFUz56ezX-C2QGUONEafwObe3LWeu+MWUby=i8Teh4A@mail.gmail.com> (raw)
In-Reply-To: <CAGsyd8VAdS+aByHkT=mi5sAgVumQgG4kBiY3kRw49NWWTtYGWA@mail.gmail.com>
References: <CAG-eXHJ+KbQ8_k-jKSGZU9V6HkLKU2Jqz7nYMYGhHuC-Zqm7qQ@mail.gmail.com>
<CAGsyd8WqPEgoAkNO0Q7rpQpOWOZ-Z6wCM7xh5d6nXCxLH_GM_A@mail.gmail.com>
<CAFL4M8EmboE4wXBHe2EMFcShxUAxXgWFa4TT-iVD2hJcHumetg@mail.gmail.com>
<CAGsyd8X7U07UK8hjapwYBfbtK0KnMSxLtH6BFaxe1_i2=BR-+A@mail.gmail.com>
<CAFL4M8FuS1ivNARaNUjoSgdjec+KH0DLXSqVa11uy1Nscup2+w@mail.gmail.com>
<CAGsyd8VAdS+aByHkT=mi5sAgVumQgG4kBiY3kRw49NWWTtYGWA@mail.gmail.com>
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 protected]> wrote:
> 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 protected]> wrote:
>
>> 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.
>>>>>>
>>>>>
view thread (2+ messages)
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: <CAFL4M8HvFUz56ezX-C2QGUONEafwObe3LWeu+MWUby=i8Teh4A@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