tschwarzinger commented on issue #26169: URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6188246071
For me, when running `DATAFUSION_EXECUTION_PARQUET_PUSHDOWN_FILTERS=true cargo run --profile release-nonlto --bin dfbench -- tpch --debug --query 17 --format parquet --iterations 1 --path ./benchmarks/data/tpch_sf10/` The last `DataSourceExec` on line item has huge dynamic filters and the following metrics: ``` metrics=[output_rows=61.38 K, elapsed_compute=14.46s, output_bytes=1438.7 KB, output_batches=524, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, page_index_pages_pruned=3.14 K total → 3.14 K matched, page_index_rows_pruned=59.99 M total → 59.99 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=1907.0 MB, bytes_scanned=353.6 MB, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=61.38 K, pushdown_rows_pruned=59.92 M, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=59.99 M, predicate_cache_records=61.38 K, bloom_filter_eval_time=40.21µs, metadata_load_time=829.87µs, page_index_eval_time=7.45ms, row_pushdown_eval_time=11.37s, statistics_eval_time=283.26ms, time_elapsed_opening=1.51s, time_elapsed_processing=16.22s, time_elapsed_scanning_total=15. 67s, time_elapsed_scanning_until_data=1.45s, output_rows_skew=0.04%, scan_efficiency_ratio=18.54% (370.7 M/2.00 B)] ``` Interesting things. The full output is in the attached file (the dynamic filters are quite huge).: - `output_rows=61.38 K, elapsed_compute=14.46s`, evaluating the filters works but takes a long time. This is what https://github.com/apache/datafusion/pull/26249 is trying to improve upon. - Nothing can be pruned, as in the sketch that @alamb provided: files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, page_index_pages_pruned=3.14 K total → 3.14 K matched`. This could be because the relevant lineitems of the parts are actually distributed over all data pages or because the `CASE hash_repartition % 24 WHEN ...` type of expressions that the multi-partition hash join produces is not suited for pruning. duckdb also seems to read the entire table `in: 6.00 M out: 5,986,865 `. - We don't have the `DELIM_SCAN` I think the actual difference comes from i) high dynamic filter evaluation time and ii) possibly the approach that duckdb takes with the `DELIM_SCAN`. As far as I understand it, the delim scan reuses the *part ids* after filtering to join on the the line items. There it just has to build and probe a hash table with 2K keys instead of the 61.38 K hash table that we have in the top of the join tree which already joined the lineitems with the parts. In theory, the hash table look ups should be done in constant time but maybe the larger tables have significant effect on cache misses etc. such that the performance could be a different. One would have to measure though. [output.log](https://github.com/user-attachments/files/33483082/output.log) -- 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]
