Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-06-20 Thread David Rowley
On 20 June 2017 at 07:49, Tom Lane wrote: > I'm not totally satisfied that there isn't any case where the smallest > selectivity hack is appropriate. In the example you're showing here, > the FK columns are independent so that we get more or less the right > answer with or

Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-06-19 Thread Tom Lane
David Rowley writes: > On 22 May 2017 at 16:10, David Rowley wrote: >> I also just noticed that I don't think I've got ANTI join cases >> correct in the patch I sent. I'll look at that now. > I've attached an updated patch. > This one

Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-05-23 Thread David Rowley
On 22 May 2017 at 16:10, David Rowley wrote: > I also just noticed that I don't think I've got ANTI join cases > correct in the patch I sent. I'll look at that now. I've attached an updated patch. This one is much less invasive than my original attempt. There are

Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-05-21 Thread David Rowley
On 21 May 2017 at 07:56, Tom Lane wrote: > I'm entirely unconvinced by this patch --- it seems to simply be throwing > away a lot of logic. Notably it lobotomizes the FK code altogether for > semi/antijoin cases, but you've not shown any example that even involves > such

Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-05-20 Thread Tom Lane
David Rowley writes: > I've been analyzing a reported regression case between a 9.5 plan and > a 9.6 plan. I tracked this down to the foreign key join selectivity > code, specifically the use_smallest_selectivity code which is applied > to outer joins where the

Re: [HACKERS] Regression in join selectivity estimations when using foreign keys

2017-05-19 Thread David Rowley
On 18 May 2017 at 20:28, David Rowley wrote: > A vastly simplified example case is: > > create table fkest (a int, b int, c int unique, primary key(a,b)); > create table fkest1 (a int, b int, primary key(a,b)); > > insert into fkest select x/10,x%10, x from

[HACKERS] Regression in join selectivity estimations when using foreign keys

2017-05-18 Thread David Rowley
I've been analyzing a reported regression case between a 9.5 plan and a 9.6 plan. I tracked this down to the foreign key join selectivity code, specifically the use_smallest_selectivity code which is applied to outer joins where the referenced table is on the outer side of the join. A vastly