Re: Extend more usecase for planning time partition pruning and init partition pruning.

Hi Andy,

On Sun, Jan 24, 2021 at 7:34 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> I recently found a use case like this. SELECT * FROM p, q WHERE p.partkey =
> q.colx AND (q.colx = $1 OR q.colx = $2); Then we can't do either planning time
> partition prune or init partition prune. Even though we have run-time
> partition pruning work at last, it is too late in some cases since we have
> to init all the plan nodes in advance. In my case, there are 10+
> partitioned relation in one query and the execution time is short, so the
> init plan a lot of plan nodes cares a lot.
>
> The attached patches fix this issue. It just get the "p.partkey = q.colx"
> case in root->eq_classes or rel->joinlist (outer join), and then check if there
> is some baserestrictinfo in another relation which can be used for partition
> pruning. To make the things easier, both partkey and colx must be Var
> expression in implementation.
>
> - v1-0001-Make-some-static-functions-as-extern-and-extend-C.patch
>
> Just some existing refactoring and extending ChangeVarNodes to be able
> to change var->attno.
>
> - v1-0002-Build-some-implied-pruning-quals-to-extend-the-us.patch

IIUC, your proposal is to transpose the "q.b in (1, 2)" in the
following query as "p.a in (1, 2)" and pass it down as a pruning qual
for p:

select * from p, q where p.a = q.b and q.b in (1, 2);

or "(q.b = 1 or q.b = 2)" in the following query as "(p.a = 1 or p.a = 2)":

select * from p, q where p.a = q.b and (q.b = 1 or q.b = 2);

While that transposition sounds *roughly* valid, I have some questions
about the approach:

* If the transposed quals are assumed valid to use for partition
pruning, could they also not be used by, say, the surviving
partitions' index scan paths? So, perhaps, it doesn't seem right that
partprune.c builds the clauses on-the-fly for pruning and dump them
once done.

* On that last part, I wonder if partprune.c isn't the wrong place to
determine that "q.b in (1, 2)" and "p.a in (1, 2)" are in fact
equivalent. That sort of thing is normally done in the phase of
planning when distribute_qual_to_rels() runs and any equivalences
found stored in PlannerInfo.eq_classes. Have you investigated why the
process_ machinery doesn't support working with ScalarArrayOpExpr and
BoolExpr to begin with?

* Or maybe have you considered generalizing what
build_implied_pruning_quals() does so that other places like
indxpath.c can use the facility?

--
Amit Langote
EDB: http://www.enterprisedb.com