On Wed, May 19, 2021 at 8:15 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Mon, 17 May 2021 at 14:52, Andy Fan <zhihui.fan1...@gmail.com> wrote: > > Would marking the new added RestrictInfo.norm_selec > 1 be OK? > > There would be cases you'd want to not count the additional clauses in > the selectivity estimation and there would be cases you would want to. > > For example: > > SELECT ... FROM t1 INNER JOIN t2 ON t1.dt = t2.dt WHERE t1.dt BETWEEN > 'date1' AND 'date2'; > > If you derived that t2.dt is also BETWEEN 'date1' AND 'date2' then > you'd most likely want to include those quals for scans feeding merge, > hash and non-parameterized nested loop joins, so you'd also want to > count them in your selectivity estimations, else you'd feed junk > values into the join selectivity estimations. > > Yes, you are correct. > Parameterized nested loop joins might be different as if you were > looping up an index for t1.dt values on some index on t2.dt, then > you'd likely not want to bother also filtering out the between clause > values too. They're redundant in that case. > > I do not truly understand this. > I imagined we'd have some functions in equivclass.c that allows you to > choose if you wanted the additional filters or not. > Sounds like a good idea. > > Tom's example, WHERE a = b AND a IN (1,2,3), if a and b were in the > same relation then you'd likely never want to include the additional > quals. The only reason I could think that it would be a good idea is > if "b" had an index but "a" didn't. I've not checked the code, but > the index matching code might already allow that to work anyway. > > +1 for this feature overall. -- Best Regards Andy Fan (https://www.aliyun.com/)