You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "debajyoti-truefoundry (via GitHub)" <gi...@apache.org> on 2025/06/30 09:35:15 UTC
[I] Performance of `distinct on (columns)` [datafusion]
debajyoti-truefoundry opened a new issue, #16620:
URL: https://github.com/apache/datafusion/issues/16620
### Describe the bug
The query filter selects `492435` rows. As there may be duplicates, I need to execute a distinct query on a column. Then order by timestamp, and retrieve the top 10 rows. In this case, all `492435` rows were unique; there were **no** duplicates.
I executed these queries in an identical environment (AWS EC2 M7A). The binary is compiled with the following settings and using the native CPU features.
```toml
[profile.release]
opt-level = 3
codegen-units = 1
```
1. `distinct on ("SpanId")`. Approximately **2 seconds** of wall clock execution time.
```
SortPreservingMergeExec: [Timestamp@1 ASC NULLS LAST], fetch=10, metrics=[output_rows=10, elapsed_compute=56.441µs]
SortExec: TopK(fetch=10), expr=[Timestamp@1 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=80, elapsed_compute=12.32639ms, row_replacements=568]
ProjectionExec: expr=[first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanId)@1 as SpanId, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Timestamp)@2 as Timestamp, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TraceId)@3 as TraceId, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanName)@4 as SpanName, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ScopeName)@5 as ScopeName, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName)@6 as ServiceName, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ParentSpanId)@7 as ParentSpanId, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration)@8 as Duration, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Tags)@9 as Tags, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanKind)@10 as SpanKind, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.MlRepoId)@11 as MlRepoId, first_value(TracingProjectId)@12 as TracingProjectId, first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode)@13 as StatusCode, first_value(tbl_
6baa3c6fd52949c18602a6b38294e8d9.TsBucketStart)@14 as TsBucketStart, ((first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName)@6 = user-service OR first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName)@6 = auth-service) AND first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanName)@4 LIKE %login% AND first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration)@8 >= 1000000 AND first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration)@8 <= 5000000 AND first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode)@13 >= 200 AND first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode)@13 <= 299) IS NOT DISTINCT FROM true as matched], metrics=[output_rows=492435, elapsed_compute=1.681826ms]
AggregateExec: mode=FinalPartitioned, gby=[SpanId@0 as SpanId], aggr=[first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Timestamp), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TraceId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ScopeName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ParentSpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Tags), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanKind), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.MlRepoId), first_value(TracingProjectId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TsBucketStart)], metrics=[output_rows=492435, elapsed_compute=3.882494179s, spill_count=0, spilled_bytes=0, spilled
_rows=0, peak_mem_used=1414466147]
CoalesceBatchesExec: target_batch_size=20000, metrics=[output_rows=492435, elapsed_compute=916.376µs]
RepartitionExec: partitioning=Hash([SpanId@0], 8), input_partitions=8, metrics=[fetch_time=3.424462188s, repartition_time=75.357473ms, send_time=327.861036ms]
AggregateExec: mode=Partial, gby=[SpanId@3 as SpanId], aggr=[first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Timestamp), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TraceId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ScopeName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ParentSpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Tags), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanKind), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.MlRepoId), first_value(TracingProjectId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TsBucketStart)], metrics=[output_rows=492435, elapsed_compute=2.742439769s, spill_count=0, spilled_bytes=0, spilled_ro
ws=0, skipped_aggregation_rows=191476, peak_mem_used=890092194]
CoalesceBatchesExec: target_batch_size=20000, metrics=[output_rows=492435, elapsed_compute=67.421µs]
FilterExec: TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, metrics=[output_rows=492435, elapsed_compute=3.831311ms]
DeltaScan, metrics=[files_pruned=96, files_scanned=205]
DataSourceExec: file_groups={8 groups: [[..., ...]}, projection=[Timestamp, TsBucketStart, TraceId, SpanId, ParentSpanId, ServiceName, SpanName, SpanKind, ScopeName, Duration, StatusCode, Tags, MlRepoId, TracingProjectId], file_type=parquet, predicate=TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, pruning_predicate=TraceId_null_count@2 != row_count@3 AND TraceId_min@0 <= fb09d0c9b49136bb161464b3e32c5083 AND fb09d0c9b49136bb161464b3e32c5083 <= TraceId_max@1 AND ParentSpanId_null_count@6 != row_count@3 AND ParentSpanId_min@4 <= 662b7388122dfb79 AND 662b7388122dfb79 <= ParentSpanId_max@5 AND Timestamp_null_count@8 != row_count@3 AND Timestamp_max@7 >= 1744702200000000 AND TsBucketStart_null_count@10 != row_count@3 AND TsBucketStart_max@9 >= 1745712000, required_guarantees=[ParentSpanId in (662b7388122dfb79), TraceId in (fb09d0c9b49136bb161464b3e32c5083)], met
rics=[output_rows=492435, elapsed_compute=8ns, bytes_scanned=369897090, file_open_errors=0, file_scan_errors=0, num_predicate_creation_errors=0, page_index_rows_matched=5863737, page_index_rows_pruned=0, predicate_evaluation_errors=0, pushdown_rows_matched=12712344, pushdown_rows_pruned=5371302, row_groups_matched_bloom_filter=10, row_groups_matched_statistics=205, row_groups_pruned_bloom_filter=195, row_groups_pruned_statistics=0, bloom_filter_eval_time=11.551132ms, metadata_load_time=76.928924ms, page_index_eval_time=310.055µs, row_pushdown_eval_time=28.421911ms, statistics_eval_time=4.607552ms, time_elapsed_opening=24.111618ms, time_elapsed_processing=622.896471ms, time_elapsed_scanning_total=3.532807376s, time_elapsed_scanning_until_data=417.093675ms]
```
2. Distinct all columns. Approximately **500MS** of wall clock execution time. For my use case, this works as if `SpanId` repeats all the other columns selected will have the identical value.
```
SortPreservingMergeExec: [Timestamp@1 ASC NULLS LAST], fetch=10, metrics=[output_rows=10, elapsed_compute=501.954µs]
SortExec: TopK(fetch=10), expr=[Timestamp@1 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=80, elapsed_compute=12.752748ms, row_replacements=536]
ProjectionExec: expr=[SpanId@0 as SpanId, Timestamp@1 as Timestamp, TraceId@2 as TraceId, SpanName@3 as SpanName, ScopeName@4 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@6 as ParentSpanId, Duration@7 as Duration, Tags@8 as Tags, SpanKind@9 as SpanKind, MlRepoId@10 as MlRepoId, TracingProjectId@11 as TracingProjectId, StatusCode@12 as StatusCode, TsBucketStart@13 as TsBucketStart, ((ServiceName@5 = user-service OR ServiceName@5 = auth-service) AND SpanName@3 LIKE %login% AND Duration@7 >= 1000000 AND Duration@7 <= 5000000 AND StatusCode@12 >= 200 AND StatusCode@12 <= 299) IS NOT DISTINCT FROM true as matched], metrics=[output_rows=492435, elapsed_compute=1.631387ms]
AggregateExec: mode=FinalPartitioned, gby=[SpanId@0 as SpanId, Timestamp@1 as Timestamp, TraceId@2 as TraceId, SpanName@3 as SpanName, ScopeName@4 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@6 as ParentSpanId, Duration@7 as Duration, Tags@8 as Tags, SpanKind@9 as SpanKind, MlRepoId@10 as MlRepoId, TracingProjectId@11 as TracingProjectId, StatusCode@12 as StatusCode, TsBucketStart@13 as TsBucketStart], aggr=[], ordering_mode=PartiallySorted([2, 6]), metrics=[output_rows=492435, elapsed_compute=369.067634ms, spill_count=0, spilled_bytes=0, spilled_rows=0, peak_mem_used=237437570]
CoalesceBatchesExec: target_batch_size=20000, metrics=[output_rows=492435, elapsed_compute=385.467µs]
RepartitionExec: partitioning=Hash([SpanId@0, Timestamp@1, TraceId@2, SpanName@3, ScopeName@4, ServiceName@5, ParentSpanId@6, Duration@7, Tags@8, SpanKind@9, MlRepoId@10, TracingProjectId@11, StatusCode@12, TsBucketStart@13], 8), input_partitions=8, metrics=[fetch_time=1.06138683s, repartition_time=71.350859ms, send_time=24.35597ms]
AggregateExec: mode=Partial, gby=[SpanId@0 as SpanId, Timestamp@1 as Timestamp, TraceId@2 as TraceId, SpanName@3 as SpanName, ScopeName@4 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@6 as ParentSpanId, Duration@7 as Duration, Tags@8 as Tags, SpanKind@9 as SpanKind, MlRepoId@10 as MlRepoId, TracingProjectId@11 as TracingProjectId, StatusCode@12 as StatusCode, TsBucketStart@13 as TsBucketStart], aggr=[], ordering_mode=PartiallySorted([2, 6]), metrics=[output_rows=492435, elapsed_compute=409.064966ms, spill_count=0, spilled_bytes=0, spilled_rows=0, peak_mem_used=206203683]
ProjectionExec: expr=[SpanId@3 as SpanId, Timestamp@0 as Timestamp, TraceId@2 as TraceId, SpanName@6 as SpanName, ScopeName@8 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@4 as ParentSpanId, Duration@9 as Duration, Tags@11 as Tags, SpanKind@7 as SpanKind, MlRepoId@12 as MlRepoId, CAST(TracingProjectId@13 AS Utf8) as TracingProjectId, StatusCode@10 as StatusCode, TsBucketStart@1 as TsBucketStart], metrics=[output_rows=492435, elapsed_compute=4.455516ms]
CoalesceBatchesExec: target_batch_size=20000, metrics=[output_rows=492435, elapsed_compute=57µs]
FilterExec: TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, metrics=[output_rows=492435, elapsed_compute=3.792731ms]
DeltaScan, metrics=[files_pruned=96, files_scanned=205]
DataSourceExec: file_groups={8 groups: [[ ...], ...]}, projection=[Timestamp, TsBucketStart, TraceId, SpanId, ParentSpanId, ServiceName, SpanName, SpanKind, ScopeName, Duration, StatusCode, Tags, MlRepoId, TracingProjectId], file_type=parquet, predicate=TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, pruning_predicate=TraceId_null_count@2 != row_count@3 AND TraceId_min@0 <= fb09d0c9b49136bb161464b3e32c5083 AND fb09d0c9b49136bb161464b3e32c5083 <= TraceId_max@1 AND ParentSpanId_null_count@6 != row_count@3 AND ParentSpanId_min@4 <= 662b7388122dfb79 AND 662b7388122dfb79 <= ParentSpanId_max@5 AND Timestamp_null_count@8 != row_count@3 AND Timestamp_max@7 >= 1744702200000000 AND TsBucketStart_null_count@10 != row_count@3 AND TsBucketStart_max@9 >= 1745712000, required_guarantees=[ParentSpanId in (662b7388122dfb79), TraceId in (fb09d0c9b49136bb161464b3e32c5083)],
metrics=[output_rows=492435, elapsed_compute=8ns, bytes_scanned=369885282, file_open_errors=0, file_scan_errors=0, num_predicate_creation_errors=0, page_index_rows_matched=5863737, page_index_rows_pruned=0, predicate_evaluation_errors=0, pushdown_rows_matched=12712344, pushdown_rows_pruned=5371302, row_groups_matched_bloom_filter=10, row_groups_matched_statistics=205, row_groups_pruned_bloom_filter=195, row_groups_pruned_statistics=0, bloom_filter_eval_time=11.322584ms, metadata_load_time=83.355586ms, page_index_eval_time=336.416µs, row_pushdown_eval_time=28.880893ms, statistics_eval_time=4.498338ms, time_elapsed_opening=30.859506ms, time_elapsed_processing=629.285022ms, time_elapsed_scanning_total=785.298422ms, time_elapsed_scanning_until_data=417.603661ms]
```
3. No Distinct. Approximately **300MS** of wall clock execution time.
```
SortPreservingMergeExec: [Timestamp@1 ASC NULLS LAST], fetch=10, metrics=[output_rows=10, elapsed_compute=21.35µs]
SortExec: TopK(fetch=10), expr=[Timestamp@1 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=30, elapsed_compute=11.579758ms, row_replacements=161]
ProjectionExec: expr=[SpanId@3 as SpanId, Timestamp@0 as Timestamp, TraceId@2 as TraceId, SpanName@6 as SpanName, ScopeName@8 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@4 as ParentSpanId, Duration@9 as Duration, Tags@11 as Tags, SpanKind@7 as SpanKind, MlRepoId@12 as MlRepoId, CAST(TracingProjectId@13 AS Utf8) as TracingProjectId, StatusCode@10 as StatusCode, TsBucketStart@1 as TsBucketStart, ((ServiceName@5 = user-service OR ServiceName@5 = auth-service) AND SpanName@6 LIKE %login% AND Duration@9 >= 1000000 AND Duration@9 <= 5000000 AND StatusCode@10 >= 200 AND StatusCode@10 <= 299) IS NOT DISTINCT FROM true as matched], metrics=[output_rows=492435, elapsed_compute=5.709997ms]
CoalesceBatchesExec: target_batch_size=20000, metrics=[output_rows=492435, elapsed_compute=46.641µs]
FilterExec: TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, metrics=[output_rows=492435, elapsed_compute=3.539565ms]
DeltaScan, metrics=[files_pruned=96, files_scanned=205]
DataSourceExec: file_groups={8 groups: [[...], ...]}, projection=[Timestamp, TsBucketStart, TraceId, SpanId, ParentSpanId, ServiceName, SpanName, SpanKind, ScopeName, Duration, StatusCode, Tags, MlRepoId, TracingProjectId], file_type=parquet, predicate=TraceId@2 = fb09d0c9b49136bb161464b3e32c5083 AND ParentSpanId@4 = 662b7388122dfb79 AND Timestamp@0 >= 1744702200000000 AND TsBucketStart@1 >= 1745712000, pruning_predicate=TraceId_null_count@2 != row_count@3 AND TraceId_min@0 <= fb09d0c9b49136bb161464b3e32c5083 AND fb09d0c9b49136bb161464b3e32c5083 <= TraceId_max@1 AND ParentSpanId_null_count@6 != row_count@3 AND ParentSpanId_min@4 <= 662b7388122dfb79 AND 662b7388122dfb79 <= ParentSpanId_max@5 AND Timestamp_null_count@8 != row_count@3 AND Timestamp_max@7 >= 1744702200000000 AND TsBucketStart_null_count@10 != row_count@3 AND TsBucketStart_max@9 >= 1745712000, required_guarantees=[ParentSpanId in (662b7388122dfb79), TraceId in (fb09d0c9b49136bb161464b3e32c5083)], metrics=[o
utput_rows=492435, elapsed_compute=8ns, bytes_scanned=369891545, file_open_errors=0, file_scan_errors=0, num_predicate_creation_errors=0, page_index_rows_matched=5863737, page_index_rows_pruned=0, predicate_evaluation_errors=0, pushdown_rows_matched=12712344, pushdown_rows_pruned=5371302, row_groups_matched_bloom_filter=10, row_groups_matched_statistics=205, row_groups_pruned_bloom_filter=195, row_groups_pruned_statistics=0, bloom_filter_eval_time=11.370524ms, metadata_load_time=61.64128ms, page_index_eval_time=332.784µs, row_pushdown_eval_time=28.634729ms, statistics_eval_time=4.530266ms, time_elapsed_opening=7.874004ms, time_elapsed_processing=610.976289ms, time_elapsed_scanning_total=628.786969ms, time_elapsed_scanning_until_data=427.639613ms]
```
I would like to understand what I can do to make `distinct on (columns)` faster.
### To Reproduce
_No response_
### Expected behavior
_No response_
### Additional context
_No response_
--
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3188307166
I think the dynamic filter pushdown that @adriangb added in 49 should help this query a lot (as it should be able to push the filter from the topk down into the scan)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "debajyoti-truefoundry (via GitHub)" <gi...@apache.org>.
debajyoti-truefoundry commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3354723617
Hey, apologies for the late reply. We actually ended up going from `distinct on ("SpanId")` -> `Distinct all column`.
I have read through https://datafusion.apache.org/blog/2025/09/10/dynamic-filters/ and I understand how the dynamic pushdown will help here due to the limit and order by timestamp.
I opened this issue for the performance difference (check `elapsed_compute`) between,
```
AggregateExec: mode=FinalPartitioned, gby=[SpanId@0 as SpanId], aggr=[first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Timestamp), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TraceId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ScopeName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ServiceName), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.ParentSpanId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Duration), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.Tags), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.SpanKind), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.MlRepoId), first_value(TracingProjectId), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.StatusCode), first_value(tbl_6baa3c6fd52949c18602a6b38294e8d9.TsBucketStart)], metrics=[output_rows=492435, elapsed_compute=3.882494179s, spill_count=0, spilled_bytes=0, spilled_rows=
0, peak_mem_used=1414466147]
```
and
```
```
AggregateExec: mode=FinalPartitioned, gby=[SpanId@0 as SpanId, Timestamp@1 as Timestamp, TraceId@2 as TraceId, SpanName@3 as SpanName, ScopeName@4 as ScopeName, ServiceName@5 as ServiceName, ParentSpanId@6 as ParentSpanId, Duration@7 as Duration, Tags@8 as Tags, SpanKind@9 as SpanKind, MlRepoId@10 as MlRepoId, TracingProjectId@11 as TracingProjectId, StatusCode@12 as StatusCode, TsBucketStart@13 as TsBucketStart], aggr=[], ordering_mode=PartiallySorted([2, 6]), metrics=[output_rows=492435, elapsed_compute=369.067634ms, spill_count=0, spilled_bytes=0, spilled_rows=0, peak_mem_used=237437570]
```
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3266408098
@debajyoti-truefoundry could you re-run this against `main` (which has several optimizations related to dynamic filter pushdown)?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "debajyoti-truefoundry (via GitHub)" <gi...@apache.org>.
debajyoti-truefoundry commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3375880735
```rust
use arrow::array::{ArrayRef, StringArray};
use arrow::datatypes::{DataType, Field, Schema};
use arrow::record_batch::RecordBatch;
use datafusion::prelude::*;
use parquet::arrow::ArrowWriter;
use parquet::file::properties::WriterProperties;
use rand::Rng;
use std::fs::File;
use std::sync::Arc;
fn generate_random_8byte_hex() -> String {
let mut rng = rand::thread_rng();
let mut bytes = [0u8; 8];
rng.fill(&mut bytes);
hex::encode(bytes)
}
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
let schema = Arc::new(Schema::new(vec![
Field::new("span_id", DataType::Utf8, false),
Field::new("data", DataType::Utf8, false),
]));
let temp_file = tempfile::Builder::new()
.suffix(".parquet")
.tempfile()?;
let temp_path = temp_file.path().to_string_lossy().to_string();
let file = File::create(&temp_path)?;
let props = WriterProperties::builder().build();
let mut writer = ArrowWriter::try_new(file, schema.clone(), Some(props))?;
let batch_size = 10_000;
let total_rows = 1_000_000;
let num_batches = total_rows / batch_size;
for _batch_idx in 0..num_batches {
let mut span_ids = Vec::with_capacity(batch_size);
let mut data_values = Vec::with_capacity(batch_size);
for _ in 0..batch_size {
span_ids.push(generate_random_8byte_hex());
data_values.push(generate_random_8byte_hex());
}
let span_id_array: ArrayRef = Arc::new(StringArray::from(span_ids));
let data_array: ArrayRef = Arc::new(StringArray::from(data_values));
let batch = RecordBatch::try_new(
schema.clone(),
vec![span_id_array, data_array],
)?;
writer.write(&batch)?;
}
writer.close()?;
let ctx = SessionContext::new();
ctx.register_parquet("spans", &temp_path, ParquetReadOptions::default())
.await?;
let sql = "explain analyze SELECT DISTINCT ON (span_id) span_id, data FROM spans ORDER BY span_id LIMIT 10";
let df = ctx.sql(sql).await?;
let results = df.collect().await?;
for batch in results {
println!("{}", arrow::util::pretty::pretty_format_batches(&[batch])?);
}
let sql2 = "explain analyze SELECT DISTINCT span_id, data FROM spans ORDER BY span_id LIMIT 10";
let df2 = ctx.sql(sql2).await?;
let results2 = df2.collect().await?;
for batch in results2 {
println!("{}", arrow::util::pretty::pretty_format_batches(&[batch])?);
}
Ok(())
}
```
```
| AggregateExec: mode=FinalPartitioned, gby=[span_id@0 as span_id, data@1 as data], aggr=[], metrics=[output_rows=1000000, elapsed_compute=74.551957ms, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=115344288]
```
vs
```
AggregateExec: mode=FinalPartitioned, gby=[span_id@0 as span_id], aggr=[first_value(spans.span_id) ORDER BY [spans.span_id ASC NULLS LAST], first_value(spans.data) ORDER BY [spans.span_id ASC NULLS LAST]], metrics=[output_rows=1000000, elapsed_compute=28.630642585s, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=1170265952]
```
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "UBarney (via GitHub)" <gi...@apache.org>.
UBarney commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-4003028557
> use arrow::array::{ArrayRef, StringArray};
> use arrow::datatypes::{DataType, Field, Schema};
> use arrow::record_batch::RecordBatch;
> use datafusion::prelude::*;
> use parquet::arrow::ArrowWriter;
> use parquet::file::properties::WriterProperties;
> use rand::Rng;
> use std::fs::File;
> use std::sync::Arc;
>
> fn generate_random_8byte_hex() -> String {
> let mut rng = rand::rng();
> let mut bytes = [0u8; 8];
> rng.fill(&mut bytes);
> hex::encode(bytes)
> }
>
> #[tokio::main]
> async fn main() -> Result<(), Box<dyn std::error::Error>> {
> let schema = Arc::new(Schema::new(vec![
> Field::new("span_id", DataType::Utf8, false),
> Field::new("data", DataType::Utf8, false),
> ]));
>
> let temp_file = tempfile::Builder::new().suffix(".parquet").tempfile()?;
> let temp_path = temp_file.path().to_string_lossy().to_string();
>
> let file = File::create(&temp_path)?;
> let props = WriterProperties::builder().build();
> let mut writer = ArrowWriter::try_new(file, schema.clone(), Some(props))?;
>
> let batch_size = 10_000;
> let total_rows = 1_000_000;
> let num_batches = total_rows / batch_size;
>
> for _batch_idx in 0..num_batches {
> let mut span_ids = Vec::with_capacity(batch_size);
> let mut data_values = Vec::with_capacity(batch_size);
>
> for _ in 0..batch_size {
> span_ids.push(generate_random_8byte_hex());
> data_values.push(generate_random_8byte_hex());
> }
>
> let span_id_array: ArrayRef = Arc::new(StringArray::from(span_ids));
> let data_array: ArrayRef = Arc::new(StringArray::from(data_values));
>
> let batch = RecordBatch::try_new(schema.clone(), vec![span_id_array, data_array])?;
>
> writer.write(&batch)?;
> }
>
> writer.close()?;
>
> let ctx = SessionContext::new();
>
> ctx.register_parquet("spans", &temp_path, ParquetReadOptions::default())
> .await?;
>
> let sql = "explain analyze SELECT DISTINCT ON (span_id) span_id, data FROM spans ORDER BY span_id LIMIT 10";
>
> let df = ctx.sql(sql).await?;
> let results = df.collect().await?;
>
> for batch in results {
> println!("{}", arrow::util::pretty::pretty_format_batches(&[batch])?);
> }
>
> let sql2 = "explain analyze SELECT DISTINCT span_id, data FROM spans ORDER BY span_id LIMIT 10";
>
> let df2 = ctx.sql(sql2).await?;
> let results2 = df2.collect().await?;
>
> for batch in results2 {
> println!("{}", arrow::util::pretty::pretty_format_batches(&[batch])?);
> }
>
> Ok(())
> }
> ```
> | AggregateExec: mode=FinalPartitioned, gby=[span_id@0 as span_id, data@1 as data], aggr=[], metrics=[output_rows=1000000, elapsed_compute=74.551957ms, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=115344288]
> ```
>
> vs
>
> ```
> AggregateExec: mode=FinalPartitioned, gby=[span_id@0 as span_id], aggr=[first_value(spans.span_id) ORDER BY [spans.span_id ASC NULLS LAST], first_value(spans.data) ORDER BY [spans.span_id ASC NULLS LAST]], metrics=[output_rows=1000000, elapsed_compute=28.630642585s, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=1170265952]
> ```
It seems like the SQL plan generated in this code has some differences compared to the plan described in the issue:
* In the issue description, `FIRST_VALUE` does not have an `ORDER BY` clause, but in this code, it does.
Additionally, based on the query plan corresponding to this code, we could further optimize the `FIRST_VALUE` parameters:
* **When the argument of `FIRST_VALUE` is only the GROUP BY key:** It can be simplified to just the group by key itself.
Example: `FIRST_VALUE(span_id ORDER BY span_id) GROUP BY span_id` → `span_id GROUP BY span_id`
* **When the `ORDER BY` clause contains the GROUP BY key:** Those fields can be removed from the `ORDER BY` clause.
Example: `FIRST_VALUE(data ORDER BY span_id) AS data FROM spans GROUP BY span_id` → `FIRST_VALUE(data) AS data FROM spans GROUP BY span_id`
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3365476837
The obvious difference between the plan from what I can see is the inclusion of `first_value` in the `DISTINCT ON` query
So I think the short answer is "it is likely we could improve the performance fo distinct on columns, by improvung the performance of the `first_value` aggregate"
I took a brief look at the implementation: https://github.com/apache/datafusion/blob/main/datafusion/functions-aggregate/src/nth_value.rs#L93
And it does not seem to have a `GroupsAccumulator` (background in [Aggregating Millions of Groups Fast in Apache Arrow DataFusion](https://arrow.apache.org/blog/2023/08/05/datafusion_fast_grouping/) )
There is a similar issue for optimizing `array_agg` here:
- https://github.com/apache/datafusion/issues/10145
I will file a corresponding ticket for implement GroupsAccumulator for `first_value` / `nth value`
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3365481120
@debajyoti-truefoundry -- could you possible provide a self-contained reproducer (e.g. the data you used for the queries above, or some syntehtic version that has the same properties / shows the same behavior?
That would allow us to verify that the evaluation of first_value was consuming a non trivial amount of the time
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Performance of `distinct on (columns)` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #16620:
URL: https://github.com/apache/datafusion/issues/16620#issuecomment-3365500499
Filed
- https://github.com/apache/datafusion/issues/17899
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org