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

Amit-san,

> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> Sent: Friday, April 05, 2019 6:47 PM
> To: Yuzuko Hosoya <hosoya.yuzuko@lab.ntt.co.jp>; 'Thibaut' <thibaut.madelaine@dalibo.com>; 'Imai,
> Yoshikazu' <imai.yoshikazu@jp.fujitsu.com>
> Cc: 'PostgreSQL Hackers' <pgsql-hackers@lists.postgresql.org>
> Subject: Re: Problem with default partition pruning
>
> Hosoya-san,
>
>
> On 2019/04/04 13:00, Yuzuko Hosoya wrote:
> > I added some test cases to each patch according to tests discussed in
> > this thread.
>
> Thanks a lot.
>
> > However, I found another problem as follows. This query should output
> > "One-Time Filter: false" because rlp3's constraints contradict WHERE
> > clause.
> >
> > -----
> > postgres=# \d+ rlp3
> >                                    Partitioned table "public.rlp3"
> >  Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description
> >
> --------+-------------------+-----------+----------+---------+----------+--------------+---------
> ----
> >  b      | character varying |           |          |         | extended |              |
> >  a      | integer           |           |          |         | plain    |              |
> > Partition of: rlp FOR VALUES FROM (15) TO (20) Partition constraint:
> > ((a IS NOT NULL) AND (a >= 15) AND (a < 20)) Partition key: LIST (b
> > varchar_ops)
> > Partitions: rlp3abcd FOR VALUES IN ('ab', 'cd'),
> >             rlp3efgh FOR VALUES IN ('ef', 'gh'),
> >             rlp3nullxy FOR VALUES IN (NULL, 'xy'),
> >             rlp3_default DEFAULT
> >
> > postgres=# explain select * from rlp3 where a = 2;
> >                              QUERY PLAN
> > --------------------------------------------------------------------
> >  Append  (cost=0.00..103.62 rows=24 width=36)
> >    ->  Seq Scan on rlp3abcd  (cost=0.00..25.88 rows=6 width=36)
> >          Filter: (a = 2)
> >    ->  Seq Scan on rlp3efgh  (cost=0.00..25.88 rows=6 width=36)
> >          Filter: (a = 2)
> >    ->  Seq Scan on rlp3nullxy  (cost=0.00..25.88 rows=6 width=36)
> >          Filter: (a = 2)
> >    ->  Seq Scan on rlp3_default  (cost=0.00..25.88 rows=6 width=36)
> >          Filter: (a = 2)
> > (9 rows)
> > -----
>
> This one too would be solved with the other patch I mentioned to fix
> get_relation_info() to load the partition constraint so that constraint exclusion can use it.
> Partition in the earlier example given by Thibaut is a leaf partition, whereas rlp3 above is a
> sub-partitioned partition, but both are partitions nonetheless.
>
> Fixing partprune.c like we're doing with the
> v2_ignore_contradictory_where_clauses_at_partprune_step.patch only works for the latter, because only
> partitioned tables visit partprune.c.
>
> OTOH, the other patch only applies to situations where constraint_exclusion = on.
>
I see.  I think that following example discussed in this thread before would
also be solved with your patch, not v2_ignore_contradictory_where_clauses_at_partprune_step.patch.

postgres=# set constraint_exclusion to on;

postgres=# explain select * from test2_0_20 where id = 25;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)


> > I think that the place of check contradiction process was wrong At
> > ignore_contradictory_where_clauses_at_partprune_step.patch.
> > So I fixed it.
>
> Thanks.  Patch contains some whitespace noise:
>
> $ git diff --check
> src/backend/partitioning/partprune.c:790: trailing whitespace.
> +         * given its partition constraint, we can ignore it,
> src/backend/partitioning/partprune.c:791: trailing whitespace.
> +         * that is not try to pass it to the pruning code.
> src/backend/partitioning/partprune.c:792: trailing whitespace.
> +         * We should do that especially to avoid pruning code
> src/backend/partitioning/partprune.c:810: trailing whitespace.
> +
> src/test/regress/sql/partition_prune.sql:87: trailing whitespace.
> +-- where clause contradicts sub-partition's constraint
>
> Can you please fix it?
>
Thanks for checking.
I'm attaching the latest patch.

>
> BTW, now I'm a bit puzzled between whether this case should be fixed by hacking on partprune.c like
> this patch does or whether to work on getting the other patch committed and expect users to set
> constraint_exclusion = on for this to behave as expected.  The original intention of setting
> partition_qual in set_relation_partition_info() was for partprune.c to use it to remove useless
> arguments of OR clauses which otherwise would cause the failure to correctly prune the default partitions
> of sub-partitioned tables.  As shown by the examples in this thread, the original effort was
> insufficient, which this patch aims to improve.  But, it also expands the scope of partprune.c's usage
> of partition_qual, which is to effectively perform full-blown constraint exclusion without being
> controllable by constraint_exclusion GUC, which may be seen as being good or bad.  The fact that it
> helps in getting partition pruning working correctly in more obscure cases like those discussed in
> this thread means it's good maybe.
>
Umm, even though this modification might be overhead, I think this problem should be solved
without setting constraint_exclusion GUC. But I'm not sure.

Best regards,
Yuzuko Hosoya