On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane <kkprog...@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.
