On Mon, 17 May 2021 at 14:52, Andy Fan <zhihui.fan1213@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.
