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

On Wed, Mar 3, 2021 at 3:56 PM Mohamed Insaf <insafmpm(at)gmail(dot)com> wrote:
>
> Hello hackers,
>
> 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)?
>
> The above query is resulting in a Query Plan like:
> [Scan(t1, with filter pk = 1 OR pk = 2)] Join [Scan(t2, with Parameter t1.pk = t2.pk)]
>
> If PG copies t1's filter into t2, it could've been like this:
> [Scan(t1, with filter pk = 1 OR pk = 2)] Join [Scan(t2, with *filter pk = 1 OR pk = 2*)]
>
> With Postgres Table Partition, this results in more performance issues. Unneeded partitions need to be scanned, since the filters are not getting copied.
>
>
> Actually, in my case, both t1 and t2 are HASH partitioned with the key (pk), and with the same number of partitions and range.
> And running the same query results in reading only 2 partitions of t1, and all of the partitions of t2.
> If we could copy the filter into t2 as well, then only 2 partitions of t2 would be required to be read.

If you have these tables partitioned similarly, partition-wise join
should take care of eliminating the partitions in t2. Partition
pruning will prune the partitions in t1. Partition-wise join will
create joins between unpruned partitions of t1 with matching
partitions of t2. Final plan will not have scans on partitions of t2
which do not match unpruned partitions of t1, effectively pruning t2
as well. You will need to set enable_partitionwise_join = true for
that.
--
Best Wishes,
Ashutosh Bapat