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