Why OR-clauses not getting copied into baserestrictinfo of another table whose columns are in the same EquivalenceClass?
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.
What could be the reasons for NOT copying the t1's filters into t2's
baserestrictinfo? If we copy that, could that result in wrong results?
P.S. PlanTree for some sample queries is attached for reference.
Thanks,
Mohamed Insaf K
Attachments:
On Wed, 3 Mar 2021 at 23:26, Mohamed Insaf <insafmpm@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]/messages/by-id/30810.1449335261@sss.pgh.pa.us 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]/messages/by-id/CA+TgmoZL6KaVGWCgwCziXiCMr3tNvf1hhrHDjjYAF5CRss2ksg@mail.gmail.com 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]: /messages/by-id/30810.1449335261@sss.pgh.pa.us
[2]: /messages/by-id/CA+TgmoZL6KaVGWCgwCziXiCMr3tNvf1hhrHDjjYAF5CRss2ksg@mail.gmail.com
On Wed, Mar 3, 2021 at 3:56 PM Mohamed Insaf <insafmpm@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