On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane <kkproghub@gmail.com> wrote:
Hello.
I have a straight forward question, but I am just trying to analyze the specifics.
So I have a set of queries depending on each other in a sequence to compute some results for generating financial report.
It involves summing up some amounts from tuns or of rows and also on certain conditions it categorizes the amounts into types (aka Debit Balance, Credit balance etc).
There are at least 6 queries in this sequence and apart from 4 input parameters. these queries never change.
So will I get any performance benefit by having them in a stored procedure rather than sending the queries from my Python based API?
One problem is that the query planner reverts to a generic query plan if you execute the same query over and over in a loop in the SP.
That bit us once. A big SP that had been running "normally" for months suddenly went from about 20 minutes to six hours. The solution (given by someone on this list a couple of years ago) was to add "set plan_cache_mode = force_custom_plan;" above the call.
That way, the query plan was updated every time. Performance dropped to about 8 minutes IIRC.
