RE: Problem with default partition pruning - Mailing list pgsql-hackers

Hi,

Thanks a lot for additional tests and the new patch.


> 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?
>

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)


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?


Best regards,
Yuzuko Hosoya