gruuya commented on PR #25292:
URL: https://github.com/apache/datafusion/pull/25292#issuecomment-5748576884
Thanks @jayzhan211, that's a good point. I've clauded up a fix for that,
alongside some other improvements that the agent flagged:
- build the pruning expressions exactly once (including across
with_new_children, file, row-group, page)
- enabling the optimization for the partitioned path
- release the raw array as soon as we build the pruning expression
- avoid `ScalarValue` (which introduces a unneeded round-trip)
- replace explicit dict-based deduping with implicit
`PrimitiveInListPruningExpr::new` sort + dedup
That said, the example you provide is a worst case one for this feature:
`unclustered keys so nothing prunes` meaning we expend the (now minimized and
amortized) cost of creating the pruning expression and running it without any
benefits.
Numbers I'm seeing
```sql
> COPY (SELECT value AS a, value % 2000 AS p FROM generate_series(1, 400000)
t(value))
TO 'many_int/' STORED AS PARQUET PARTITIONED BY (p);
COPY (SELECT value * 4 AS k FROM generate_series(1, 100000) t(value))
TO 'dim_int.parquet' STORED AS PARQUET;
CREATE EXTERNAL TABLE probe_i STORED AS PARQUET LOCATION 'many_int/';
CREATE EXTERNAL TABLE dim_i STORED AS PARQUET LOCATION 'dim_int.parquet';
+--------+
| count |
+--------+
| 400000 |
+--------+
1 row(s) fetched.
Elapsed 1.142 seconds.
+--------+
| count |
+--------+
| 100000 |
+--------+
1 row(s) fetched.
Elapsed 0.007 seconds.
0 row(s) fetched.
Elapsed 0.227 seconds.
0 row(s) fetched.
Elapsed 0.001 seconds.
> SELECT count(*) FROM dim_i JOIN probe_i ON dim_i.k = probe_i.a; SELECT
count(*) FROM dim_i JOIN probe_i ON dim_i.k = probe_i.a; SELECT count(*) FROM
dim_i JOIN probe_i ON dim_i.k = probe_i.a;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.088 seconds.
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.072 seconds.
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.072 seconds.
> SET datafusion.optimizer.hash_join_dynamic_pruning_max_distinct_values = 0;
0 row(s) fetched.
Elapsed 0.000 seconds.
> SELECT count(*) FROM dim_i JOIN probe_i ON dim_i.k = probe_i.a; SELECT
count(*) FROM dim_i JOIN probe_i ON dim_i.k = probe_i.a; SELECT count(*) FROM
dim_i JOIN probe_i ON dim_i.k = probe_i.a;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.090 seconds.
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.070 seconds.
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row(s) fetched.
Elapsed 0.080 seconds.
```
So `on` and `off` are no longer off by a OOM but are very close by (and
inside the noise margin). However `off` should always have the best time in
this scenario by design.
I'm wondering what this means about the default value of
`hash_join_dynamic_pruning_max_distinct_values`
1. leave as is
2. reduce (32K?)
3. disable by default (0)
Also any chance you or @2010YOUY01 can kick-off the tpcds benchmarks, since
I don't have the permissions yet?
--
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]
