Apply constraint exclusion more generally in partitioning

Enterprise / PostgreSQL - Alvaro Herrera [] - 7 August 2019 16:21 UTC

We were applying constraint exclusion on the partition constraint when generating pruning steps for a clause, but only for the rather restricted situation of them being boolean OR operators; however it is possible to have differently shaped clauses that also benefit from constraint exclusion. This applies particularly to the default partition since their constraints are in essence a long list of OR'ed subclauses ... but it applies to other cases too. So in certain cases we're scanning partitions that we don't need to.

Remove the specialized code in OR clauses, and add a generally applicable test of the clause refuting the partition constraint; mark the whole pruning operation as contradictory if it hits.

This has the unwanted side-effect of testing some (most? all?) constraints more than once if constraint_exclusion=on. That seems unavoidable as far as I can tell without some additional work, but that's not the recommended setting for that parameter anyway. However, because this imposes additional processing cost for all queries using partitioned tables, I decided not to backpatch this change.

Author: Amit Langote, Yuzuko Hosoya, Álvaro Herrera Reviewers: Shawn Wang, Thibaut Madeleine, Yoshikazu Imai, Kyotaro Horiguchi; they were also uncredited reviewers for commit 489247b0e615. Discussion:

4e85642d93 Apply constraint exclusion more generally in partitioning
src/backend/partitioning/partprune.c | 56 ++++++++++++++-------------
src/test/regress/expected/partition_prune.out | 18 +++++++++
src/test/regress/sql/partition_prune.sql | 4 ++
3 files changed, 52 insertions(+), 26 deletions(-)


  • Share