On Wed, 3 Mar 2021 at 23:26, Mohamed Insaf <insaf...@gmail.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
