Re: Why OR-clauses not getting copied into baserestrictinfo of another table whose columns are in the same EquivalenceClass?

On Wed, 3 Mar 2021 at 23:26, Mohamed Insaf <insafmpm(at)gmail(dot)com> wrote:
> I have a question regarding distributing the filter clause(baserestrictinfo) of one table into another table(Keys belong to the same EquivalenceClass).
>
> In the following query, why PG is not copying the filter (t1.pk=1 OR t1.pk=2) into t2's baserestrictinfo? I believe PG copies those filters which are OpExpr and not BoolExpr, but still wanted to know what would be the risks if it gets copied.
>
> SELECT * FROM
> t1 INNER JOIN t2 ON (t1.pk = t2.pk)
> WHERE t1.pk = 1 OR t1.pk = 2;
>
> The filters are effectively: (t1.pk = t2.pk) AND (t1.pk = 1 OR t1.pk = 2). Can we expand this into (t1.pk = t2.pk) AND (t1.pk = 1 OR t1.pk = 2) AND (t2.pk = 1 OR t2.pk = 2)?

There's not really any reason we don't do this other than nobody has
implemented it yet. In 2015 I did propose [1] we do something a bit
smarter with range quals and push those into EquivalenceClasses too,
but there was some concern about duplication of other quals that might
already exist in the EquivalenceClass and additional evaluations of
redundant quals. I don't think there are any problems there we
couldn't code around.

IIRC there was also some concern about the effort required to find a
given Expr in an EquivalenceClass. That might be a little more
efficient to do now as we could pull_varnos from the Expr and only
look at each varno's RelOptInfo->eclass_indexes. However, we might
not have built the eclass_indexes by the time we need to do this.

Also, we'd still need to trawl through each EquivalenceMember which
would be slow for ECs with lots of members. It's not been touched in
a while, but in [2] there was some WIP with some infrastructure that
would help to speed up finding an Expr within an EquivalenceClass.

More recently (probably 2-3 years) Tom did mention about the
possibility of putting IN(const1, const2) type Exprs in
EquivalenceClass. That's pretty similar to your case. I can't find
the thread for that.

David

[1] https://www.postgresql.org/message-id/flat/30810.1449335261%40sss.pgh.pa.us#906319f5e212fc3a6a682f16da079f04
[2] https://www.postgresql.org/message-id/flat/CA%2BTgmoZL6KaVGWCgwCziXiCMr3tNvf1hhrHDjjYAF5CRss2ksg%40mail.gmail.com#6423828089e65655005ae8af526e93ab