Great tip!!! Thx
El mié, 10 de jul de 2024, 16:17, Ron Johnson <ronljohnsonjr@xxxxxxxxx> escribió:
On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane <kkproghub@xxxxxxxxx> 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.
