From: | Thibaut <thibaut(dot)madelaine(at)dalibo(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>, "'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-21 13:28:16 |
Message-ID: | c6b9557e-c029-e72c-c697-3f216e559806@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Le 20/03/2019 à 10:06, Amit Langote a écrit :
> Hi Thibaut,
>
> On 2019/03/19 23:58, Thibaut Madelaine wrote:
>> I kept on testing with sub-partitioning.
> Thanks.
>
>> 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.
> I think I've figured what may be wrong.
>
> Partition pruning step generation code should ignore any arguments of an
> OR clause that won't be true for a sub-partitioned partition, given its
> partition constraint.
>
> In this case, id = 25 contradicts test2_0_20's partition constraint (which
> is, a IS NOT NULL AND a >= 0 AND a < 20), so the OR clause should really
> be simplified to id = 5, ignoring the id = 25 argument. Note that we
> remove id = 25 only for the considerations of pruning and not from the
> actual clause that's passed to the final plan, although it wouldn't be a
> bad idea to try to do that.
>
> Attached revised delta patch, which includes the fix described above.
>
> Thanks,
> Amit
Amit, I tested many cases with nested range sub-partitions... and I did
not find any problem with your last patch :-)
I tried mixing with hash partitions with no problems.
From the patch, there seems to be less checks than before. I cannot
think of a case that can have performance impacts.
Hosoya-san, if you agree with Amit's proposal, do you think you can send
a patch unifying your default_partition_pruning.patch and Amit's second
v1-delta.patch?
Cordialement,
Thibaut
From | Date | Subject | |
---|---|---|---|
Next Message | Pavan Deolasee | 2019-03-21 13:40:25 | Re: Re: A separate table level option to control compression |
Previous Message | Jeremy Finzel | 2019-03-21 13:27:20 | Re: Automated way to find actual COMMIT LSN of subxact LSN |