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

On Wed, Jul 11, 2018 at 5:36 PM, amul sul <sulamul(at)gmail(dot)com> wrote:
> On Wed, Jul 11, 2018 at 5:10 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

>>
> 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.

Maybe it's not very clear but this is the original comments I have
retained. Just moved it out of the (!generate_opsteps) condition.

Just the explain this comment consider below example,

create table hp (a int, b text) partition by hash (a int, b text);
create table hp0 partition of hp for values with (modulus 4, remainder 0);
create table hp3 partition of hp for values with (modulus 4, remainder 3);
create table hp1 partition of hp for values with (modulus 4, remainder 1);
create table hp2 partition of hp for values with (modulus 4, remainder 2);

postgres=# insert into hp values (1, null);
INSERT 0 1
postgres=# insert into hp values (2, null);
INSERT 0 1
postgres=# select tableoid::regclass, * from hp;
tableoid | a | b
----------+---+---
hp1 | 1 |
hp2 | 2 |
(2 rows)

Now, if we query based on "b is null" then we can not decide which
partition should be pruned whereas in case
of other schemes, it will go to default partition so we can prune all
other partitions.

explain (costs off) select * from hp where b is null;

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