cockroachdb / cockroachdb/cockroach

opt: improve column-pruning for mutation operators

Open
#113,612 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement E-quick-win O-support P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

We have a rule, `PruneMutationReturnCols`, which can unneeded prune columns of a mutation under a `Project` operator. However, we can't handle cases where the parent operator isn't a `Project`. This can be fixed by adding cases for the mutation operators to `DerivePruneCols`. This will allow non-project operators to see when it would be possible to prune mutation columns, so that they can push down a `Project` operator in order to fire `PruneMutationReturnCols`.

Here's an optimizer test demonstrating the problem:
```
exec-ddl
CREATE TABLE xy (x INT, y INT);
----

exec-ddl
CREATE TABLE ab (a INT, b INT, INDEX foo (a) STORING (b));
----

opt
UPDATE xy SET y = b
FROM ab@foo WHERE x = a;
----
update xy
├── columns:
├── fetch columns: x:6 y:7 xy.rowid:8
├── passthrough columns: a:11 b:12 ab.rowid:13 ab.crdb_internal_mvcc_timestamp:14 ab.tableoid:15
├── update-mapping:
│ └── b:12 => y:2
├── cardinality: [0 - 0]
├── volatile, mutations
└── distinct-on
├── columns: x:6!null y:7 xy.rowid:8!null a:11!null b:12 ab.rowid:13!null ab.crdb_internal_mvcc_timestamp:14 ab.tableoid:15
├── grouping columns: xy.rowid:8!null
├── key: (8)
├── fd: (8)-->(6,7,11-15), (13)-->(11,12,14,15), (6)==(11), (11)==(6)
├── inner-join (hash)
│ ├── columns: x:6!null y:7 xy.rowid:8!null a:11!null b:12 ab.rowid:13!null ab.crdb_internal_mvcc_timestamp:14 ab.tableoid:15
│ ├── key: (8,13)
│ ├── fd: (8)-->(6,7), (13)-->(11,12,14,15), (6)==(11), (11)==(6)
│ ├── scan xy
│ │ ├── columns: x:6 y:7 xy.rowid:8!null
│ │ ├── key: (8)
│ │ └── fd: (8)-->(6,7)
│ ├── index-join ab
│ │ ├── columns: a:11 b:12 ab.rowid:13!null ab.crdb_internal_mvcc_timestamp:14 ab.tableoid:15
│ │ ├── key: (13)
│ │ ├── fd: (13)-->(11,12,14,15)
│ │ └── scan ab@foo
│ │ ├── columns: a:11 b:12 ab.rowid:13!null
│ │ ├── flags: force-index=foo
│ │ ├── key: (13)
│ │ └── fd: (13)-->(11,12)
│ └── filters
│ └── x:6 = a:11 [outer=(6,11), constraints=(/6: (/NULL - ]; /11: (/NULL - ]), fd=(6)==(11), (11)==(6)]
└── aggregations
├── first-agg [as=x:6, outer=(6)]
│ └── x:6
├── first-agg [as=y:7, outer=(7)]
│ └── y:7
├── first-agg [as=a:11, outer=(11)]
│ └── a:11
├── first-agg [as=b:12, outer=(12)]
│ └── b:12
├── first-agg [as=ab.rowid:13, outer=(13)]
│ └── ab.rowid:13
├── first-agg [as=ab.crdb_internal_mvcc_timestamp:14, outer=(14)]
│ └── ab.crdb_internal_mvcc_timestamp:14
└── first-agg [as=ab.tableoid:15, outer=(15)]
└── ab.tableoid:15
```
Note the unnecessary index join on `ab@primary` in order to fetch the `crdb_internal_mvcc_timestamp` and `tableoid` columns.

Jira issue: CRDB-33070

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.