Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
On Fri, Feb 15, 2019 at 12:24:18PM -0500, Benedict Holland wrote: > That sounds like a perfect task for a view if the joins are all the same. But note that either the view itself needs to have both where clauses (with hardcoded dates?), or otherwise the view needs to be on only one table, and the toplevel query needs to have where clause on each view, or else one of the tables won't get constraint exclusion. On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar wrote: > > yes i accept , but when i will do for existing tables, i am facing issue. > > > > I have created 100 Function , all the function having five table join(*now > > all partition by date*) , now its not possible to change where condition > > in all 100 Function. > > > > so that i am trying any other possibilities are there. > > From: Justin Pryzby mailto:[email protected]>> > > Sent: 14 February 2019 16:10:01 > > To: suganthi Sekar > > Cc: [email protected] > [email protected]> > > Subject: Re: constraint exclusion with ineq condition (Re: server hardware > > tuning.) > > > > On Thu, Feb 14, 2019 at 10:38:36AM +, suganthi Sekar wrote: > > > u mean the below parameter need to set on . its already on only. > > > alter system set constraint_exclusion to 'on'; > > > > No, I said: > > > You can work around it by specifying the same condition on > > b.call_created_date: > > > > AND b.call_created_date >='2017-11-01' AND > > b.call_created_date<'2017-11-30' -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581
Re: Performance regressions found using sqlfuzz
Andres Freund writes: > On 2019-02-14 17:27:40 +, Jung, Jinho wrote: >> - Our analysis: We believe that this regression has to do with two factors: >> 1) conditional expression (e.g., LEAST or NULLIF) are not reduced to >> constants unlike string functions (e.g., CHAR_LENGTH) 2) change in the cost >> estimation function for bitmap scan. Execution time grows by 3 orders of >> magnitude. We note that this regression is only observed on large databases >> (e.g., scale factor of 50). > Hm. The primary problem here is that the estimation both before and > after are really bad. So I don't think the commit you point out here is > really to blame. I'm not that bothered by the query not being great, > given the weird construction with LEAST(), but we probably could fix > that pretty easily. We already did: Author: Tom Lane Branch: master [6f19a8c41] 2018-12-30 13:42:04 -0500 Teach eval_const_expressions to constant-fold LEAST/GREATEST expressions. Doing this requires an assumption that the invoked btree comparison function is immutable. We could check that explicitly, but in other places such as contain_mutable_functions we just assume that it's true, so we may as well do likewise here. (If the comparison function's behavior isn't immutable, the sort order in indexes built with it would be unstable, so it seems certainly wrong for it not to be so.) Vik Fearing Discussion: https://postgr.es/m/[email protected] BTW, const-folding NULLIF would not be a similarly tiny fix, because it would need to check for immutability of the underlying operator (since it is possibly a cross-type comparison, we can't get away with just assuming it's immutable). I'm not convinced that case is worth carrying extra code for. regards, tom lane
