Re: Problem with default partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Yuzuko Hosoya <hosoya(dot)yuzuko(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-18 09:44:07
Message-ID: 9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Thanks,
Amit

Attachment Content-Type Size
v1-delta.patch text/plain 1.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2019-03-18 09:56:15 Re: speeding up planning with partitions
Previous Message David Rowley 2019-03-18 09:35:05 Re: Tid scan improvements