Sorry, it was typo. Bind variable is bigint only. Thanks
On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, <dmu...@gmail.com> 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, <ravisq...@gmail.com> 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, <dmu...@gmail.com> 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, <ram.p...@gmail.com> 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. >>>> >>>
