Re: [HACKERS] Runtime Partition Pruning

On 7 December 2017 at 23:56, Beena Emerson <memissemerson(at)gmail(dot)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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services