Subject: Re: Problem with default partition pruning
To: Yuzuko Hosoya <hosoya.yuzuko@lab.ntt.co.jp>,
 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>,
 "'Imai, Yoshikazu'" <imai.yoshikazu@jp.fujitsu.com>
Cc: 'PostgreSQL Hackers' <pgsql-hackers@lists.postgresql.org>
References: <00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp>
 <afef4fb8-c448-d54a-9628-7c26277d4895@lab.ntt.co.jp>
 <010e01d4ce68$c0427b10$40c77130$@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A5129A78A@g01jpexmbkw24>
 <a4968068-6401-7a9c-8bd4-6a3bc9164a86@dalibo.com>
 <001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp>
 <9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp>
 <003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp>
From: Thibaut Madelaine <thibaut.madelaine@dalibo.com>
Message-ID: <bd03f475-30d4-c4d0-3d7f-d2fbde755971@dalibo.com>
Date: Tue, 19 Mar 2019 15:58:57 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.1
MIME-Version: 1.0
In-Reply-To: <003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: en-GB
Precedence: bulk


Le 19/03/2019 à 08:01, Yuzuko Hosoya a écrit :
> Hi Amit-san,
>
> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> Sent: Monday, March 18, 2019 6:44 PM
>  
>> Hosoya-san,
>>
>> On 2019/03/15 15:05, Yuzuko Hosoya wrote:
>>> Indeed, it's problematic.  I also did test and I found that this
>>> problem was occurred when any partition didn't match WHERE clauses.
>>> So following query didn't work correctly.
>>>
>>> # explain select * from test1_3 where (id > 0 and id < 30);
>>>                            QUERY PLAN
>>> -----------------------------------------------------------------
>>>  Append  (cost=0.00..58.16 rows=12 width=36)
>>>    ->  Seq Scan on test1_3_1  (cost=0.00..29.05 rows=6 width=36)
>>>          Filter: ((id > 0) AND (id < 30))
>>>    ->  Seq Scan on test1_3_2  (cost=0.00..29.05 rows=6 width=36)
>>>          Filter: ((id > 0) AND (id < 30))
>>> (5 rows)
>>>
>>> I created a new patch to handle this problem, and confirmed the query
>>> you mentioned works as expected
>>>
>>> # explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230);
>>>                                 QUERY PLAN
>>> ----------------------------------------------------------------------
>>> -----  Append  (cost=0.00..70.93 rows=26 width=36)
>>>    ->  Seq Scan on test1_1_1  (cost=0.00..35.40 rows=13 width=36)
>>>          Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
>>>    ->  Seq Scan on test1_3_1  (cost=0.00..35.40 rows=13 width=36)
>>>          Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id <
>>> 230)))
>>> (5 rows)
>>>
>>> v2 patch attached.
>>> Could you please check it again?
>> I think the updated patch breaks the promise that get_matching_range_bounds won't set scan_default
>> based on individual pruning value comparisons.  How about the attached delta patch that applies on
>> top of your earlier v1 patch, which fixes the issue reported by Thibaut?
>>
> Indeed.  I agreed with your proposal.
> Also, I confirmed your patch works correctly.
>
> Best regards,
> Yuzuko Hosoya

I kept on testing with sub-partitioning.
I found a case, using 2 default partitions, where a default partition is
not pruned:

--------------

create table test2(id int, val text) partition by range (id);
create table test2_20_plus_def partition of test2 default;
create table test2_0_20 partition of test2 for values from (0) to (20)
  partition by range (id);
create table test2_0_10 partition of test2_0_20 for values from (0) to (10);
create table test2_10_20_def partition of test2_0_20 default;

# explain (costs off) select * from test2 where id=5 or id=25;
               QUERY PLAN               
-----------------------------------------
 Append
   ->  Seq Scan on test2_0_10
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_10_20_def
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_20_plus_def
         Filter: ((id = 5) OR (id = 25))
(7 rows)

--------------

I have the same output using Amit's v1-delta.patch or Hosoya's
v2_default_partition_pruning.patch.