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

On Fri, Feb 19, 2021 at 6:03 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Mon, Feb 8, 2021 at 3:43 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
>>
>>
>> On Mon, Jan 25, 2021 at 10:21 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> 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/)
>>>
>>
>>
>> Here is a performance test regarding this patch. In the following simple
>> case,
>> we can get 3x faster than before.
>>
>> create table p (a int, b int, c int) partition by list(c);
>> select 'create table p_'||i||' partition of p for values in (' || i ||
>> ');' from generate_series(1, 100)i; \gexec
>> insert into p select i, i, i from generate_series(1, 100)i;
>> create table m as select * from p;
>> analyze m;
>> analyze p;
>>
>> test sql: select * from m, p where m.c = p.c and m.c in (3, 10);
>>
>> With this patch: 1.1ms
>> Without this patch: 3.4ms
>>
>> I'm happy with the result and the implementation, I have add this into
>> commitfest https://commitfest.postgresql.org/32/2975/
>>
>> Thanks.
>>
>> --
>> Best Regards
>> Andy Fan (https://www.aliyun.com/)
>>
>
> Rebase to the current latest commit 678d0e239b.
>
>
Rebase to the latest commit ea1268f630 .

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