From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Thibaut Madelaine <thibaut(dot)madelaine(at)dalibo(dot)com>, 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-20 09:06:13 |
Message-ID: | 9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v1-delta.patch | text/plain | 3.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2019-03-20 09:06:40 | Re: speeding up planning with partitions |
Previous Message | Amit Kapila | 2019-03-20 08:38:10 | Re: Transaction commits VS Transaction commits (with parallel) VS query mean time |