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

Thanks Tom for joining.

> I'm not in favor of complicating the EquivalenceClass
> mechanism for this, because .... (b) what it definitely will do
> is make ECs harder to understand and reason about.

I'm not willing to show opposition on purpose, and I'm not insist on current
strategy, but I can't understand the comment here, not sure how others.
So I just point it out. IMO, the semantics of ec_filter is that every EMs
in this
EC can have this filter. I do like this method very much. If we need
something
to improve that, it may be the content in ec_filter is not generic
enough. For example:

select * from t1, t2 where t1.a = t2.a and t2.a > 3;

Then the EC filter is "t2.a > 3". Why is it a "t2.a" rather than a more
generic type to show "any EM" in this EC, I can double check the
patch to see if this can be any helpful.

Maybe I'm focusing on the current situation too much, could you describe
more about the badness of this semantics level?

> If we develop a
> separate mechanism that can infer things from inequalities, and it

_only_

kicks in when there are some inequalities, that might work out okay.
>

I will try to make this part clearer. The current mechanism includes 3
steps.
1). Gather the inequalities_qual_list during the deconstruct_jointree. 2).
After the root->eq_classes is built, scan each of the above quals to find
out if there is an EC match, if yes, add it to the EC. There are some
fast paths here.
3). compose the qual in ec_filter and members in ec_members, then
distribute it to the relations.

Step 1 would make sure only inequalities is checked. Are you unhappy with
the
cost of step 2 here? for the case like

SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t1.b > 3;

we have to go step 2 and get nothing finally. As for the case like "FROM
t1, t2, t3
WHERE t1.a = t2.a and t3.c > 3". t3.c > 3 can be discard quickly with
EC->relids checking.

But because of that, I don't even like the 0001 patch in this series.
> I've not looked at the subsequent ones.
>
>
I agree with 0001 patch should be the first one to reach an agreement .

--
Best Regards
Andy Fan