Re: partition pruning doesn't work with IS NULL clause in multikeyrange partition case

On Wed, Jul 11, 2018 at 4:20 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> On Wed, Jul 11, 2018 at 3:06 PM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> Hi,
>> Consider following test case.
>> create table prt (a int, b int, c int) partition by range(a, b);
>> create table prt_p1 partition of prt for values (0, 0) to (100, 100);
>> create table prt_p1 partition of prt for values from (0, 0) to (100, 100);
>> create table prt_p2 partition of prt for values from (100, 100) to (200, 200);
>> create table prt_def partition of prt default;
>>

> --- a/src/backend/partitioning/partprune.c
> +++ b/src/backend/partitioning/partprune.c
> @@ -857,7 +857,7 @@
> gen_partprune_steps_internal(GeneratePruningStepsContext *context,
>          * If generate_opsteps is set to false it means no OpExprs were directly
>          * present in the input list.
>          */
> -       if (!generate_opsteps)
> +       if (nullkeys || !generate_opsteps)
>         {
>                 /*
>                  * Generate one prune step for the information derived
> from IS NULL,
> @@ -865,8 +865,7 @@
> gen_partprune_steps_internal(GeneratePruningStepsContext *context,
>                  * clauses for all partition keys.
>                  */
>                 if (!bms_is_empty(nullkeys) &&
> -                       (part_scheme->strategy != PARTITION_STRATEGY_HASH ||
> -                        bms_num_members(nullkeys) == part_scheme->partnatts))
> +                       (part_scheme->strategy != PARTITION_STRATEGY_HASH))
>                 {
>                         PartitionPruneStep *step;
>
> postgres=# explain verbose select * from prt where a is null and b = 100;
>                               QUERY PLAN
> ----------------------------------------------------------------------
>  Append  (cost=0.00..35.51 rows=1 width=12)
>    ->  Seq Scan on public.prt_def  (cost=0.00..35.50 rows=1 width=12)
>          Output: prt_def.a, prt_def.b, prt_def.c
>          Filter: ((prt_def.a IS NULL) AND (prt_def.b = 100))
> (4 rows)
>
> Above fix is just to show the root cause of the issue, I haven't
> investigated that what should be the exact fix for this issue.
>

I think the actual fix should be as attached.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com