partition pruning doesn't work with IS NULL clause in multikey rangepartition case

Поиск
Список
Период
Сортировка
От Ashutosh Bapat
Тема partition pruning doesn't work with IS NULL clause in multikey rangepartition case
Дата
Msg-id CAFjFpRc7qjLUfXLVBBC_HAnx644sjTYM=qVoT3TJ840HPbsTXw@mail.gmail.com
обсуждение исходный текст
Ответы Re: partition pruning doesn't work with IS NULL clause in multikeyrange partition case  (Dilip Kumar <dilipbalaut@gmail.com>)
Список pgsql-hackers
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;

In a range partitioned table, a row with any partition key NULL goes
to the default partition if it exists.
insert into prt values (null, 1);
insert into prt values (1, null);
insert into prt values (null, null);
select tableoid::regclass, * from prt;
 tableoid | a | b | c
----------+---+---+---
 prt_def  |   | 1 |
 prt_def  | 1 |   |
 prt_def  |   |   |
(3 rows)

There's a comment in get_partition_for_tuple(), which says so.
/*
 * No range includes NULL, so this will be accepted by the
 * default partition if there is one, and otherwise rejected.
 */

But when there is IS NULL clause on any of the partition keys with
some condition on other partition key, all the partitions scanned. I
expected pruning to prune all the partitions except the default one.

explain verbose select * from prt where a is null and b = 100;
                              QUERY PLAN
----------------------------------------------------------------------
 Append  (cost=0.00..106.52 rows=3 width=12)
   ->  Seq Scan on public.prt_p1  (cost=0.00..35.50 rows=1 width=12)
         Output: prt_p1.a, prt_p1.b, prt_p1.c
         Filter: ((prt_p1.a IS NULL) AND (prt_p1.b = 100))
   ->  Seq Scan on public.prt_p2  (cost=0.00..35.50 rows=1 width=12)
         Output: prt_p2.a, prt_p2.b, prt_p2.c
         Filter: ((prt_p2.a IS NULL) AND (prt_p2.b = 100))
   ->  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))
(10 rows)

I thought that the following code in get_matching_range_bounds()
    /*
     * If there are no datums to compare keys with, or if we got an IS NULL
     * clause just return the default partition, if it exists.
     */
    if (boundinfo->ndatums == 0 || !bms_is_empty(nullkeys))
    {
        result->scan_default = partition_bound_has_default(boundinfo);
        return result;
    }

would do the trick but through the debugger I saw that nullkeys is
NULL for this query.

I didn't investigate further to see why nullkeys is NULL, but it looks
like that's the problem and we are missing an optimization.
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Dmitry Dolgov
Дата:
Сообщение: Re: Problem with tupdesc in jsonb_to_recordset
Следующее
От: amul sul
Дата:
Сообщение: Cannot dump foreign key constraints on partitioned table