Re: [HACKERS] Runtime Partition Pruning

Hello,

On Fri, Dec 8, 2017 at 3:37 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 7 December 2017 at 23:56, Beena Emerson <memissemerson@gmail.com> wrote:
>> Currently Amit's v13 patches do not apply on the HEAD and I was
>> working on 487a0c1518af2f3ae2d05b7fd23d636d687f28f3 which is the last
>> commit where all Amit's v13 patches applies cleanly.
>
> Thanks.
>
> I was just looking over this and was wondering about the following case:
>
> drop table if exists p;
> create table p (a int not null, b int not null) partition by range (a);
> create table p1 partition of p for values from (0) to (1000);
> create table p2 partition of p for values from (1000) to (2000);
> create table p3 partition of p for values from (2000) to (3000);
> create table p4 partition of p for values from (3000) to (4000);
>
> create index on p1 (a);
> create index on p2 (a);
> create index on p3 (a);
> create index on p4 (a);
>
>
> insert into p select x,x from generate_series(1,3999) x;
>
> drop table if exists t;
> create table t (a int not null);
>
> insert into t select generate_Series(1,10);
>
> analyze p;
>
> analyze t;
>
> set enable_mergejoin=0;
> set enable_hashjoin=0;
>
> explain analyze select * from p inner join t on p.a = t.a;
>
> The patch gives me:
>
>                                        QUERY PLAN
> ----------------------------------------------------------------------------------------
>  Nested Loop (actual time=0.032..0.159 rows=10 loops=1)
>    ->  Seq Scan on t (actual time=0.012..0.013 rows=10 loops=1)
>    ->  Append (actual time=0.004..0.013 rows=1 loops=10)
>          ->  Index Scan using p1_a_idx on p1 (actual time=0.004..0.004
> rows=1 loops=10)
>                Index Cond: (a = t.a)
>          ->  Index Scan using p2_a_idx on p2 (actual time=0.003..0.003
> rows=0 loops=10)
>                Index Cond: (a = t.a)
>          ->  Index Scan using p3_a_idx on p3 (actual time=0.002..0.002
> rows=0 loops=10)
>                Index Cond: (a = t.a)
>          ->  Index Scan using p4_a_idx on p4 (actual time=0.003..0.003
> rows=0 loops=10)
>                Index Cond: (a = t.a)
>  Planning time: 0.472 ms
>  Execution time: 0.241 ms
> (13 rows)
>
> but I expected to get (never executed) for p2, p3 and p4.
>
> The following code makes me think you intend this to work:
>
> @@ -280,6 +438,10 @@ ExecReScanAppend(AppendState *node)
>  {
>   int i;
>
> + /* Determine subplans to scan based on the new Params */
> + if (node->ps.chgParam != NULL && node->join_clauses)
> + set_append_subplan_indexes(node, node->join_clauses);
> +
>
> It just does not due to the node->join_clauses being NULL.
>
Thank you for your tests. I am working on this.

-- 

Beena Emerson

EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company