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

From: Mohamed Insaf <insafmpm(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Why OR-clauses not getting copied into baserestrictinfo of another table whose columns are in the same EquivalenceClass?
Date: 2021-03-03 06:58:04
Message-ID: CAEzomOpNFjwPRd1kas++nvot=fLn+O-cPyPVzXtXC1Oz7ksbfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
pgPartitionedTable_simple.txt text/plain 4.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message k.jamison@fujitsu.com 2021-03-03 07:07:59 RE: PATCH: Batch/pipelining support for libpq
Previous Message Michael Paquier 2021-03-03 06:57:47 Re: buildfarm windows checks / tap tests on windows