foxtail463 commented on PR #67182:
URL: https://github.com/apache/doris/pull/67182#issuecomment-5480173076
| 场景 | 第一轮：SQL 规划多花时间 | 第一轮：本规则耗时 | 第二轮：SQL 规划多花时间 | 第二轮：本规则耗时 |
|---|---:|---:|---:|---:|
| 无 Filter 的 Scan | ≈0（-84.0 μs，噪声） | 1.06 μs | ≈0（+9.9 μs） | 0.81 μs |
| 不支持推导的普通 Filter | +34.7 μs（+0.68%） | 16.02 μs | +13.6 μs（+0.32%） | 15.93
μs |
| `substring` 等值 | +281.2 μs（+6.30%） | 90.33 μs | +248.1 μs（+6.16%） | 74.58
μs |
| `year` 等值 | +339.1 μs（+7.39%） | 132.56 μs | +237.0 μs（+7.88%） | 92.51 μs |
| `date_format(%Y-%m)` 等值 | +793.7 μs（+19.61%） | 324.15 μs | +453.9
μs（+14.04%） | 244.21 μs |
| `date_format` 秒级格式等值 | +554.8 μs（+12.22%） | 306.83 μs | +436.5 μs（+11.56%）
| 250.75 μs |
| `date_trunc(day)` 等值 | +310.4 μs（+7.69%） | 147.74 μs | +244.1 μs（+6.74%） |
100.20 μs |
| `date_floor(interval 3 day)` 等值 | +249.8 μs（+5.73%） | 132.01 μs | +160.2
μs（+6.74%） | 86.28 μs |
| `month_floor(period=3)` 等值 | +277.3 μs（+6.59%） | 152.93 μs | +138.7
μs（+6.74%） | 79.72 μs |
| `month_ceil(period=3)` 等值 | +218.0 μs（+6.99%） | 114.43 μs | +181.0
μs（+8.13%） | 80.75 μs |
| `to_date` 等值 | +120.3 μs（+5.07%） | 74.77 μs | +145.3 μs（+6.37%） | 89.66 μs
|
| `to_monday` 等值 | +119.2 μs（+6.46%） | 69.30 μs | +140.3 μs（+8.02%） | 59.25
μs |
| 单 Scan 混合 4 类推导 | +361.9 μs（+8.09%） | 260.50 μs | +328.5 μs（+8.23%） |
160.09 μs |
| 单 Scan、16 个 OR 分支 | +1058.6 μs（+7.84%） | 263.87 μs | +891.7 μs（+9.39%） |
221.61 μs |
| 8 个 UNION ALL Scan | +898.8 μs（+6.43%） | 230.25 μs | +493.9 μs（+5.86%） |
170.54 μs |
```sql
CREATE DATABASE IF NOT EXISTS monotonic_pruning_benchmark;
USE monotonic_pruning_benchmark;
SET disable_nereids_rules = 'PRUNE_EMPTY_PARTITION';
CREATE TABLE monotonic_bench (
id BIGINT NOT NULL,
dt DATETIMEV2 NOT NULL,
v VARCHAR(128) NOT NULL
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES ('replication_num' = '1');
-- scan_only
SELECT * FROM monotonic_bench;
-- unsupported_filter
SELECT * FROM monotonic_bench WHERE id > 10;
-- prefix_equal
SELECT * FROM monotonic_bench WHERE substring(v, 1, 3) = 'abc';
-- year_equal
SELECT * FROM monotonic_bench WHERE year(dt) = 2026;
-- date_format_month_equal
SELECT * FROM monotonic_bench WHERE date_format(dt, '%Y-%m') = '2026-07';
-- date_format_second_equal
SELECT * FROM monotonic_bench WHERE date_format(dt, '%Y-%m-%d %H:%i:%s') =
'2026-07-28 12:34:56';
-- date_trunc_day_equal
SELECT * FROM monotonic_bench WHERE date_trunc(dt, 'day') = '2026-07-28
00:00:00';
-- date_floor_three_days_equal
SELECT * FROM monotonic_bench WHERE date_floor(dt, interval 3 day) =
'2026-07-28 00:00:00';
-- month_floor_period_equal
SELECT * FROM monotonic_bench WHERE month_floor(dt, 3) = '2026-07-01
00:00:00';
-- month_ceil_period_equal
SELECT * FROM monotonic_bench WHERE month_ceil(dt, 3) = '2026-07-01
00:00:00';
-- to_date_equal
SELECT * FROM monotonic_bench WHERE to_date(dt) = '2026-07-28';
-- to_monday_equal
SELECT * FROM monotonic_bench WHERE to_monday(dt) = '2026-07-27';
-- single_scan_mixed_inferences
SELECT * FROM monotonic_bench WHERE year(dt) = 2026 AND substring(v, 1, 3) =
'abc' AND date_format(dt, '%Y-%m') = '2026-07' AND date_trunc(dt, 'day') =
'2026-07-28 00:00:00';
-- sixteen_branch_or
SELECT * FROM monotonic_bench WHERE (year(dt) = 2000 AND substring(v, 1, 3)
= '000') OR
(year(dt) = 2001 AND substring(v, 1, 3) = '001') OR
(year(dt) = 2002 AND substring(v, 1, 3) = '002') OR
(year(dt) = 2003 AND substring(v, 1, 3) = '003') OR
(year(dt) = 2004 AND substring(v, 1, 3) = '004') OR
(year(dt) = 2005 AND substring(v, 1, 3) = '005') OR
(year(dt) = 2006 AND substring(v, 1, 3) = '006') OR
(year(dt) = 2007 AND substring(v, 1, 3) = '007') OR
(year(dt) = 2008 AND substring(v, 1, 3) = '008') OR
(year(dt) = 2009 AND substring(v, 1, 3) = '009') OR
(year(dt) = 2010 AND substring(v, 1, 3) = '010') OR
(year(dt) = 2011 AND substring(v, 1, 3) = '011') OR
(year(dt) = 2012 AND substring(v, 1, 3) = '012') OR
(year(dt) = 2013 AND substring(v, 1, 3) = '013') OR
(year(dt) = 2014 AND substring(v, 1, 3) = '014') OR
(year(dt) = 2015 AND substring(v, 1, 3) = '015');
-- eight_scan_union
SELECT * FROM monotonic_bench WHERE year(dt) = 2020 AND substring(v, 1, 3) =
'000' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2021 AND substring(v, 1, 3) =
'001' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2022 AND substring(v, 1, 3) =
'002' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2023 AND substring(v, 1, 3) =
'003' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2024 AND substring(v, 1, 3) =
'004' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2025 AND substring(v, 1, 3) =
'005' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2026 AND substring(v, 1, 3) =
'006' UNION ALL
SELECT * FROM monotonic_bench WHERE year(dt) = 2027 AND substring(v, 1, 3) =
'007';
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
