Re: why partition pruning doesn't work?

On 2018/06/06 18:52, David Rowley wrote:
> On 6 June 2018 at 18:05, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2018/06/06 14:10, David Rowley wrote:
>>> I then decided that
>>> I didn't like the way we need to check which params are in the Expr
>>> each time we call partkey_datum_from_expr. It seems better to prepare
>>> this in advance when building the pruning steps. I started work on
>>> that, but soon realised that I'd need to pass a List of Bitmapsets to
>>> the executor. This is a problem as Bitmapset is not a Node type and
>>> cannot be copied with COPY_NODE_FIELD(). Probably this could be
>>> refactored to instead of passing 3 Lists in the PartitionPruneStepOp
>>> we could invent a new node type that just has 3 fields and store a
>>> single List.
>>
>> I wonder why we need to create those Bitmapsets in the planner?  Why not
>> in ExecSetupPartitionPruneState()?  For example, like how
>> context->exprstates is initialized.
> 
> That seems like a good idea.  Certainly much better than working them
> out each time we prune.
> 
> v3 patch attached.

Thanks David.  This one looks good.  I also like it that hasparamlessexprs
is no longer determined and set in the planner.

I checked what happens with the cases that Ashutosh complained about
upthread and seems that the pruning works as expected.

create table t1 (a int, b int) partition by range (a);
create table t1p1 partition of t1 for values from (0) to (100);
create table t1p2 partition of t1 for values from (100) to (200);
create index on t1 (a);
insert into t1 select i, i from generate_series(0, 199) i;

explain (costs off, analyze) select * from t1 x left join t1 y on x.a =
y.b + 100 where y.a = 5;
                                          QUERY PLAN

-----------------------------------------------------------------------------------------------
 Nested Loop (actual time=0.294..0.371 rows=1 loops=1)
   ->  Append (actual time=0.067..0.092 rows=1 loops=1)
         ->  Bitmap Heap Scan on t1p1 y (actual time=0.049..0.059 rows=1
loops=1)
               Recheck Cond: (a = 5)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on t1p1_a_idx (actual
time=0.022..0.022 rows=1 loops=1)
                     Index Cond: (a = 5)
   ->  Append (actual time=0.192..0.219 rows=1 loops=1)
         ->  Index Scan using t1p1_a_idx on t1p1 x (never executed)
               Index Cond: (a = (y.b + 100))
         ->  Index Scan using t1p2_a_idx on t1p2 x_1 (actual
time=0.134..0.145 rows=1 loops=1)
               Index Cond: (a = (y.b + 100))
 Planning Time: 5.314 ms
 Execution Time: 0.938 ms
(14 rows)

Note that the condition x.a = y.b + 100 is able to prune t1p1, whereas on
HEAD it isn't.

Thanks,
Amit