search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.49k stars 3.02k forks source link

[Bug] Invalid Adaptive Reordering of Partitioned Join for tpch q21 #23626

Open gaurav8297 opened 1 month ago

gaurav8297 commented 1 month ago

For tpch q21:

with adaptive planning:

Query 20240930_053638_00003_qzvum, FINISHED, 9 nodes
Splits: 69,182 total, 69,182 done (100.00%)
12:42 [188B rows, 1.25TB] [247M rows/s, 1.67GB/s]

without adaptive planning:

Query 20240930_052705_00000_qzvum, FAILED, 8 nodes
Splits: 51,400 total, 51,400 done (NaN%)
6:34 [188B rows, 1.25TB] [477M rows/s, 3.24GB/s]
gaurav8297 commented 1 month ago

Some logs: First call to adaptive planning

2024-09-30T06:57:02.531Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin   Left output size: 6.077524869566522E11, Right output size: 6.2090160966943E11
2024-09-30T06:57:02.532Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin  Left output size: 1.079999896806E12, Right output size: 1.5478757817764893E11
2024-09-30T06:57:02.532Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin  Left output size: 1.377226946675475E11, Right output size: 3.684888418233333E10
2024-09-30T06:57:02.533Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin   Left output size: 8.508534817393131E11, Right output size: 3.7721968276E8

Second call to adaptive planning

2024-09-30T07:01:56.096Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin  Left output size: 7.5582519022E11, Right output size: 1.477261493858036E12
2024-09-30T07:01:56.098Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin  Left output size: 1.477261493858036E12, Right output size: 7.5582519022E11
2024-09-30T07:01:56.099Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin  Left output size: 1.079999896806E12, Right output size: 3.682736933008136E11
2024-09-30T07:01:56.099Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin   Left output size: 1.1096970582459525E11, Right output size: 1.0259863440025479E11
2024-09-30T07:01:56.099Z    WARN    query-execution-0   io.trino.sql.planner.iterative.rule.AdaptiveReorderPartitionedJoin   Left output size: 1.062141519096E12, Right output size: 2.16553024E8

For some reason right side stats changed significantly for the first join.

losipiuk commented 1 month ago

@gaurav8297 do you recall what size of cluster and what schema did you do the tests on. So far I cannot reproduce that.

losipiuk commented 1 month ago

Ok - I managed to reproduce