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