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

On Wed, Jul 11, 2018 at 4:20 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> On Wed, Jul 11, 2018 at 3:06 PM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)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