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

On Wed, Jul 11, 2018 at 5:10 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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.
>
I am not sure that I have understand the following comments
11 + * Generate one prune step for the information derived from IS NULL,
12 + * if any. To prune hash partitions, we must have found IS NULL
13 + * clauses for all partition keys.
14 */

I am not sure that I have understood this -- no such restriction
required to prune the hash partitions, if I am not missing anything.

Regards,
Amul