kumarUjjawal opened a new pull request, #27548:
URL: https://github.com/apache/datafusion/pull/27548

   ## Which issue does this PR close?
   
   - Closes #25621.
   - Part of #25610.
   
   ## Rationale for this change
   
   `FilterExec` estimated every `col IN (...)` and `col NOT IN (...)` at the 
20% default. On TPC-DS SF1 `date_dim`, `d_moy IN (1, ..., 12)` gave 14,610 rows 
instead of 73,049, and `d_moy NOT IN (1, ..., 12)` gave 14,610 instead of 0.
   
   ## What changes are included in this PR?
   
   - When the whole predicate is one `col [NOT] IN (literals)`, the estimate is 
the share of the column's distinct values that the list names, within the 
non-null rows. Listed values outside min and max match nothing. `NOT IN` with a 
NULL item matches nothing.
   - The distinct count comes from the column statistics. Without one, integer 
and `Date32` columns use the number of values from min to max, capped at the 
non-null rows. Other columns keep the default, because their ranges hold many 
more values than the column.
   - An `IN` list inside an `AND`, or with a non-literal item, keeps the 
default. #25612 covers `AND` predicates.
   - `IN` and `NOT IN` now reject NULLs in the output column statistics, as 
`LIKE` does.
   - `unique_match_limit` and the new estimate share one list parser. The 
null-row scaling moved unchanged from `string_selectivity` into a shared helper.
   
   `dfbench statistics` on TPC-DS SF1 (data from 
apache/datafusion-benchmarks@32f67477):
   
   | Query | Predicate | Before | After | Actual |
   | --- | --- | ---: | ---: | ---: |
   | q23 | `d_year IN (4 years)` | 14,610 | 1,454 | 1,461 |
   | q29 | `d_year IN (3 years)` | 14,610 | 1,091 | 1,096 |
   | q53, q63 | `d_month_seq IN (12 months)` | 14,610 | 366 | 365 |
   | q45 | `i_item_sk IN (10 keys)` | 3,600 | 10 | 10 |
   
   - All 14 changed `FilterExec` estimates (10 `IN` filters and 4 filters above 
them in q23) moved closer to the actual rows. The p50 q-error went from 3.00x 
to 2.97x.
   - Only q45 changed plan shape: the hash join now builds from the 10-row 
`item` filter. Over 10 iterations, q45 took 392.8 ms on `main` and 385.7 ms 
with this PR. q53 and the untouched q3 stayed within 1%.
   - In q23 the plan did not change. But the joins and aggregates above the 
more accurate date filter now estimate fewer rows, so their q-error grew (for 
example, 143x to 1,441x). The 10x over-estimate of the filter hid an existing 
join under-estimate.
   
   ## What is the testing strategy for this PR?
   
   - `parquet_statistics.slt` checks `EXPLAIN` row estimates for `IN` and `NOT 
IN` on a Parquet file without a distinct count.
   - `sql_in_list_filter_selectivity` in `custom_sources_cases/statistics.rs` 
checks SQL predicates on integer and string columns.
   - Unit tests in `filter.rs` cover the issue's cases, out-of-range and 
repeated values, NULL items and bounds, null and row counts, the types that 
keep the default, and predicates that keep the default.
   - `test_filter_statistics_in_list_on_a_unique_column` now expects `NOT IN` 
of 3 of 100 unique values to give 97 rows, not the default 20.
   - `./dev/rust_lint.sh` passes.
   
   ## Are there any user-facing changes?
   
   Row estimates change for filters that are one `IN` or `NOT IN` list of 
literals, and this can change plans that use them. There is no API change.
   


-- 
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]

Reply via email to