RE: Problem with default partition pruning

Amit-san,

> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp]
> Sent: Friday, April 05, 2019 6:47 PM
> To: Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>; 'Thibaut' <thibaut(dot)madelaine(at)dalibo(dot)com>; '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
>
> 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