RE: Problem with default partition pruning

From: "Yuzuko Hosoya" <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
To: "'Amit Langote'" <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, "'Thibaut'" <thibaut(dot)madelaine(at)dalibo(dot)com>, "'Imai, Yoshikazu'" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: "'PostgreSQL Hackers'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: Problem with default partition pruning
Date: 2019-03-19 07:01:09
Message-ID: 003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Amit-san,

From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-03-19 07:09:13 Re: [PATCH] src/test/modules/dummy_index -- way to test reloptions from inside of access method
Previous Message Kyotaro HORIGUCHI 2019-03-19 07:00:58 Re: Proposal to suppress errors thrown by to_reg*()