Re: partition pruning doesn't work with IS NULL clause in multikeyrange partition case - Mailing list pgsql-hackers

On Wed, Jul 11, 2018 at 5:10 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> 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.
>
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