Repository navigation
Commit 6145a09
authored
feat: estimate Decimal filter selectivity with interval analysis (#25790)
## Which issue does this PR close?
- Closes #25613.
## Rationale for this change
Decimal columns were excluded from interval analysis, so any filter
touching
one fell back to the default 20% selectivity. For the Q6-style predicate
in the
issue, that meant estimating ~1.2M rows when only ~114K match.
## What changes are included in this PR?
- `FilterExec` statistics use a new `check_statistics_support`, which
also
accepts Decimal32/64/128/256.
- `next_value` / `prev_value` handle decimals, so strict comparisons get
tight
bounds (`x < 24.00` becomes `x <= 23.99`). At the limit of a decimal's
precision the bound becomes unbounded instead of overflowing.
- `get_extreme_value!` supports Decimal32/64, which previously hit
`unreachable!()`.
- `CastExpr` now propagates constraints back through lossless decimal
widening
casts. Without this, a literal with a different scale than the column
(e.g.
`a >= 0.055` on `Decimal(15, 2)`, coerced to `Decimal128(30, 15)`) left
the
column range untouched and the estimate at 100%.
- Decimal arithmetic (`+ - * / %`) is not analyzed: it changes the
precision
and scale of its operands, so those predicates keep the default
selectivity.
`check_support` and `is_datatype_supported` are unchanged, so symmetric
hash
join pruning behaves as before. Decimal support there could be a
follow-up.
## What is the testing strategy for this PR?
- Unit tests for decimal next/prev values across all four widths,
including
precision boundaries like 9.99 / -9.99 for `Decimal(3, 2)`, plus strict
comparison bounds and Decimal32/64 overflow handling.
- `test_filter_statistics_decimal_expr` and
`test_filter_statistics_decimal_mixed_scale_expr` check row estimates
and
tightened min/max for same-scale and mixed-scale predicates. The
mixed-scale
test mirrors the plan the SQL planner produces.
- `test_filter_statistics_decimal_arithmetic_expr` checks that decimal
multiplication and subtraction predicates use the default selectivity
and
keep the column's own type.
- `filter_statistics_decimal.slt` runs Q6-style ranges with fractional
literals on a parquet table with min/max stats, checking EXPLAIN
estimates
and query results, including a mixed-scale literal and decimal
arithmetic.
- New cases in `test_cast_constraint_propagation`, plus boundary checks
that
propagating through a decimal widening cast never excludes a valid
input,
covering cross-width casts, a negative scale and the precision limits.
- `test_custom_filter_selectivity` relied on decimals being unsupported,
so I
switched it to `Utf8`.
- No existing sqllogictest or TPC-H/TPC-DS plan outputs changed.
Reproducer from the issue (TPC-H SF1, current main):
| SELECT | Before | After | Actual |
| ------------------------- | --------: | ------: | ------: |
| Date range | 869,535 | 869,535 | 909,455 |
| Date + Decimal conditions | 1,200,243 | 111,291 | 114,160 |
Worth a closer look in review:
- The cast change extends the allowlist added in #25531. Decimal
widening is
lossless and order-preserving, and casting bounds back rounds to a
neighboring value, which only widens the range. That reasoning is what
makes it safe, so I'd appreciate a second opinion on it.
- A decimal column with no min/max stats now gets selectivity 1.0
instead of
the default, which matches how integer columns already behave.
## Are there any user-facing changes?
Better row estimates for filters on decimal columns, which may change
some
plan choices. One new public function, `check_statistics_support`; no
breaking
changes.1 parent 2bb383e commit 6145a09
5 files changed
Lines changed: 744 additions & 19 deletions
File tree
- datafusion
- expr-common/src
- physical-expr/src
- expressions
- intervals
- physical-plan/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
27 | 27 | | |
28 | 28 | | |
29 | 29 | | |
| 30 | + | |
30 | 31 | | |
31 | | - | |
| 32 | + | |
| 33 | + | |
32 | 34 | | |
33 | 35 | | |
34 | 36 | | |
| |||
37 | 39 | | |
38 | 40 | | |
39 | 41 | | |
40 | | - | |
| 42 | + | |
| 43 | + | |
| 44 | + | |
| 45 | + | |
| 46 | + | |
| 47 | + | |
| 48 | + | |
| 49 | + | |
41 | 50 | | |
42 | 51 | | |
43 | 52 | | |
| |||
84 | 93 | | |
85 | 94 | | |
86 | 95 | | |
| 96 | + | |
| 97 | + | |
| 98 | + | |
| 99 | + | |
| 100 | + | |
| 101 | + | |
| 102 | + | |
| 103 | + | |
| 104 | + | |
| 105 | + | |
87 | 106 | | |
88 | 107 | | |
89 | 108 | | |
| |||
100 | 119 | | |
101 | 120 | | |
102 | 121 | | |
103 | | - | |
| 122 | + | |
| 123 | + | |
| 124 | + | |
| 125 | + | |
| 126 | + | |
| 127 | + | |
| 128 | + | |
| 129 | + | |
| 130 | + | |
104 | 131 | | |
105 | 132 | | |
106 | 133 | | |
| |||
147 | 174 | | |
148 | 175 | | |
149 | 176 | | |
| 177 | + | |
| 178 | + | |
| 179 | + | |
| 180 | + | |
| 181 | + | |
| 182 | + | |
| 183 | + | |
| 184 | + | |
| 185 | + | |
| 186 | + | |
| 187 | + | |
| 188 | + | |
| 189 | + | |
| 190 | + | |
| 191 | + | |
| 192 | + | |
| 193 | + | |
| 194 | + | |
| 195 | + | |
| 196 | + | |
| 197 | + | |
| 198 | + | |
150 | 199 | | |
151 | 200 | | |
152 | 201 | | |
| |||
1208 | 1257 | | |
1209 | 1258 | | |
1210 | 1259 | | |
| 1260 | + | |
| 1261 | + | |
1211 | 1262 | | |
1212 | 1263 | | |
1213 | 1264 | | |
| |||
1216 | 1267 | | |
1217 | 1268 | | |
1218 | 1269 | | |
| 1270 | + | |
| 1271 | + | |
1219 | 1272 | | |
1220 | 1273 | | |
1221 | 1274 | | |
| |||
1228 | 1281 | | |
1229 | 1282 | | |
1230 | 1283 | | |
1231 | | - | |
| 1284 | + | |
| 1285 | + | |
| 1286 | + | |
| 1287 | + | |
| 1288 | + | |
| 1289 | + | |
| 1290 | + | |
| 1291 | + | |
| 1292 | + | |
1232 | 1293 | | |
1233 | 1294 | | |
1234 | 1295 | | |
1235 | 1296 | | |
1236 | 1297 | | |
1237 | 1298 | | |
1238 | | - | |
| 1299 | + | |
| 1300 | + | |
| 1301 | + | |
| 1302 | + | |
| 1303 | + | |
| 1304 | + | |
| 1305 | + | |
| 1306 | + | |
| 1307 | + | |
1239 | 1308 | | |
1240 | 1309 | | |
1241 | 1310 | | |
| |||
1246 | 1315 | | |
1247 | 1316 | | |
1248 | 1317 | | |
| 1318 | + | |
| 1319 | + | |
| 1320 | + | |
| 1321 | + | |
| 1322 | + | |
| 1323 | + | |
1249 | 1324 | | |
1250 | 1325 | | |
1251 | 1326 | | |
| |||
1300 | 1375 | | |
1301 | 1376 | | |
1302 | 1377 | | |
| 1378 | + | |
| 1379 | + | |
| 1380 | + | |
| 1381 | + | |
| 1382 | + | |
| 1383 | + | |
| 1384 | + | |
| 1385 | + | |
| 1386 | + | |
| 1387 | + | |
| 1388 | + | |
| 1389 | + | |
| 1390 | + | |
| 1391 | + | |
| 1392 | + | |
| 1393 | + | |
| 1394 | + | |
1303 | 1395 | | |
1304 | 1396 | | |
1305 | 1397 | | |
| |||
2281 | 2373 | | |
2282 | 2374 | | |
2283 | 2375 | | |
2284 | | - | |
| 2376 | + | |
| 2377 | + | |
| 2378 | + | |
| 2379 | + | |
2285 | 2380 | | |
2286 | 2381 | | |
2287 | 2382 | | |
| |||
2594 | 2689 | | |
2595 | 2690 | | |
2596 | 2691 | | |
| 2692 | + | |
| 2693 | + | |
| 2694 | + | |
| 2695 | + | |
| 2696 | + | |
| 2697 | + | |
| 2698 | + | |
| 2699 | + | |
| 2700 | + | |
| 2701 | + | |
| 2702 | + | |
| 2703 | + | |
| 2704 | + | |
| 2705 | + | |
| 2706 | + | |
| 2707 | + | |
| 2708 | + | |
| 2709 | + | |
| 2710 | + | |
| 2711 | + | |
| 2712 | + | |
| 2713 | + | |
| 2714 | + | |
| 2715 | + | |
| 2716 | + | |
| 2717 | + | |
| 2718 | + | |
| 2719 | + | |
| 2720 | + | |
| 2721 | + | |
| 2722 | + | |
| 2723 | + | |
| 2724 | + | |
| 2725 | + | |
| 2726 | + | |
| 2727 | + | |
| 2728 | + | |
| 2729 | + | |
| 2730 | + | |
| 2731 | + | |
| 2732 | + | |
| 2733 | + | |
| 2734 | + | |
| 2735 | + | |
| 2736 | + | |
| 2737 | + | |
| 2738 | + | |
| 2739 | + | |
| 2740 | + | |
| 2741 | + | |
| 2742 | + | |
| 2743 | + | |
| 2744 | + | |
| 2745 | + | |
| 2746 | + | |
| 2747 | + | |
| 2748 | + | |
| 2749 | + | |
| 2750 | + | |
| 2751 | + | |
| 2752 | + | |
| 2753 | + | |
| 2754 | + | |
| 2755 | + | |
| 2756 | + | |
| 2757 | + | |
| 2758 | + | |
| 2759 | + | |
| 2760 | + | |
| 2761 | + | |
| 2762 | + | |
| 2763 | + | |
| 2764 | + | |
| 2765 | + | |
| 2766 | + | |
| 2767 | + | |
| 2768 | + | |
| 2769 | + | |
| 2770 | + | |
| 2771 | + | |
| 2772 | + | |
| 2773 | + | |
| 2774 | + | |
| 2775 | + | |
| 2776 | + | |
| 2777 | + | |
| 2778 | + | |
| 2779 | + | |
| 2780 | + | |
| 2781 | + | |
| 2782 | + | |
| 2783 | + | |
| 2784 | + | |
| 2785 | + | |
| 2786 | + | |
| 2787 | + | |
| 2788 | + | |
| 2789 | + | |
| 2790 | + | |
| 2791 | + | |
| 2792 | + | |
| 2793 | + | |
| 2794 | + | |
| 2795 | + | |
| 2796 | + | |
| 2797 | + | |
| 2798 | + | |
| 2799 | + | |
| 2800 | + | |
| 2801 | + | |
| 2802 | + | |
| 2803 | + | |
| 2804 | + | |
| 2805 | + | |
| 2806 | + | |
| 2807 | + | |
| 2808 | + | |
| 2809 | + | |
2597 | 2810 | | |
2598 | 2811 | | |
2599 | 2812 | | |
| |||
4407 | 4620 | | |
4408 | 4621 | | |
4409 | 4622 | | |
| 4623 | + | |
| 4624 | + | |
| 4625 | + | |
| 4626 | + | |
| 4627 | + | |
| 4628 | + | |
| 4629 | + | |
| 4630 | + | |
| 4631 | + | |
| 4632 | + | |
| 4633 | + | |
| 4634 | + | |
| 4635 | + | |
| 4636 | + | |
| 4637 | + | |
| 4638 | + | |
| 4639 | + | |
| 4640 | + | |
4410 | 4641 | | |
4411 | 4642 | | |
4412 | 4643 | | |
| |||
0 commit comments