devanbenz commented on PR #26249:
URL: https://github.com/apache/datafusion/pull/26249#issuecomment-6201620853
@tschwarzinger @adriangb
> I think the only thing where this could be counter-productive is, if the
data source can apply pruning with the CASE expression but not with the new
special-purpose expression. I am not sure whether this happening or is even
worth doing.
I was able to reproduce this and generate a table + query (with the help of
my buddy claude) to get pruning in the CASE expr working, but, not working in
my specialization:
```
Probe: /tmp/df-prune/probe.parquet (2.7 MB)
- Schema: k Int64 NOT NULL, v Int64 NOT NULL (v = k % 7)
- Rows: 1,000,000, with k = 0…999999, sorted
- Row groups: 100, each holding 10,000 rows with no overlap
┌───────────┬───────┬────────┬────────┐
│ row group │ rows │ k min │ k max │
├───────────┼───────┼────────┼────────┤
│ 0 │ 10000 │ 0 │ 9999 │
├───────────┼───────┼────────┼────────┤
│ 1 │ 10000 │ 10000 │ 19999 │
├───────────┼───────┼────────┼────────┤
│ … │ … │ … │ … │
├───────────┼───────┼────────┼────────┤
│ 98 │ 10000 │ 980000 │ 989999 │
├───────────┼───────┼────────┼────────┤
│ 99 │ 10000 │ 990000 │ 999999 │
┌────────────────────────┬───────────────────────────────────────────────────────────┬─────────────────┐
│ build │ probe scan filter
│ row groups read │
├────────────────────────┼───────────────────────────────────────────────────────────┼─────────────────┤
│ base (9551051f1, main) │ CASE hash_repartition % 4 WHEN 0 THEN … AND
hash_lookup … │ 2 of 100 │
├────────────────────────┼───────────────────────────────────────────────────────────┼─────────────────┤
│ PR (4c7c51a5c) │ k >= 0 AND k <= 999999 AND
partitioned_hash_lookup(4) │ 100 of 100 │
└────────────────────────┴───────────────────────────────────────────────────────────┴─────────────────┘
```
Where the query is like so (including building parquet files):
```
-- Does the probe-side scan prune row groups using the hash join dynamic
filter?
-- Base: CASE hash % N WHEN i THEN hash_lookup_i ... -> pruning sees
per-partition KEY_RANGE_BITMAPs.
-- PR: partitioned_hash_lookup(...) -> pruning only sees the merged [min,
max] bounds.
-- probe: k = 0..999_999 sorted, 100 row groups of 10_000 rows
COPY (SELECT value AS k, value % 7 AS v FROM generate_series(0, 999999)
ORDER BY k)
TO '/tmp/df-prune/probe.parquet' STORED AS PARQUET
OPTIONS ('format.max_row_group_size' '10000');
-- build: 2000 distinct keys (~500 per partition > 150 -> hash-table
strategy, not IN LIST),
-- clustered at both ends so the [min, max] bounds cover the whole probe
range
COPY (
SELECT value AS k FROM generate_series(0, 999)
UNION ALL
SELECT value AS k FROM generate_series(999000, 999999)
) TO '/tmp/df-prune/build.parquet' STORED AS PARQUET;
CREATE EXTERNAL TABLE probe STORED AS PARQUET LOCATION
'/tmp/df-prune/probe.parquet';
CREATE EXTERNAL TABLE build STORED AS PARQUET LOCATION
'/tmp/df-prune/build.parquet';
-- force a Partitioned (multi-partition build) hash join with `build` on the
build side
SET datafusion.execution.target_partitions = 4;
SET datafusion.optimizer.repartition_joins = true;
SET datafusion.optimizer.hash_join_single_partition_threshold = 0;
SET datafusion.optimizer.hash_join_single_partition_threshold_rows = 0;
SET datafusion.optimizer.join_reordering = false;
EXPLAIN ANALYZE
SELECT count(*), sum(p.v)
FROM build b JOIN probe p ON b.k = p.k;
SELECT count(*), sum(p.v) FROM build b JOIN probe p ON b.k = p.k;
```
Going to continue playing with this and getting it to a much better state.
--
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]
