adriangb commented on PR #15770:
URL: https://github.com/apache/datafusion/pull/15770#issuecomment-2959969124
Here's the current Q23 benchmarks on my local machine on this branch using
the following test script:
```sql
-- Current status quo before this PR: filter pushdown on, no dynamic filter
pushdown, default target partitions.
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 0;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
-- With dynamic filters turned on
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
-- With filter pushdown off, dynamic filters on
SET datafusion.execution.parquet.pushdown_filters = false;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
-- With dynamic filters off and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
-- With dynamic filters on and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
```
```
DataFusion CLI v48.0.0
> -- Current status quo before this PR: filter pushdown on, no dynamic
filter pushdown, default target partitions.
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 0;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.003 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.214 seconds.
> -- With dynamic filters turned on
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.091 seconds.
> -- With filter pushdown off, dynamic filters on
SET datafusion.execution.parquet.pushdown_filters = false;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.104 seconds.
> -- With dynamic filters off and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.803 seconds.
> -- With dynamic filters on and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.095 seconds.
```
<details>
<summary>And with explain plans</summary>
```
> -- Current status quo before this PR: filter pushdown on, no dynamic
filter pushdown, default target partitions.
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 0;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | SortPreservingMergeExec: [EventTime@0 ASC NULLS LAST],
fetch=10, metrics=[output_rows=10, elapsed_compute=2.208µs]
|
| | SortExec: TopK(fetch=10), expr=[EventTime@0 ASC
NULLS LAST], preserve_partitioning=[true], filter=[EventTime@0 < 1372708802],
metrics=[output_rows=120, elapsed_compute=454.554682ms, row_replacements=4239]
|
| | DataSourceExec: file_groups={12 groups:
[[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_0.parquet:0..122446530,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_1.parquet:0..174965044,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_10.parquet:0..101513258,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_11.parquet:0..118419888,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_12.parquet:0..149514164,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_17.parquet:59860177..116867853,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_18.parquet:0..133119589,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_19.parquet:0..103692598,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_2.parquet:0..230595491,
Users/adriangb/GitHub/datafusion/benchmarks/data/hit
s_partitioned/hits_20.parquet:0..85766533, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_26.parquet:122668027..156510916,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_27.parquet:0..166286210,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_28.parquet:0..162772407,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_29.parquet:0..79213288,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_3.parquet:0..192507052,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_35.parquet:54087341..153632381,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_36.parquet:0..92487304,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_37.parquet:0..108247781,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_38.parquet:0..132005180,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitione
d/hits_39.parquet:0..103522954, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_42.parquet:118278445..288524057,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_43.parquet:0..299692947,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_44.parquet:0..242404750,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_45.parquet:0..148061387,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_46.parquet:0..92407680,
...], ...]}, projection=[EventTime], file_type=parquet,
metrics=[output_rows=99997497, elapsed_compute=12ns, bytes_scanned=413239770,
file_open_errors=0, file_scan_errors=0, num_predicate_creation_errors=0,
page_index_rows_matched=0, page_index_rows_pruned=0,
predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0,
row_groups_matched_bloom_filter=0, row_groups_matched_statistics=0,
row_groups_pruned_bloom_filter=0, row_groups_pruned_statistics=
0, bloom_filter_eval_time=222ns, metadata_load_time=131.111478ms,
page_index_eval_time=222ns, row_pushdown_eval_time=222ns,
statistics_eval_time=222ns, time_elapsed_opening=17.80283ms,
time_elapsed_processing=607.505781ms, time_elapsed_scanning_total=1.202561798s,
time_elapsed_scanning_until_data=185.184704ms] |
| |
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.160 seconds.
> -- With dynamic filters turned on
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | SortPreservingMergeExec: [EventTime@0 ASC NULLS LAST],
fetch=10, metrics=[output_rows=10, elapsed_compute=2.208µs]
|
| | SortExec: TopK(fetch=10), expr=[EventTime@0 ASC
NULLS LAST], preserve_partitioning=[true], filter=[EventTime@0 < 1372708802],
metrics=[output_rows=120, elapsed_compute=28.725461ms, row_replacements=2513]
|
| | DataSourceExec: file_groups={12 groups:
[[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_0.parquet:0..122446530,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_1.parquet:0..174965044,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_10.parquet:0..101513258,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_11.parquet:0..118419888,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_12.parquet:0..149514164,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_17.parquet:59860177..116867853,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_18.parquet:0..133119589,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_19.parquet:0..103692598,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_2.parquet:0..230595491,
Users/adriangb/GitHub/datafusion/benchmarks/data/hit
s_partitioned/hits_20.parquet:0..85766533, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_26.parquet:122668027..156510916,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_27.parquet:0..166286210,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_28.parquet:0..162772407,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_29.parquet:0..79213288,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_3.parquet:0..192507052,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_35.parquet:54087341..153632381,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_36.parquet:0..92487304,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_37.parquet:0..108247781,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_38.parquet:0..132005180,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitione
d/hits_39.parquet:0..103522954, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_42.parquet:118278445..288524057,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_43.parquet:0..299692947,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_44.parquet:0..242404750,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_45.parquet:0..148061387,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_46.parquet:0..92407680,
...], ...]}, projection=[EventTime], file_type=parquet,
predicate=DynamicFilterPhysicalExpr [ EventTime@0 < 1372708802 ],
pruning_predicate=EventTime_null_count@1 != row_count@2 AND EventTime_min@0 <
1372708802, required_guarantees=[] |
| | , metrics=[output_rows=6238470, elapsed_compute=12ns,
bytes_scanned=195749199, file_open_errors=0, file_scan_errors=0,
num_predicate_creation_errors=0, page_index_rows_matched=0,
page_index_rows_pruned=0, predicate_evaluation_errors=0,
pushdown_rows_matched=6238470, pushdown_rows_pruned=40208246,
row_groups_matched_bloom_filter=0, row_groups_matched_statistics=87,
row_groups_pruned_bloom_filter=0, row_groups_pruned_statistics=180,
bloom_filter_eval_time=329.981µs, metadata_load_time=109.567194ms,
page_index_eval_time=5.192µs, row_pushdown_eval_time=10.217872ms,
statistics_eval_time=610.473µs, time_elapsed_opening=35.371746ms,
time_elapsed_processing=413.242048ms, time_elapsed_scanning_total=506.896542ms,
time_elapsed_scanning_until_data=326.002671ms]
|
| |
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.090 seconds.
> -- With filter pushdown off, dynamic filters on
SET datafusion.execution.parquet.pushdown_filters = false;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 0;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | SortPreservingMergeExec: [EventTime@0 ASC NULLS LAST],
fetch=10, metrics=[output_rows=10, elapsed_compute=1.833µs]
|
| | SortExec: TopK(fetch=10), expr=[EventTime@0 ASC
NULLS LAST], preserve_partitioning=[true], filter=[EventTime@0 < 1372708804],
metrics=[output_rows=120, elapsed_compute=198.546347ms, row_replacements=4021]
|
| | DataSourceExec: file_groups={12 groups:
[[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_0.parquet:0..122446530,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_1.parquet:0..174965044,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_10.parquet:0..101513258,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_11.parquet:0..118419888,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_12.parquet:0..149514164,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_17.parquet:59860177..116867853,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_18.parquet:0..133119589,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_19.parquet:0..103692598,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_2.parquet:0..230595491,
Users/adriangb/GitHub/datafusion/benchmarks/data/hit
s_partitioned/hits_20.parquet:0..85766533, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_26.parquet:122668027..156510916,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_27.parquet:0..166286210,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_28.parquet:0..162772407,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_29.parquet:0..79213288,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_3.parquet:0..192507052,
...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_35.parquet:54087341..153632381,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_36.parquet:0..92487304,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_37.parquet:0..108247781,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_38.parquet:0..132005180,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitione
d/hits_39.parquet:0..103522954, ...],
[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_42.parquet:118278445..288524057,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_43.parquet:0..299692947,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_44.parquet:0..242404750,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_45.parquet:0..148061387,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_46.parquet:0..92407680,
...], ...]}, projection=[EventTime], file_type=parquet,
predicate=DynamicFilterPhysicalExpr [ EventTime@0 < 1372708804 ],
pruning_predicate=EventTime_null_count@1 != row_count@2 AND EventTime_min@0 <
1372708804, required_guarantees=[] |
| | , metrics=[output_rows=43758473, elapsed_compute=12ns,
bytes_scanned=184844240, file_open_errors=0, file_scan_errors=0,
num_predicate_creation_errors=0, page_index_rows_matched=0,
page_index_rows_pruned=0, predicate_evaluation_errors=0,
pushdown_rows_matched=0, pushdown_rows_pruned=0,
row_groups_matched_bloom_filter=0, row_groups_matched_statistics=80,
row_groups_pruned_bloom_filter=0, row_groups_pruned_statistics=186,
bloom_filter_eval_time=297.062µs, metadata_load_time=91.240148ms,
page_index_eval_time=4.319µs, row_pushdown_eval_time=222ns,
statistics_eval_time=524.059µs, time_elapsed_opening=36.330791ms,
time_elapsed_processing=295.282703ms, time_elapsed_scanning_total=533.27498ms,
time_elapsed_scanning_until_data=105.782465ms]
|
| |
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.106 seconds.
> -- With dynamic filters off and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 1;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.001 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | SortExec: TopK(fetch=10), expr=[EventTime@0 ASC NULLS
LAST], preserve_partitioning=[false], filter=[EventTime@0 < 1372708800],
metrics=[output_rows=10, elapsed_compute=349.617653ms, row_replacements=367]
|
| | DataSourceExec: file_groups={1 group:
[[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_0.parquet:0..122446530,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_1.parquet:0..174965044,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_10.parquet:0..101513258,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_11.parquet:0..118419888,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_12.parquet:0..149514164,
...]]}, projection=[EventTime], file_type=parquet,
metrics=[output_rows=99997497, elapsed_compute=1ns, bytes_scanned=413239770,
file_open_errors=0, file_scan_errors=0, num_predicate_creation_errors=0,
page_index_rows_matched=0, page_index_rows_pruned=0,
predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0,
row_groups_matched_bloom_filter=0, row_groups_matched_statistics=0,
row_groups_pruned_bloom_filter=0, row_groups_pruned
_statistics=0, bloom_filter_eval_time=200ns, metadata_load_time=24.393942ms,
page_index_eval_time=200ns, row_pushdown_eval_time=200ns,
statistics_eval_time=200ns, time_elapsed_opening=1.642709ms,
time_elapsed_processing=374.266438ms, time_elapsed_scanning_total=778.7721ms,
time_elapsed_scanning_until_data=72.534498ms] |
| |
|
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.823 seconds.
> -- With dynamic filters on and target partitions set to 1
SET datafusion.execution.parquet.pushdown_filters = true;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 1;
explain analyze SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/'
ORDER BY "EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | SortExec: TopK(fetch=10), expr=[EventTime@0 ASC NULLS
LAST], preserve_partitioning=[false], filter=[EventTime@0 < 1372708800],
metrics=[output_rows=10, elapsed_compute=3.53979ms, row_replacements=367]
|
| | DataSourceExec: file_groups={1 group:
[[Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_0.parquet:0..122446530,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_1.parquet:0..174965044,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_10.parquet:0..101513258,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_11.parquet:0..118419888,
Users/adriangb/GitHub/datafusion/benchmarks/data/hits_partitioned/hits_12.parquet:0..149514164,
...]]}, projection=[EventTime], file_type=parquet,
predicate=DynamicFilterPhysicalExpr [ EventTime@0 < 1372708800 ],
pruning_predicate=EventTime_null_count@1 != row_count@2 AND EventTime_min@0 <
1372708800, required_guarantees=[] |
| | , metrics=[output_rows=705744, elapsed_compute=1ns,
bytes_scanned=15876270, file_open_errors=0, file_scan_errors=0,
num_predicate_creation_errors=0, page_index_rows_matched=0,
page_index_rows_pruned=0, predicate_evaluation_errors=0,
pushdown_rows_matched=705744, pushdown_rows_pruned=3294256,
row_groups_matched_bloom_filter=0, row_groups_matched_statistics=7,
row_groups_pruned_bloom_filter=0, row_groups_pruned_statistics=313,
bloom_filter_eval_time=23.77µs, metadata_load_time=23.91685ms,
page_index_eval_time=2.322µs, row_pushdown_eval_time=1.768526ms,
statistics_eval_time=204.189µs, time_elapsed_opening=22.822957ms,
time_elapsed_processing=48.607876ms, time_elapsed_scanning_total=35.223838ms,
time_elapsed_scanning_until_data=11.836286ms] |
| |
|
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.094 seconds.
```
</details>
The interesting bit is that this is now *faster even with predicate pushdown
turned off* thanks to the late partition / stats based pruning @alamb !! For
the case of a single partition it's **14x faster**:
```
> SET datafusion.execution.parquet.pushdown_filters = false;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 1.169 seconds.
> SET datafusion.execution.parquet.pushdown_filters = false;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = true;
SET datafusion.execution.target_partitions = 1;
SELECT "EventTime" FROM 'benchmarks/data/hits_partitioned/' ORDER BY
"EventTime" LIMIT 10;
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+------------+
| EventTime |
+------------+
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
| 1372708800 |
+------------+
10 row(s) fetched.
Elapsed 0.082 seconds.
```
--
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]
