pingcap / pingcap/tidb

Support eliminate common expression in selection and projection

Open
#58,126 1 comment 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
planner/cascades sig/planner type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**

It is a common pattern that we want to filter by an expression as well as know the result of that expression, like: (this example comes from in [MySQL manual](https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#operator_json-column-path))

```sql
create table j(a json);
select JSON_EXTRACT(a, "$.id") FROM j WHERE JSON_EXTRACT(a, "$.id") > 1;
```

An ideal execution plan is:

```
TableScan → Projection(JSON_EXTRACT(a, "$.id") → Col#1) → Selection(Col#1 > 1)
```

The current execution plan is:

```
TableScan → Selection(JSON_EXTRACT(a, "$.id") > 1) → Projection(JSON_EXTRACT(a, "$.id") → Col#1)
```

The ideal execution plan is more efficient due to reducing duplicated function calculations:

Suppose there are N rows in TableScan and K rows after filtering.

- In ideal plan, JSON_EXTRACT() executes N times, GT() executes N times
- In current plan, JSON_EXTRACT() executes N+K times, GT() executes N times

This optimization is very useful when the common expression is heavy. Common expression is also very commonly used, for example, in scenarios like Full Text Search, to return the search rank: (this is a recommended way of using FTS by [MySQL manual](https://dev.mysql.com/doc/refman/8.4/en/fulltext-natural-language.html))

```sql
mysql> SELECT id, body, MATCH (title,body)
-> AGAINST ('Security implications of running MySQL as root'
-> IN NATURAL LANGUAGE MODE) AS score
-> FROM articles
-> WHERE MATCH (title,body)
-> AGAINST('Security implications of running MySQL as root'
-> IN NATURAL LANGUAGE MODE);
```

**Describe the feature you'd like:**

Optimizer generates the ideal plan.

**Describe alternatives you've considered:**

**Teachability, Documentation, Adoption, Migration Strategy:**

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.