opt: prune unnecessary synthesized columns for CHECK constraints #51526
Description
During mutations, the optimizer synthesizes columns for CHECK constraints and partial index predicates. These columns are included in the query plan, regardless if it can be proven that they do not need to be calculated. We should prune these columns (or not even created them in optbuilder) if they do not need to be created.
Example of current behavior:
root@127.0.0.1:61646/defaultdb> create table t (a int, b int, check (b > 0), index (b) where b > 10);
CREATE TABLE
Time: 3.123ms
root@127.0.0.1:61646/defaultdb> explain (opt, verbose) update t set a = 1 where a = 0;
text
----------------------------------------------------------------------------------------------------
update t
├── columns: <none>
├── fetch columns: a:4 b:5 rowid:6
├── update-mapping:
│ └── a_new:8 => a:1
├── check columns: check1:9
├── partial index put columns: partial_index_del1:7
├── partial index del columns: partial_index_del1:7
├── cardinality: [0 - 0]
├── volatile, mutations
├── stats: [rows=0]
├── cost: 1130.46
└── project
├── columns: check1:9 a_new:8 partial_index_del1:7 a:4 b:5 rowid:6
├── stats: [rows=10]
├── cost: 1130.45
├── key: (6)
├── fd: ()-->(4,8), (6)-->(5), (5)-->(7,9)
├── prune: (4-9)
├── select
│ ├── columns: a:4 b:5 rowid:6
│ ├── stats: [rows=10, distinct(4)=1, null(4)=0]
│ ├── cost: 1130.04
│ ├── key: (6)
│ ├── fd: ()-->(4), (6)-->(5)
│ ├── scan t
│ │ ├── columns: a:4 b:5 rowid:6
│ │ ├── partial index predicates
│ │ │ └── t_b_idx: filters
│ │ │ └── b:5 > 10 [outer=(5), constraints=(/5: [/11 - ]; tight)]
│ │ ├── stats: [rows=1000, distinct(4)=100, null(4)=10, distinct(6)=1000, null(6)=0]
│ │ ├── cost: 1120.02
│ │ ├── key: (6)
│ │ ├── fd: (6)-->(4,5)
│ │ └── prune: (4-6)
│ └── filters
│ └── a:4 = 0 [outer=(4), constraints=(/4: [/0 - /0]; tight), fd=()-->(4)]
└── projections
├── b:5 > 0 [as=check1:9, outer=(5)]
├── 1 [as=a_new:8]
└── b:5 > 10 [as=partial_index_del1:7, outer=(5)]
(41 rows)
The partial_index_del1 and check1 columns are unnecessary because the UPDATE does not refer to any of the columns in the CHECK constraint or partial index.