Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?

On Wed, May 19, 2021 at 8:15 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Mon, 17 May 2021 at 14:52, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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/)