Re: Problem with default partition pruning
red about another use case. If default partition contains rows whose > id = 300 and then we add another partition which have constraints like id >= > 300 and id < 400, I thought we won't scan the rows anymore. But I noticed we > simply can't add such a partition. > > postgres=# insert into test1 values (300); > INSERT 0 1 > postgres=# create table test1_3 partition of test1 for values from (300) to > (400); > ERROR: updated partition constraint for default partition "test1_def" would > be violated by some row > > > So I haven't come up with bad cases so far :) > > -- > Yoshikazu Imai Hello Yoshikazu-San, I tested your patch using some sub-partitions and found a possible problem. I create a new partitioned partition test1_3 with 2 sub-partitions : - create table test1_3 partition of test1 for values from (200) to (400) partition by range (id); create table test1_3_1 partition of test1_3 for values from (200) to (250); create table test1_3_2 partition of test1_3 for values from (250) to (350); # explain select * from test1 where (id > 0 and id < 30); QUERY PLAN --- Append (cost=0.00..29.08 rows=6 width=36) -> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((id > 0) AND (id < 30)) (3 rows) # explain select * from test1 where (id > 220 and id < 230); QUERY PLAN - Append (cost=0.00..29.08 rows=6 width=36) -> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((id > 220) AND (id < 230)) (3 rows) # explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230); QUERY PLAN --- Append (cost=0.00..106.40 rows=39 width=36) -> Seq Scan on test1_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))) -> Seq Scan on test1_3_2 (cost=0.00..35.40 rows=13 width=36) Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230))) (7 rows) - Partition pruning is functioning when only the sub-partition is required. When both the partition and the sub-partition is required, there is no pruning on the sub-partition. Cordialement, -- Thibaut Madelaine Dalibo
Re: Problem with default partition pruning
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
Re: selecting from partitions and constraint exclusion
Le 25/03/2019 à 01:31, Amit Langote a écrit : > On 2019/03/22 17:17, Amit Langote wrote: >> I'll add this to July fest to avoid forgetting about this. > I'd forgotten to do this, but done today. :) > > Thanks, > Amit Hello Amit, Just a quick information that your last patch does not apply on head: $ git apply ~/Téléchargements/v2-0001-Fix-planner-to-load-partition-constraint-in-some-.patch error: patch failed: src/test/regress/expected/partition_prune.out:3637 error: src/test/regress/expected/partition_prune.out: patch does not apply Manually applying it on top of Hosoya's last 2 patches, It corrects the different cases we found so far. I will keep on testing next week. Cordialement, Thibaut
Re: Problem with default partition pruning
Le 19/03/2019 à 08:01, Yuzuko Hosoya a écrit : > Hi Amit-san, > > From: Amit Langote [mailto:langote_amit...@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 I kept on testing with sub-partitioning. 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.
Re: Problem with default partition pruning
Le 22/03/2019 à 07:38, Amit Langote a écrit : > Hosoya-san, > > On 2019/03/22 15:02, Yuzuko Hosoya wrote: >> I understood Amit's proposal. But I think the issue Thibaut reported would >> occur regardless of whether clauses have OR clauses or not as follows. >> I tested a query which should output "One-Time Filter: false". >> >> # explain select * from test2_0_20 where id = 25; >> QUERY PLAN >> --- >> Append (cost=0.00..25.91 rows=6 width=36) >>-> Seq Scan on test2_10_20_def (cost=0.00..25.88 rows=6 width=36) >> Filter: (id = 25) >> > Good catch, thanks. > >> As Amit described in the previous email, id = 25 contradicts test2_0_20's >> partition constraint, so I think this clause should be ignored and we can >> also handle this case in the similar way as Amit proposal. >> >> I attached v1-delta-2.patch which fix the above issue. >> >> What do you think about it? > It looks fine to me. You put the code block to check whether a give > clause contradicts the partition constraint in its perfect place. :) > > Maybe we should have two patches as we seem to be improving two things: > > 1. Patch to fix problems with default partition pruning originally > reported by Hosoya-san > > 2. Patch to determine if a given clause contradicts a sub-partitioned > table's partition constraint, fixing problems unearthed by Thibaut's tests > > About the patch that Horiguchi-san proposed upthread, I think it has merit > that it will make partprune.c code easier to reason about, but I think we > should pursue it separately. > > Thanks, > Amit Hosoya-san, very good idea to run queries directly on tables partitions! I tested your last patch and if I didn't mix up patches on the end of a too long week, I get a problem when querying the sub-sub partition: test=# explain select * from test2_0_10 where id = 25; QUERY PLAN Seq Scan on test2_0_10 (cost=0.00..25.88 rows=6 width=36) Filter: (id = 25) (2 rows) Cordialement, Thibaut
