Query performance !
> > Hi Experts, > > The attached query is performing slow, this needs to be optimized to > improve the performance. > > Could you help me with query rewrite (or) on new indexes to be created to > improve the performance? > > Thanks a ton in advance for your support. >
Re: Query performance !
On Tue, Jul 27, 2021 at 10:44:03PM +0530, kenny a wrote: > Hi Experts, > > The attached query is performing slow, this needs to be optimized to > improve the performance. > > Could you help me with query rewrite (or) on new indexes to be created to > improve the performance? > > Thanks a ton in advance for your support. Uh, there is no query, and I think you should read this: https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Performance of lateral join
On Tue, Jul 27, 2021 at 09:08:49AM +, Simen Andreas Andreassen Lønsethagen wrote: > >Easy first question: is the temp table analyzed before being used in a > > join ? > > No, I haven't done that. Today, I tried to run > > ANALYZE records_to_filter_on; > > on the same sample data set (3.75 million rows) before the join, and it did > not seem to make much of a difference in terms of time (new output of EXPLAIN > ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to > me). If the "shape" of the plan didn't change, then ANALYZE had no effect. I think you'd see an improvement if both tables were ordered by foedselsnummer. It might be that that's already somewhat/partially true (?) I suggest to create an index on the temp table's r.foedselsnummer, CLUSTER on that index, and then ANALYZE the table. The index won't be useful for this query, it's just for clustering (unless you can instead populate the temp table in order). Check if there's already high correlation of dpd_bis_foedselsnummer (over 0.9): | SELECT tablename, attname, inherited, null_frac, n_distinct, correlation FROM pg_stats WHERE attname='dpd_bis_foedselsnummer' AND tablename='...'; If not, consider clustering on the existing "unique_descending" index and then analyzing that table, too. This would also affect performance of other queries - hopefully improving several things at once. -- Justin
RE: Big performance slowdown from 11.2 to 13.3
Tom, One question that popped up in my head. hash_mem_multiplier is an upper-bound right: it doesn't reserve memory ahead of time correct? So there is no reason for me to spend undue amounts of time fine-tuning this parameter? If I have work_mem to 521MB, then I can set hash_mem_multiplier to 8 and should be OK. This doesn't mean that every query will consume 4GB of memory. Thank you, Laurent. -Original Message- From: Tom Lane Sent: Sunday, July 25, 2021 14:08 To: [email protected] Cc: Peter Geoghegan ; David Rowley ; Justin Pryzby ; [email protected] Subject: Re: Big performance slowdown from 11.2 to 13.3 "[email protected]" writes: > As a user of PG, we have taken pride in the last few years in tuning the heck > out of the system and getting great performance compared to alternatives like > SQLServer. The customers we work with typically have data centers and are > overwhelmingly Windows shops: we won the battle to deploy a complex > operational system on PG vs SQLServer, but Linux vs Windows was still a > bridge too far for many. I am surprised that this limitation introduced after > V11 hasn't caused issues elsewhere though. Maybe it has, but you're the first to report the problem, or at least the first to report it with enough detail to trace the cause. I've pushed a fix that removes the artificial restriction on work_mem times hash_mem_multiplier; it will be in next month's 13.4 release. You'll still need to increase hash_mem_multiplier to get satisfactory performance on your workload, but at least it'll be possible to do that. regards, tom lane
Re: Big performance slowdown from 11.2 to 13.3
On Tue, Jul 27, 2021 at 7:57 PM [email protected] < [email protected]> wrote: > hash_mem_multiplier is an upper-bound right: it doesn't reserve memory > ahead of time correct? > Yes, that is what the phrasing "maximum amount" in the docs is trying to convey. https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY But also note that it is "each operation" that gets access to that limit. David J.
Re: Big performance slowdown from 11.2 to 13.3
"[email protected]" writes: > One question that popped up in my head. hash_mem_multiplier is an upper-bound > right: it doesn't reserve memory ahead of time correct? So there is no reason > for me to spend undue amounts of time fine-tuning this parameter? If I have > work_mem to 521MB, then I can set hash_mem_multiplier to 8 and should be OK. > This doesn't mean that every query will consume 4GB of memory. Yeah, I wouldn't sweat over the specific value. The pre-v13 behavior was effectively equivalent to hash_mem_multiplier = infinity, so if you weren't having any OOM problems before, just crank it up. regards, tom lane
Re: Big performance slowdown from 11.2 to 13.3
I wrote: > Yeah, I wouldn't sweat over the specific value. The pre-v13 behavior > was effectively equivalent to hash_mem_multiplier = infinity, so if > you weren't having any OOM problems before, just crank it up. Oh, wait, scratch that: the old executor's behavior is accurately described by that statement, but the planner's is not. The planner will not pick a hashagg plan if it guesses that the hash table would exceed the configured limit (work_mem before, now work_mem times hash_mem_multiplier). So raising hash_mem_multiplier to the moon might bias the v13 planner to pick hashagg plans in cases where earlier versions would not have. This doesn't describe your immediate problem, but it might be a reason to not just set the value as high as you can. BTW, this also suggests that the planner is underestimating the amount of memory needed for the hashagg, both before and after. That might be something to investigate at some point. regards, tom lane
