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.
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,
