pingcap / pingcap/tidb

Predicate push down caused unexpected non-optimal plan

Open
#38,784 1 comment 1 reaction 1 assignee Claimed by @winoros View on GitHub
affects-6.4 affects-6.5 may-affects-4.0 may-affects-5.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.0 may-affects-6.1 may-affects-6.2 may-affects-6.3 sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

Explain the query-9 of chbenchmark.

```
select
n_name,
extract(
year
from
o_entry_d
) as l_year,
sum(ol_amount) as sum_profit
from
item,
stock,
supplier,
order_line,
orders,
nation
where
ol_i_id = s_i_id
and ol_supply_w_id = s_w_id
and mod(
(s_w_id * s_i_id),
10000
) = s_suppkey
and ol_w_id = o_w_id
and ol_d_id = o_d_id
and ol_o_id = o_id
and ol_i_id = i_id
and s_nationkey = n_nationkey
and i_data like '%BB'
group by
n_name,
extract(
year
from
o_entry_d
)
order by
n_name,
l_year desc;
```

### 2. What did you expect to see? (Required)

As you can see, there's `ol_i_id = s_i_id `, `ol_i_id = i_id `, and `mod((s_w_id * s_i_id), 10000) = s_suppkey`.
We can get `s_i_id = i_id` from the equivalence class propagation.
Then TiDB would use that to generate `mod((s_w_id * i_id), 10000) = s_suppkey`.

It's an equal function though one side is a scalar function. TiDB only accepts `col_x = col_y` as the join key. So TiDB would add a projection to project `mod((s_w_id * i_id), 10000) = s_suppkey` as `Col#x`. Then let `mod((s_w_id * i_id), 10000) = s_suppkey` become `Col#x = s_suppkey`.

So far the optimization sounds good. However, this projection cut the join tree into two parts, and unfortunately, the cut part is a cartesian product of table `item` and table `stock`. Finally, the SQL got a bad performance than we expected.

### 3. What did you see instead (Required)

No cartesian product in the final plan.

### 4. What is your TiDB version? (Required)

current master.

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.