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

On Sun, Jan 24, 2021 at 6:34 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> Hi:
>
> 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
>
> Do the real job.
>
> Thought?
>
>
>
> --
> Best Regards
> Andy Fan (https://www.aliyun.com/)
>

Some results from this patch.

create table p (a int, b int, c character varying(8)) partition by list(c);
create table p1 partition of p for values in ('000001');
create table p2 partition of p for values in ('000002');
create table p3 partition of p for values in ('000003');
create table q (a int, c character varying(8), b int) partition by list(c);
create table q1 partition of q for values in ('000001');
create table q2 partition of q for values in ('000002');
create table q3 partition of q for values in ('000003');

Before the patch:
postgres=# explain (costs off) select * from p inner join q on p.c = q.c
and q.c > '000002';
QUERY PLAN
----------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Append
-> Seq Scan on p1 p_1
-> Seq Scan on p2 p_2
-> Seq Scan on p3 p_3
-> Hash
-> Seq Scan on q3 q
Filter: ((c)::text > '000002'::text)
(9 rows)

After the patch:

QUERY PLAN
----------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Seq Scan on p3 p
-> Hash
-> Seq Scan on q3 q
Filter: ((c)::text > '000002'::text)
(6 rows)

Before the patch:
postgres=# explain (costs off) select * from p inner join q on p.c = q.c
and (q.c = '000002' or q.c = '000001');
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Append
-> Seq Scan on p1 p_1
-> Seq Scan on p2 p_2
-> Seq Scan on p3 p_3
-> Hash
-> Append
-> Seq Scan on q1 q_1
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
-> Seq Scan on q2 q_2
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
(12 rows)

After the patch:
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Append
-> Seq Scan on p1 p_1
-> Seq Scan on p2 p_2
-> Hash
-> Append
-> Seq Scan on q1 q_1
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
-> Seq Scan on q2 q_2
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
(11 rows)

Before the patch:
postgres=# explain (costs off) select * from p left join q on p.c = q.c
where (q.c = '000002' or q.c = '000001');
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Append
-> Seq Scan on p1 p_1
-> Seq Scan on p2 p_2
-> Seq Scan on p3 p_3
-> Hash
-> Append
-> Seq Scan on q1 q_1
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
-> Seq Scan on q2 q_2
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
(12 rows)

After the patch:
QUERY PLAN
--------------------------------------------------------------------------------------------
Hash Join
Hash Cond: ((p.c)::text = (q.c)::text)
-> Append
-> Seq Scan on p1 p_1
-> Seq Scan on p2 p_2
-> Hash
-> Append
-> Seq Scan on q1 q_1
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
-> Seq Scan on q2 q_2
Filter: (((c)::text = '000002'::text) OR ((c)::text =
'000001'::text))
(11 rows)

--
Best Regards
Andy Fan (https://www.aliyun.com/)