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 cha
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
Sorry, it was typo. Bind variable is bigint only.
Thanks
On Fri, 8 Nov, 2024, 7:09 pm David Mullineux, 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
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
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 wi
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 repetitiv
On Wed, 2024-11-06 at 22:43 +0530, Ramakrishna m wrote:
> 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.
If I read you
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 an
