From: "Yuzuko Hosoya" <hosoya.yuzuko@lab.ntt.co.jp>
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>
In-Reply-To: <9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp>
Subject: RE: Problem with default partition pruning
Date: Tue, 19 Mar 2019 16:01:09 +0900
Message-ID: <003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit
Content-language: ja
Thread-index: 
 AQLwSThp7L0tC61luGAd4dJt3HxY3QJvrgn9AgtGZCUBpM85VAEOZ04CAeo+SK4CDbFrsqOCe+pw
To: "'Amit Langote'" <Langote_Amit_f8@lab.ntt.co.jp>,
        "'Thibaut'" <thibaut.madelaine@dalibo.com>,
        "'Imai, Yoshikazu'" <imai.yoshikazu@jp.fujitsu.com>
Cc: "'PostgreSQL Hackers'" <pgsql-hackers@lists.postgresql.org>
Precedence: bulk

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