Join reads full table when I am only interested in a subset #23442

HaraldVanWoerkom · 2024-09-16T09:08:54Z

HaraldVanWoerkom
Sep 16, 2024

I have a large table (150M rows) that contains a column with an array of data (20KB of data). The total is 3TB of data. I am using pyiceberg tables.
I want to read a subset of this table, based on the results of a query on a different table:

SELECT * FROM huge_table ht INNER JOIN query_table qt ON ht.key = qt.key WHERE qt.something = 'X'

The problem is that the query plan for this results in a list of keys that are then joined with the huge table. This causes Trino to load the entire huge_table (3TB) and then filter out the unwanted data, which is rather slow.
If I manually split the queries:

SELECT key FROM query_table qt WHERE qt.something = 'X'
SELECT * FROM huge_table ht WHERE ht.key in ()

the read only reads the part of the table I want, a much faster operation ( is pushed down to the ScanFilter operation). However, the eventually should support around 1M keys, so this does not seem to be a proper solution.

I have tried rewriting the query in several ways, but Trino always resolves it to a join (which is fair enough, the operation is essentially a join). But is there a way to convince Trino that I really want the filter keys to be pushed down to the read operation (ScanFilter in the execution plan)?

Praveen2112 · 2024-09-16T10:38:02Z

Praveen2112
Sep 16, 2024
Collaborator

Trino does have a dynamic filter operation which tends to pass the unique values of query_table#key to the huge_table and uses them to filter the rows which needs to be selected - It again depends on how big query_table. A query plan iwth DF would be helpful to issue. There was a proposal like #684 which would allow us to retain the data from a sub query and use them to a parent query

4 replies

HaraldVanWoerkom Sep 16, 2024
Author

This is the query plan (I've redacted it a bit to protect the actual tables):

 Trino version: 455
 Fragment 0 [HASH]
     Output layout: [key, something]
     Output partitioning: SINGLE []
     Output[columnNames = [key, something, data]
        Layout: [key:varchar, something:varchar, data:array(real)]
        Estimates: {rows: 89708 (160.59MB), cpu: 0, memory: 0B, network: 0B}
        data := data
      InnerJoin[criteria = (key = key), distribution = PARTITIONED]
           Layout: [something:varchar, data:array(real)]
           Estimates: {rows: 89708 (160.59MB), cpu: 35.91G, memory: 149.90MB, network: 0B}
           Distribution: PARTITIONED
         RemoteSource[sourceFragmentIds = [1]]
              Layout: [key:varchar, something:varchar, data:array(real)]
         LocalExchange[partitioning = HASH, arguments = [key::varchar]]
              Layout: [key:varchar, something:varchar]
              Estimates: {rows: 89290 (149.90MB), cpu: 149.90M, memory: 0B, network: 0B}
            RemoteSource[sourceFragmentIds = [2]]
                  Layout: [key:varchar, something:varchar]

 Fragment 1 [SOURCE]
     Output layout: [key, something, data]
     Output partitioning: HASH [key]
     ScanFilter[table = huge_table$data@2486599918348774888, dynamicFilters = {key = #df_349}]
         Layout: [key:varchar, something:varchar, data:array(real)]
         Estimates: {rows: 167329754 (35.61GB), cpu: 35.61G, memory: 0B, network: 0B}/{rows: 167329754 (35.61GB), cpu: 35.61G, memory: 0B, network: 0B}
         key_0 := 1:key:varchar
         data := 8:data:array(real)
         something := 5:something:varchar

 Fragment 2 [SOURCE]
     Output layout: [key, something]
     Output partitioning: HASH [key]
     DynamicFilterSource[dynamicFilterAssignments = {key -> #df_349}]
        Layout: [key:varchar, something:varchar]
        Estimates: {rows: 89290 (149.90MB), cpu: ?, memory: ?, network: ?}
      ScanFilter[table = query_table$data@2235185504460674640, filterPredicate = (something = varchar 'X')]
            Layout: [key:varchar, something:varchar]
            Estimates: {rows: 167329754 (274.33GB), cpu: 274.33G, memory: 0B, network: 0B}/{rows: 89290 (149.90MB), cpu: 274.33G, memory: 0B, network: 0B}

raunaqmorarka Sep 16, 2024
Collaborator

Can you provide the output of EXPLAIN ANALYZE VERBOSE in the above case ?

HaraldVanWoerkom Sep 17, 2024
Author

I found out a bit more: the size of the query_table influences how the huge_table is read, which is something I did not expect.
I've added two analyses. The first one reads the entire huge_table, the second one does not. The only difference is the query_table.
In the first (slow) query, query_table contains 135 columns.
In the second query, query_table contains only 109 columns (the columns that are dropped happen to be integer and double columns, nothing special).

Slow query:

trino:test> explain analyze verbose select * from huge_table ht inner join query_table6 qt on ht.key = qt.key where something='FQ6RU285SEF4';
                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Trino version: 455                                                                                                                                                                                                                                                                                                                                                                                                  
 Queued: 680.89us, Analysis: 18.72ms, Planning: 169.57ms, Execution: 1.91m                                                                                                                                                                                                                                                                                                                                           
 Fragment 1 [HASH]                                                                                                                                                                                                                                                                                                                                                                                                   
     CPU: 7.50m, Scheduled: 11.10m, Blocked 1.64h (Input: 43.49m, Output: 0.00ns), Input: 48684912 rows (365.88GB); per task: avg.: 2704717.33 std.dev.: 1213106.37, Output: 15708 rows (148.34MB)                                                                                                                                                                                                                   
     Task output distribution: {count=18.00, p01=2.69MB, p05=2.69MB, p10=2.74MB, p25=2.84MB, p50=3.01MB, p75=3.16MB, p90=3.38MB, p95=97.30MB, p99=97.30MB, max=97.30MB}                                                                                                                                                                                                                                              
     Task input distribution: {count=18.00, p01=97.66MB, p05=97.66MB, p10=796.95MB, p25=24.99GB, p50=25.15GB, p75=25.16GB, p90=25.17GB, p95=25.18GB, p99=25.18GB, max=25.18GB}                                                                                                                                                                                                                                       
     Output layout: [key, data, something, <133 more columns>
     Output partitioning: SINGLE []                                                                                                                                                                                                                                                                                                                                                                                  
     InnerJoin[criteria = (key = key_0), distribution = PARTITIONED]                                                                                                                                                                                                                                                                                                                                                 
     <E2><94><82>   Layout: [key:varchar, data:array(real), something:varchar, <133 more columns>]
     <E2><94><82>   Reorder joins cost : {rows: 89708 (118.95MB), cpu: 468.32G, memory: 113.70MB, network: 17.35GB}                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   Estimates: {rows: 89708 (118.95MB), cpu: 17.47G, memory: 113.70MB, network: 0B}                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   CPU: 2.16m (2.06%), Scheduled: 2.20m (0.11%), Blocked: 54.22m (55.42%), Output: 15708 rows (148.34MB)                                                                                                                                                                                                                                                                                            
     <E2><94><82>   Left (probe) metrics:                                                                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>     'CPU time distribution (s)' = {count=144, p01=0.02, p05=0.02, p10=0.33, p25=0.60, p50=0.92, p75=1.15, p90=1.51, p95=1.79, p99=1.83, min=0.02, max=1.92}                                                                                                                                                                                                                                        
     <E2><94><82>     'Input rows distribution' = {count=144, p01=1181.00, p05=1450.00, p10=13637.00, p25=398407.50, p50=412006.50, p75=421853.00, p90=429836.00, p95=440010.00, p99=454824.00, min=1115.00, max=459673.00}                                                                                                                                                                                          
     <E2><94><82>     'Scheduled time distribution (s)' = {count=144, p01=0.03, p05=0.03, p10=0.33, p25=0.61, p50=0.92, p75=1.18, p90=1.58, p95=1.80, p99=1.84, min=0.02, max=1.96}                                                                                                                                                                                                                                  
     <E2><94><82>   Right (build) metrics:                                                                                                                                                                                                                                                                                                                                                                           
     <E2><94><82>     'Blocked time distribution (s)' = {count=144, p01=0.20, p05=0.20, p10=2.31, p25=24.43, p50=27.30, p75=29.03, p90=30.01, p95=30.36, p99=30.36, min=0.00, max=30.36}                                                                                                                                                                                                                             
     <E2><94><82>     'CPU time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.03, p99=0.03, min=0.00, max=0.03}                                                                                                                                                                                                                                        
     <E2><94><82>     'Input rows distribution' = {count=144, p01=27.00, p05=29.00, p10=30.00, p25=35.13, p50=40.82, p75=44.30, p90=50.00, p95=1242.00, p99=1308.00, min=26.00, max=1346.00}                                                                                                                                                                                                                         
     <E2><94><82>     'Scheduled time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.03, p99=0.03, min=0.00, max=0.03}                                                                                                                                                                                                                                  
     <E2><94><82>   Left (probe) Input avg.: 337980.58 rows, Input std.dev.: 45.03%                                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   Right (build) Input avg.: 109.08 rows, Input std.dev.: 262.21%                                                                                                                                                                                                                                                                                                                                   
     <E2><94><82>   Distribution: PARTITIONED                                                                                                                                                                                                                                                                                                                                                                        
     <E2><94><9C><E2><94><80> RemoteSource[sourceFragmentIds = [2]]                                                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>      Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>      CPU: 5.33m (5.09%), Scheduled: 8.88m (0.44%), Blocked: 43.40m (44.37%), Output: 48669204 rows (365.86GB)                                                                                                                                                                                                                                                                                      
     <E2><94><82>      metrics:                                                                                                                                                                                                                                                                                                                                                                                      
     <E2><94><82>        'Blocked time distribution (s)' = {count=144, p01=0.17, p05=0.17, p10=0.92, p25=20.60, p50=21.52, p75=23.43, p90=24.37, p95=24.49, p99=24.50, min=0.17, max=24.50}                                                                                                                                                                                                                          
     <E2><94><82>        'CPU time distribution (s)' = {count=144, p01=0.02, p05=0.03, p10=0.76, p25=1.65, p50=2.41, p75=2.90, p90=3.27, p95=3.41, p99=3.45, min=0.02, max=3.50}                                                                                                                                                                                                                                     
     <E2><94><82>        'Input rows distribution' = {count=144, p01=1181.00, p05=1450.00, p10=13637.00, p25=398407.50, p50=412006.50, p75=421853.00, p90=429836.00, p95=440010.00, p99=454824.00, min=1115.00, max=459673.00}                                                                                                                                                                                       
     <E2><94><82>        'Scheduled time distribution (s)' = {count=144, p01=0.03, p05=0.04, p10=0.82, p25=3.24, p50=4.24, p75=4.69, p90=5.10, p95=5.62, p99=5.67, min=0.03, max=5.91}                                                                                                                                                                                                                               
     <E2><94><82>      Input avg.: 337980.58 rows, Input std.dev.: 45.03%                                                                                                                                                                                                                                                                                                                                            
     <E2><94><94><E2><94><80> LocalExchange[partitioning = HASH, arguments = [key_0::varchar]]                                                                                                                                                                                                                                                                                                                       
        <E2><94><82>   Layout: [key_0:varchar, something:varchar, <133 more columns>]
        <E2><94><82>   Estimates: {rows: 89290 (113.70MB), cpu: 113.70M, memory: 0B, network: 0B}                                                                                                                                                                                                                                                                                                                    
        <E2><94><82>   CPU: 79.00ms (0.00%), Scheduled: 85.00ms (0.00%), Blocked: 6.56s (0.11%), Output: 15708 rows (26.53MB)                                                                                                                                                                                                                                                                                        
        <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                                      
        <E2><94><82>     'CPU time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                                     
        <E2><94><82>     'Input rows distribution' = {count=144, p01=0.00, p05=21.00, p10=25.00, p25=31.22, p50=39.89, p75=48.30, p90=59.00, p95=1026.00, p99=1360.00, min=0.00, max=1376.00}                                                                                                                                                                                                                        
        <E2><94><82>     'Scheduled time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                               
        <E2><94><82>   Input avg.: 109.08 rows, Input std.dev.: 263.34%                                                                                                                                                                                                                                                                                                                                              
        <E2><94><94><E2><94><80> RemoteSource[sourceFragmentIds = [3]]                                                                                                                                                                                                                                                                                                                                               
               Layout: [key_0:varchar, something:varchar, <133 more columns>]
               CPU: 255.00ms (0.00%), Scheduled: 510.00ms (0.00%), Blocked: 6.12s (0.10%), Output: 15708 rows (26.51MB)                                                                                                                                                                                                                                                                                              
               metrics:                                                                                                                                                                                                                                                                                                                                                                                              
                 'Blocked time distribution (s)' = {count=144, p01=0.03, p05=0.03, p10=0.03, p25=0.04, p50=0.04, p75=0.04, p90=0.04, p95=0.11, p99=0.11, min=0.03, max=0.11}                                                                                                                                                                                                                                         
                 'CPU time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.02, p99=0.02, min=0.00, max=0.03}                                                                                                                                                                                                                                             
                 'Input rows distribution' = {count=144, p01=0.00, p05=21.00, p10=25.00, p25=31.22, p50=39.89, p75=48.30, p90=59.00, p95=1026.00, p99=1360.00, min=0.00, max=1376.00}                                                                                                                                                                                                                                
                 'Scheduled time distribution (s)' = {count=144, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.03, p99=0.04, min=0.00, max=0.04}                                                                                                                                                                                                                                       
               Input avg.: 109.08 rows, Input std.dev.: 263.34%                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                     
 Fragment 2 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                                                 
     CPU: 1.57h, Scheduled: 1.32d, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 167329754 rows (432.95GB); per task: avg.: 398404.18 std.dev.: 180410.85, Output: 48669204 rows (365.86GB)                                                                                                                                                                                                                 
     Task output distribution: {count=420.00, p01=0B, p05=0B, p10=0B, p25=162.53kB, p50=59.41MB, p75=1.38GB, p90=2.75GB, p95=2.79GB, p99=2.82GB, max=2.83GB}                                                                                                                                                                                                                                                         
     Task input distribution: {count=420.00, p01=12.54MB, p05=12.93MB, p10=19.33MB, p25=133.57MB, p50=981.59MB, p75=1.38GB, p90=2.75GB, p95=2.79GB, p99=2.82GB, max=2.83GB}                                                                                                                                                                                                                                          
     Amount of input data processed by the workers for this stage might be skewed                                                                                                                                                                                                                                                                                                                                    
     Output layout: [key, data]                                                                                                                                                                                                                                                                                                                                                                                      
     Output partitioning: HASH [key]                                                                                                                                                                                                                                                                                                                                                                                 
     ScanFilter[table = iceberg:test.huge_table$data@2617120010605135979, dynamicFilters = {key = #df_393}]                                                                                                                                                                                                                                                                                                          
         Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                                                     
         Estimates: {rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}/{rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}                                                                                                                                                                                                                                                              
         CPU: 1.57h (90.20%), Scheduled: 1.32d (93.34%), Blocked: 0.00ns (0.00%), Output: 48669204 rows (365.86GB)                                                                                                                                                                                                                                                                                                   
         connector metrics:                                                                                                                                                                                                                                                                                                                                                                                          
           'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=948643070448}                                                                                                                                                                                                                                                                                                                                     
         metrics:                                                                                                                                                                                                                                                                                                                                                                                                    
           'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.01, p50=0.01, p75=0.37, p90=0.77, p95=1.03, p99=1.29, min=0.00, max=1.59}                                                                                                                                                                                                                                                 
           'Dynamic Filter CPU time' = {duration=26.57s}                                                                                                                                                                                                                                                                                                                                                             
           'Dynamic Filter input positions' = LongCount{total=167329754}                                                                                                                                                                                                                                                                                                                                             
           'Input rows distribution' = {count=12392, p01=323.44, p05=1540.42, p10=2957.56, p25=6337.36, p50=12853.04, p75=23042.79, p90=23693.07, p95=23764.11, p99=23892.95, min=60.00, max=24217.00}                                                                                                                                                                                                               
           'Projection CPU time' = {duration=396.36ms}                                                                                                                                                                                                                                                                                                                                                               
           'Scheduled time distribution (s)' = {count=12392, p01=0.05, p05=0.06, p10=0.08, p25=0.12, p50=0.41, p75=15.00, p90=30.38, p95=39.83, p99=50.17, min=0.02, max=77.10}                                                                                                                                                                                                                                      
         Input avg.: 13503.05 rows, Input std.dev.: 59.11%                                                                                                                                                                                                                                                                                                                                                           
         key := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                                                        
         data := 2:data:array(real)                                                                                                                                                                                                                                                                                                                                                                                  
         Input: 167329754 rows (432.95GB), Filtered: 70.91%, Physical input: 327.58GB, Physical input time: 1.15d                                                                                                                                                                                                                                                                                                             Dynamic filters:                                                                                                                                                                                                                                                                                                                                                                                            
             - df_393, [ SortedRangeSet[type=varchar, ranges=15708, {[0000307A1D21839898E5EC4EB741E25C], ..., [FFF6E9E4F34AC21A3F691F5DA3F0699E]}] ], collection time=41.62s                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                     
 Fragment 3 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                                                 
     CPU: 2.77m, Scheduled: 2.07h, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 166223343 rows (27.92GB); per task: avg.: 7915397.29 std.dev.: 645639.68, Output: 15708 rows (26.69MB)                                                                                                                                                                                                                     
     Task output distribution: {count=21.00, p01=904.92kB, p05=1.02MB, p10=1.07MB, p25=1.19MB, p50=1.32MB, p75=1.37MB, p90=1.42MB, p95=1.55MB, p99=1.58MB, max=1.58MB}                                                                                                                                                                                                                                               
     Task input distribution: {count=21.00, p01=919.61MB, p05=1.15GB, p10=1.24GB, p25=1.30GB, p50=1.32GB, p75=1.38GB, p90=1.49GB, p95=1.52GB, p99=1.53GB, max=1.53GB}                                                                                                                                                                                                                                                
     Output layout: [key_0, something, <133 more columns>]
     Output partitioning: HASH [key_0]                                                                                                                                                                                                                                                                                                                                                                               
     DynamicFilterSource[dynamicFilterAssignments = {key_0 -> #df_393}]                                                                                                                                                                                                                                                                                                                                              
     <E2><94><82>   Layout: [key_0:varchar, something:varchar, <133 more columns>]
     <E2><94><82>   Estimates: {rows: 89290 (113.70MB), cpu: ?, memory: ?, network: ?}                                                                                                                                                                                                                                                                                                                               
     <E2><94><82>   CPU: 2.13s (0.03%), Scheduled: 2.81s (0.00%), Blocked: 0.00ns (0.00%), Output: 15708 rows (26.69MB)                                                                                                                                                                                                                                                                                              
     <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                                         
     <E2><94><82>     'CPU time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.01, p95=0.01, p99=0.02, min=0.00, max=0.03}                                                                                                                                                                                                                                        
     <E2><94><82>     'Input rows distribution' = {count=640, p01=0.00, p05=2.89, p10=7.06, p25=14.64, p50=23.51, p75=33.51, p90=42.10, p95=49.11, p99=61.00, min=0.00, max=78.00}                                                                                                                                                                                                                                   
     <E2><94><82>     'Scheduled time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.01, p95=0.01, p99=0.02, min=0.00, max=0.19}                                                                                                                                                                                                                                  
     <E2><94><82>   Input avg.: 24.54 rows, Input std.dev.: 56.78%                                                                                                                                                                                                                                                                                                                                                   
     <E2><94><94><E2><94><80> ScanFilter[table = iceberg:test.query_table6$data@2728742793164734417, filterPredicate = (something = varchar 'FQ6RU285SEF4')]                                                                                                                                                                                                                                                         
            Layout: [key_0:varchar, something:varchar, <133 more columns>]
            Estimates: {rows: 167329754 (208.08GB), cpu: 208.08G, memory: 0B, network: 0B}/{rows: 89290 (113.70MB), cpu: 208.08G, memory: 0B, network: 0B}                                                                                                                                                                                                                                                           
            CPU: 2.74m (2.61%), Scheduled: 2.07h (6.11%), Blocked: 0.00ns (0.00%), Output: 15708 rows (26.48MB)                                                                                                                                                                                                                                                                                                      
            connector metrics:                                                                                                                                                                                                                                                                                                                                                                                       
              'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=21407887457}                                                                                                                                                                                                                                                                                                                                   
            metrics:                                                                                                                                                                                                                                                                                                                                                                                                 
              'CPU time distribution (s)' = {count=640, p01=0.00, p05=0.05, p10=0.12, p25=0.19, p50=0.25, p75=0.32, p90=0.39, p95=0.45, p99=0.56, min=0.00, max=0.72}                                                                                                                                                                                                                                                
              'Filter CPU time' = {duration=256.30ms}                                                                                                                                                                                                                                                                                                                                                                
              'Input rows distribution' = {count=640, p01=0.00, p05=41920.50, p10=197443.89, p25=225816.74, p50=256372.11, p75=303832.00, p90=365406.67, p95=411730.25, p99=523515.00, min=0.00, max=535961.00}                                                                                                                                                                                                      
              'Projection CPU time' = {duration=105.84ms}                                                                                                                                                                                                                                                                                                                                                            
              'Scheduled time distribution (s)' = {count=640, p01=0.07, p05=1.33, p10=6.60, p25=9.59, p50=11.67, p75=14.17, p90=17.32, p95=19.11, p99=23.41, min=0.03, max=28.11}                                                                                                                                                                                                                                    
            Input avg.: 259723.97 rows, Input std.dev.: 36.08%                                                                                                                                                                                                                                                                                                                                                       
            something := 2:something:varchar                                                                                                                                                                                                                                                                                                                                                                         
            key_0 := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                                                   
			<133 more columns>
            Input: 166223343 rows (27.92GB), Filtered: 99.99%, Physical input: 19.96GB, Physical input time: 2.02h                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                     
(1 row)

Query 20240917_083535_00032_wi368, FINISHED, 11 nodes
Splits: 13,473 total, 13,473 done (100.00%)
1:55 [334M rows, 348GB] [2.91M rows/s, 3.03GB/s]

Fast query

trino:test> explain analyze verbose select * from huge_table ht inner join query_table5 qt on ht.key = qt.key where something='FQ6RU285SEF4';
                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Trino version: 455                                                                                                                                                                                                                                                                                                                                                                                                  
 Queued: 630.76us, Analysis: 21.30ms, Planning: 128.57ms, Execution: 24.03s                                                                                                                                                                                                                                                                                                                                          
 Fragment 1 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                                                 
     CPU: 15.19m, Scheduled: 6.10h, Blocked 6.25h (Input: 3.39m, Output: 0.00ns), Input: 173927114 rows (98.20GB); per task: avg.: 414112.18 std.dev.: 180410.85, Output: 15708 rows (145.78MB)                                                                                                                                                                                                                      
     Task output distribution: {count=420.00, p01=0B, p05=0B, p10=0B, p25=0B, p50=884B, p75=636.19kB, p90=1.16MB, p95=1.46MB, p99=1.80MB, max=2.54MB}                                                                                                                                                                                                                                                                
     Task input distribution: {count=420.00, p01=29.79MB, p05=30.02MB, p10=30.25MB, p25=36.65MB, p50=46.62MB, p75=387.34MB, p90=711.78MB, p95=861.50MB, p99=1.09GB, max=1.34GB}                                                                                                                                                                                                                                      
     Amount of input data processed by the workers for this stage might be skewed                                                                                                                                                                                                                                                                                                                                    
     Output layout: [key, data, something, <107 more columns>]
     Output partitioning: SINGLE []                                                                                                                                                                                                                                                                                                                                                                                  
     InnerJoin[criteria = (key = key_0), distribution = REPLICATED]                                                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   Layout: [key:varchar, data:array(real), something:varchar, <107 more columns>]
     <E2><94><82>   Reorder joins cost : {rows: 89708 (104.38MB), cpu: 399.59G, memory: 991.97MB, network: 991.97MB}                                                                                                                                                                                                                                                                                                 
     <E2><94><82>   Estimates: {rows: 89708 (104.38MB), cpu: 19.18G, memory: 991.97MB, network: 0B}                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   CPU: 2.48m (14.54%), Scheduled: 2.74m (0.72%), Blocked: 6.06h (97.19%), Output: 15708 rows (145.78MB)                                                                                                                                                                                                                                                                                            
     <E2><94><82>   Left (probe) metrics:                                                                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>     'Blocked time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.14}                                                                                                                                                                                                                                  
     <E2><94><82>     'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                                      
     <E2><94><82>     'Input rows distribution' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=4.84, p95=8.89, p99=17.75, min=0.00, max=46.00}                                                                                                                                                                                                                                      
     <E2><94><82>     'Scheduled time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.06}                                                                                                                                                                                                                                
     <E2><94><82>   Right (build) metrics:                                                                                                                                                                                                                                                                                                                                                                           
     <E2><94><82>     'Blocked time distribution (s)' = {count=3360, p01=0.00, p05=0.01, p10=0.02, p25=0.21, p50=1.31, p75=13.86, p90=16.03, p95=16.90, p99=19.43, min=0.00, max=19.95}                                                                                                                                                                                                                              
     <E2><94><82>     'CPU time distribution (s)' = {count=3360, p01=0.02, p05=0.02, p10=0.02, p25=0.03, p50=0.04, p75=0.05, p90=0.07, p95=0.07, p99=0.08, min=0.01, max=0.10}                                                                                                                                                                                                                                       
     <E2><94><82>     'Input rows distribution' = {count=3360, p01=1887.00, p05=1887.00, p10=1887.00, p25=1935.12, p50=1973.41, p75=1991.05, p90=2019.00, p95=2019.00, p99=2019.00, min=1887.00, max=2019.00}                                                                                                                                                                                                        
     <E2><94><82>     'Scheduled time distribution (s)' = {count=3360, p01=0.02, p05=0.02, p10=0.02, p25=0.03, p50=0.04, p75=0.06, p90=0.08, p95=0.09, p99=0.12, min=0.01, max=0.21}                                                                                                                                                                                                                                 
     <E2><94><82>   Left (probe) Input avg.: 1.27 rows, Input std.dev.: 291.66%                                                                                                                                                                                                                                                                                                                                      
     <E2><94><82>   Right (build) Input avg.: 1963.50 rows, Input std.dev.: 2.02%                                                                                                                                                                                                                                                                                                                                    
     <E2><94><82>   Distribution: REPLICATED                                                                                                                                                                                                                                                                                                                                                                         
     <E2><94><9C><E2><94><80> ScanFilter[table = iceberg:test.huge_table$data@2617120010605135979, dynamicFilters = {key = #df_388}]                                                                                                                                                                                                                                                                                 
     <E2><94><82>      Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>      Estimates: {rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}/{rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}                                                                                                                                                                                                                                                
     <E2><94><82>      CPU: 11.88m (69.51%), Scheduled: 6.04h (94.78%), Blocked: 0.00ns (0.00%), Output: 15708 rows (122.38MB)                                                                                                                                                                                                                                                                                       
     <E2><94><82>      connector metrics:                                                                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>        'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=948643070448}                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>      metrics:                                                                                                                                                                                                                                                                                                                                                                                      
     <E2><94><82>        'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.01, p75=0.01, p90=0.23, p95=0.44, p99=0.61, min=0.00, max=0.91}                                                                                                                                                                                                                                   
     <E2><94><82>        'Dynamic Filter CPU time' = {duration=24.75s}                                                                                                                                                                                                                                                                                                                                               
     <E2><94><82>        'Dynamic Filter input positions' = LongCount{total=167329754}                                                                                                                                                                                                                                                                                                                               
     <E2><94><82>        'Input rows distribution' = {count=12392, p01=325.16, p05=1531.58, p10=2952.16, p25=6330.45, p50=12882.19, p75=23132.41, p90=23693.65, p95=23764.06, p99=23892.83, min=60.00, max=24217.00}                                                                                                                                                                                                 
     <E2><94><82>        'Projection CPU time' = {duration=73.46ms}                                                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>        'Scheduled time distribution (s)' = {count=12392, p01=0.03, p05=0.04, p10=0.05, p25=0.07, p50=0.12, p75=0.23, p90=8.33, p95=13.21, p99=16.56, min=0.02, max=20.14}                                                                                                                                                                                                                          
     <E2><94><82>      Input avg.: 13503.05 rows, Input std.dev.: 59.11%                                                                                                                                                                                                                                                                                                                                             
     <E2><94><82>      key := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                                          
     <E2><94><82>      data := 2:data:array(real)                                                                                                                                                                                                                                                                                                                                                                    
     <E2><94><82>      Input: 167329754 rows (88.38GB), Filtered: 99.99%, Physical input: 106.68GB, Physical input time: 4.43h                                                                                                                                                                                                                                                                                       
     <E2><94><82>      Dynamic filters:                                                                                                                                                                                                                                                                                                                                                                              
     <E2><94><82>          - df_388, [ SortedRangeSet[type=varchar, ranges=15708, {[0000307A1D21839898E5EC4EB741E25C], ..., [FFF6E9E4F34AC21A3F691F5DA3F0699E]}] ], collection time=2.57s                                                                                                                                                                                                                            
     <E2><94><94><E2><94><80> LocalExchange[partitioning = HASH, arguments = [key_0::varchar]]                                                                                                                                                                                                                                                                                                                       
        <E2><94><82>   Layout: [key_0:varchar, something:varchar, <107 more columns>]
        <E2><94><82>   Estimates: {rows: 89290 (99.20MB), cpu: 99.20M, memory: 0B, network: 0B}                                                                                                                                                                                                                                                                                                                      
        <E2><94><82>   CPU: 26.85s (2.62%), Scheduled: 30.46s (0.13%), Blocked: 7.14m (1.91%), Output: 6597360 rows (9.83GB)                                                                                                                                                                                                                                                                                         
        <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                                      
        <E2><94><82>     'CPU time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.01, p90=0.01, p95=0.01, p99=0.03, min=0.00, max=0.05}                                                                                                                                                                                                                                    
        <E2><94><82>     'Input rows distribution' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=54.82, p50=1852.20, p75=2303.32, p90=3804.66, p95=5932.43, p99=15093.91, min=0.00, max=15708.00}                                                                                                                                                                                                                 
        <E2><94><82>     'Scheduled time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.01, p90=0.01, p95=0.02, p99=0.03, min=0.00, max=0.08}                                                                                                                                                                                                                              
        <E2><94><82>   Input avg.: 1963.50 rows, Input std.dev.: 117.79%                                                                                                                                                                                                                                                                                                                                             
        <E2><94><94><E2><94><80> RemoteSource[sourceFragmentIds = [2]]                                                                                                                                                                                                                                                                                                                                               
               Layout: [key_0:varchar, something:varchar, <107 more columns>]
               CPU: 22.13s (2.16%), Scheduled: 30.34s (0.13%), Blocked: 3.39m (0.91%), Output: 6597360 rows (9.82GB)                                                                                                                                                                                                                                                                                                 
               metrics:                                                                                                                                                                                                                                                                                                                                                                                              
                 'Blocked time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.01, p25=0.01, p50=0.02, p75=0.05, p90=0.11, p95=0.20, p99=0.74, min=0.00, max=1.04}                                                                                                                                                                                                                                        
                 'CPU time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.01, p75=0.01, p90=0.01, p95=0.02, p99=0.04, min=0.00, max=0.07}                                                                                                                                                                                                                                            
                 'Input rows distribution' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=54.82, p50=1852.20, p75=2303.32, p90=3804.66, p95=5932.43, p99=15093.91, min=0.00, max=15708.00}                                                                                                                                                                                                                         
                 'Scheduled time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.01, p75=0.01, p90=0.02, p95=0.02, p99=0.05, min=0.00, max=0.12}                                                                                                                                                                                                                                      
               Input avg.: 1963.50 rows, Input std.dev.: 117.79%                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                     
 Fragment 2 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                                                 
     CPU: 1.91m, Scheduled: 16.21m, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 166814270 rows (23.08GB); per task: avg.: 8779698.42 std.dev.: 1498110.56, Output: 15708 rows (23.93MB)                                                                                                                                                                                                                   
     Task output distribution: {count=19.00, p01=380.84kB, p05=380.84kB, p10=1.06MB, p25=1.19MB, p50=1.31MB, p75=1.39MB, p90=1.43MB, p95=1.56MB, p99=1.56MB, max=1.56MB}                                                                                                                                                                                                                                             
     Task input distribution: {count=19.00, p01=335.09MB, p05=335.09MB, p10=1.01GB, p25=1.20GB, p50=1.26GB, p75=1.34GB, p90=1.36GB, p95=1.36GB, p99=1.36GB, max=1.36GB}                                                                                                                                                                                                                                              
     Output layout: [key_0, something, <107 more columns>]
     Output partitioning: BROADCAST []                                                                                                                                                                                                                                                                                                                                                                               
     DynamicFilterSource[dynamicFilterAssignments = {key_0 -> #df_388}]                                                                                                                                                                                                                                                                                                                                              
     <E2><94><82>   Layout: [key_0:varchar, something:varchar, <107 more columns>]
     <E2><94><82>   Estimates: {rows: 89290 (99.20MB), cpu: ?, memory: ?, network: ?}                                                                                                                                                                                                                                                                                                                                
     <E2><94><82>   CPU: 288.00ms (0.03%), Scheduled: 349.00ms (0.00%), Blocked: 0.00ns (0.00%), Output: 15708 rows (23.93MB)                                                                                                                                                                                                                                                                                        
     <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                                         
     <E2><94><82>     'CPU time distribution (s)' = {count=636, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                                        
     <E2><94><82>     'Input rows distribution' = {count=636, p01=0.00, p05=3.00, p10=7.65, p25=14.31, p50=23.05, p75=33.76, p90=43.44, p95=51.57, p99=66.00, min=0.00, max=87.00}                                                                                                                                                                                                                                   
     <E2><94><82>     'Scheduled time distribution (s)' = {count=636, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.06}                                                                                                                                                                                                                                  
     <E2><94><82>   Input avg.: 24.70 rows, Input std.dev.: 59.06%                                                                                                                                                                                                                                                                                                                                                   
     <E2><94><94><E2><94><80> ScanFilter[table = iceberg:test.query_table5$data@4918960324565841527, filterPredicate = (something = varchar 'FQ6RU285SEF4')]                                                                                                                                                                                                                                                         
            Layout: [key_0:varchar, something:varchar, <107 more columns>]
            Estimates: {rows: 167329754 (181.54GB), cpu: 181.54G, memory: 0B, network: 0B}/{rows: 89290 (99.20MB), cpu: 181.54G, memory: 0B, network: 0B}                                                                                                                                                                                                                                                            
            CPU: 1.90m (11.14%), Scheduled: 16.20m (4.24%), Blocked: 0.00ns (0.00%), Output: 15708 rows (23.93MB)                                                                                                                                                                                                                                                                                                    
            connector metrics:                                                                                                                                                                                                                                                                                                                                                                                       
              'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=18809504869}                                                                                                                                                                                                                                                                                                                                   
            metrics:                                                                                                                                                                                                                                                                                                                                                                                                 
              'CPU time distribution (s)' = {count=636, p01=0.00, p05=0.05, p10=0.09, p25=0.14, p50=0.18, p75=0.22, p90=0.28, p95=0.31, p99=0.37, min=0.00, max=0.42}                                                                                                                                                                                                                                                
              'Filter CPU time' = {duration=218.35ms}                                                                                                                                                                                                                                                                                                                                                                
              'Input rows distribution' = {count=636, p01=0.00, p05=60693.20, p10=205067.64, p25=221283.44, p50=253188.07, p75=306340.98, p90=370976.28, p95=422601.23, p99=549278.00, min=0.00, max=559729.00}                                                                                                                                                                                                      
              'Projection CPU time' = {duration=187.09ms}                                                                                                                                                                                                                                                                                                                                                            
              'Scheduled time distribution (s)' = {count=636, p01=0.19, p05=0.59, p10=0.98, p25=1.33, p50=1.59, p75=1.84, p90=2.00, p95=2.05, p99=2.22, min=0.17, max=2.42}                                                                                                                                                                                                                                          
            Input avg.: 262286.59 rows, Input std.dev.: 36.23%                                                                                                                                                                                                                                                                                                                                                       
            something := 2:something:varchar                                                                                                                                                                                                                                                                                                                                                                         >
            key_0 := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                                                   >
			<107 more columns>
            Input: 166814270 rows (23.08GB), Filtered: 99.99%, Physical input: 17.54GB, Physical input time: 14.11m                                                                                                                                                                                                                                                                                                  >
                                                                                                                                                                                                                                                                                                                                                                                                                     >
                                                                                                                                                                                                                                                                                                                                                                                                                     >
(1 row)

Query 20240917_090457_00036_wi368, FINISHED, 11 nodes
Splits: 19,757 total, 19,757 done (100.00%)
24.43 [341M rows, 125GB] [14M rows/s, 5.14GB/s]

HaraldVanWoerkom Sep 17, 2024
Author

Based on this, I found this partial solution:
select * from huge_table ht inner join query_table6 qt2 on ht.key=qt2.key where ht.key in (select qt.key from query_table6 qt where something='FQ6RU285SEF4');
This solution reads query_table6 twice, once to create the dynamic filter, and once to gather the data for the join. It uses the dynamic filter for reading huge_table and also for the second time reading query_table6.

I do not exactly understand why the original query does not work. It seems that a dynamic filter is created, but that the read of huge_table is placed in a fragment that cannot benefit from it. This query basically does the same thing. The second read from query_table6 results in a new dynamic filter and that is used to filter huge_table. And that dynamic filter should be exactly the same as the dynamic filter used in the original query. So why is the huge_table read placed in a separate fragment?

trino:test> explain analyze verbose select * from huge_table ht inner join query_table6 qt2 on ht.key=qt2.key where ht.key in (select qt.key from query_table6 qt where something='FQ6RU285SEF4');
                                                                                                                                                                   >
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Trino version: 455                                                                                                                                                                                                                                                                                                                                                                                
 Queued: 686.97us, Analysis: 28.68ms, Planning: 237.48ms, Execution: 26.72s                                                                                                                                                                                                                                                                                                                        
 Fragment 1 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                               
     CPU: 3.55m, Scheduled: 21.99m, Blocked 8.67m (Input: 1.70m, Output: 0.00ns), Input: 167659622 rows (34.51GB); per task: avg.: 7983791.52 std.dev.: 531032.80, Output: 15708 rows (148.30MB)                                                                                                                                                                                                   
     Task output distribution: {count=21.00, p01=4.91MB, p05=5.65MB, p10=5.94MB, p25=6.60MB, p50=7.30MB, p75=7.60MB, p90=7.87MB, p95=8.60MB, p99=8.76MB, max=8.76MB}                                                                                                                                                                                                                               
     Task input distribution: {count=21.00, p01=1.16GB, p05=1.47GB, p10=1.55GB, p25=1.61GB, p50=1.64GB, p75=1.70GB, p90=1.80GB, p95=1.83GB, p99=1.84GB, max=1.84GB}                                                                                                                                                                                                                                
     Output layout: [something, creationtype, <133 more columns>]
     Output partitioning: SINGLE []                                                                                                                                                                                                                                                                                                                                                                
     InnerJoin[criteria = (key_0 = key), distribution = REPLICATED]                                                                                                                                                                                                                                                                                                                                
     <E2><94><82>   Layout: [key_0:varchar, something:varchar, <133 more columns>]
     <E2><94><82>   Reorder joins cost : {rows: 90128 (119.51MB), cpu: 470.75G, memory: 141.86MB, network: 146.58MB}                                                                                                                                                                                                                                                                               
     <E2><94><82>   Estimates: {rows: 90128 (119.49MB), cpu: 208.37G, memory: 94.63MB, network: 0B}                                                                                                                                                                                                                                                                                                
     <E2><94><82>   CPU: 2.29s (0.24%), Scheduled: 3.24s (0.01%), Blocked: 4.44m (1.13%), Output: 15708 rows (148.30MB)                                                                                                                                                                                                                                                                            
     <E2><94><82>   Left (probe) metrics:                                                                                                                                                                                                                                                                                                                                                          
     <E2><94><82>     'Blocked time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.75}                                                                                                                                                                                                                  
     <E2><94><82>     'CPU time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                      
     <E2><94><82>     'Input rows distribution' = {count=640, p01=0.00, p05=2.89, p10=7.10, p25=14.67, p50=23.51, p75=33.62, p90=42.10, p95=49.00, p99=61.00, min=0.00, max=78.00}                                                                                                                                                                                                                 
     <E2><94><82>     'Scheduled time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.10}                                                                                                                                                                                                                
     <E2><94><82>   Right (build) metrics:                                                                                                                                                                                                                                                                                                                                                         
     <E2><94><82>     'Blocked time distribution (s)' = {count=168, p01=0.81, p05=0.88, p10=1.01, p25=1.32, p50=1.57, p75=1.80, p90=2.14, p95=2.24, p99=2.50, min=0.81, max=2.50}                                                                                                                                                                                                                  
     <E2><94><82>     'CPU time distribution (s)' = {count=168, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.01, p95=0.01, p99=0.01, min=0.00, max=0.01}                                                                                                                                                                                                                      
     <E2><94><82>     'Input rows distribution' = {count=168, p01=1887.00, p05=1887.00, p10=1887.00, p25=1934.89, p50=1973.31, p75=1991.43, p90=2019.00, p95=2019.00, p99=2019.00, min=1887.00, max=2019.00}                                                                                                                                                                                       
     <E2><94><82>     'Scheduled time distribution (s)' = {count=168, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.01, p90=0.01, p95=0.01, p99=0.08, min=0.00, max=0.08}                                                                                                                                                                                                                
     <E2><94><82>   Left (probe) Input avg.: 24.54 rows, Input std.dev.: 56.78%                                                                                                                                                                                                                                                                                                                    
     <E2><94><82>   Right (build) Input avg.: 1963.50 rows, Input std.dev.: 2.02%                                                                                                                                                                                                                                                                                                                  
     <E2><94><82>   Distribution: REPLICATED                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><9C><E2><94><80> ScanFilter[table = iceberg:test.query_table6$data@2728742793164734417, dynamicFilters = {key_0 = #df_730}]                                                                                                                                                                                                                                                           
     <E2><94><82>      Layout: [key_0:varchar, something:varchar, <133 more columns>]
     <E2><94><82>      Estimates: {rows: 167329754 (208.08GB), cpu: 208.08G, memory: 0B, network: 0B}/{rows: 167329754 (208.08GB), cpu: 208.08G, memory: 0B, network: 0B}                                                                                                                                                                                                                          
     <E2><94><82>      CPU: 2.77m (17.39%), Scheduled: 20.97m (5.31%), Blocked: 0.00ns (0.00%), Output: 15708 rows (26.48MB)                                                                                                                                                                                                                                                                       
     <E2><94><82>      connector metrics:                                                                                                                                                                                                                                                                                                                                                          
     <E2><94><82>        'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=21565231284}                                                                                                                                                                                                                                                                                                      
     <E2><94><82>      metrics:                                                                                                                                                                                                                                                                                                                                                                    
     <E2><94><82>        'CPU time distribution (s)' = {count=640, p01=0.01, p05=0.06, p10=0.14, p25=0.21, p50=0.27, p75=0.32, p90=0.37, p95=0.40, p99=0.47, min=0.00, max=0.52}                                                                                                                                                                                                                   
     <E2><94><82>        'Dynamic Filter CPU time' = {duration=23.73s}                                                                                                                                                                                                                                                                                                                             
     <E2><94><82>        'Dynamic Filter input positions' = LongCount{total=167329754}                                                                                                                                                                                                                                                                                                             
     <E2><94><82>        'Input rows distribution' = {count=640, p01=12677.00, p05=55781.89, p10=206497.37, p25=226186.14, p50=256531.06, p75=303137.61, p90=365373.14, p95=412722.25, p99=523515.00, min=5401.00, max=535961.00}                                                                                                                                                                  
     <E2><94><82>        'Projection CPU time' = {duration=72.52ms}                                                                                                                                                                                                                                                                                                                                
     <E2><94><82>        'Scheduled time distribution (s)' = {count=640, p01=0.09, p05=1.05, p10=1.40, p25=1.68, p50=2.00, p75=2.30, p90=2.60, p95=2.88, p99=3.02, min=0.06, max=3.35}                                                                                                                                                                                                             
     <E2><94><82>      Input avg.: 261452.74 rows, Input std.dev.: 34.51%                                                                                                                                                                                                                                                                                                                          
     <E2><94><82>      something := 2:something:varchar                                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>      key_0 := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                      
     <E2><94><82>      Input: 167329754 rows (32.00GB), Filtered: 99.99%, Physical input: 20.05GB, Physical input time: 17.80m                                                                                                                                                                                                                                                                     
     <E2><94><82>      Dynamic filters:                                                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>          - df_730, [ SortedRangeSet[type=varchar, ranges=15708, {[0000307A1D21839898E5EC4EB741E25C], ..., [FFF6E9E4F34AC21A3F691F5DA3F0699E]}] ], collection time=21.34s                                                                                                                                                                                                         
     <E2><94><94><E2><94><80> LocalExchange[partitioning = HASH, arguments = [key::varchar]]                                                                                                                                                                                                                                                                                                       
        <E2><94><82>   Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                     
        <E2><94><82>   Estimates: {rows: 89708 (9.46MB), cpu: 9.46M, memory: 0B, network: 0B}                                                                                                                                                                                                                                                                                                      
        <E2><94><82>   CPU: 3.42s (0.36%), Scheduled: 6.04s (0.03%), Blocked: 2.54m (0.65%), Output: 329868 rows (2.51GB)                                                                                                                                                                                                                                                                          
        <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                    
        <E2><94><82>     'CPU time distribution (s)' = {count=168, p01=0.01, p05=0.01, p10=0.01, p25=0.01, p50=0.02, p75=0.02, p90=0.03, p95=0.03, p99=0.04, min=0.01, max=0.04}                                                                                                                                                                                                                   
        <E2><94><82>     'Input rows distribution' = {count=168, p01=1128.00, p05=1366.00, p10=1639.00, p25=1813.50, p50=1974.87, p75=2148.28, p90=2331.00, p95=2430.00, p99=2637.00, min=871.00, max=2788.00}                                                                                                                                                                                     
        <E2><94><82>     'Scheduled time distribution (s)' = {count=168, p01=0.01, p05=0.01, p10=0.01, p25=0.01, p50=0.02, p75=0.03, p90=0.05, p95=0.18, p99=0.19, min=0.01, max=0.19}                                                                                                                                                                                                             
        <E2><94><82>   Input avg.: 1963.50 rows, Input std.dev.: 15.33%                                                                                                                                                                                                                                                                                                                            
        <E2><94><94><E2><94><80> RemoteSource[sourceFragmentIds = [2]]                                                                                                                                                                                                                                                                                                                             
               Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                             
               CPU: 40.61s (4.24%), Scheduled: 50.66s (0.21%), Blocked: 1.70m (0.43%), Output: 329868 rows (2.51GB)                                                                                                                                                                                                                                                                                
               metrics:                                                                                                                                                                                                                                                                                                                                                                            
                 'Blocked time distribution (s)' = {count=168, p01=0.24, p05=0.28, p10=0.29, p25=0.34, p50=0.77, p75=0.84, p90=0.87, p95=0.93, p99=1.07, min=0.24, max=1.07}                                                                                                                                                                                                                       
                 'CPU time distribution (s)' = {count=168, p01=0.01, p05=0.01, p10=0.02, p25=0.06, p50=0.16, p75=0.48, p90=0.52, p95=0.53, p99=0.54, min=0.01, max=0.54}                                                                                                                                                                                                                           
                 'Input rows distribution' = {count=168, p01=1128.00, p05=1366.00, p10=1639.00, p25=1813.50, p50=1974.87, p75=2148.28, p90=2331.00, p95=2430.00, p99=2637.00, min=871.00, max=2788.00}                                                                                                                                                                                             
                 'Scheduled time distribution (s)' = {count=168, p01=0.02, p05=0.03, p10=0.03, p25=0.09, p50=0.23, p75=0.51, p90=0.55, p95=0.61, p99=0.70, min=0.02, max=0.70}                                                                                                                                                                                                                     
               Input avg.: 1963.50 rows, Input std.dev.: 15.33%                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                   
 Fragment 2 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                               
     CPU: 12.15m, Scheduled: 6.15h, Blocked 6.49h (Input: 1.57m, Output: 0.00ns), Input: 173927114 rows (88.61GB); per task: avg.: 414112.18 std.dev.: 180410.85, Output: 15708 rows (122.38MB)                                                                                                                                                                                                    
     Task output distribution: {count=420.00, p01=0B, p05=0B, p10=0B, p25=0B, p50=740B, p75=534.17kB, p90=994.74kB, p95=1.23MB, p99=1.51MB, max=2.13MB}                                                                                                                                                                                                                                            
     Task input distribution: {count=420.00, p01=6.42MB, p05=6.65MB, p10=6.88MB, p25=13.27MB, p50=23.24MB, p75=363.96MB, p90=688.40MB, p95=838.12MB, p99=1.06GB, max=1.31GB}                                                                                                                                                                                                                       
     Amount of input data processed by the workers for this stage might be skewed                                                                                                                                                                                                                                                                                                                  
     Output layout: [key, data]                                                                                                                                                                                                                                                                                                                                                                    
     Output partitioning: BROADCAST []                                                                                                                                                                                                                                                                                                                                                             
     DynamicFilterSource[dynamicFilterAssignments = {key -> #df_730}]                                                                                                                                                                                                                                                                                                                              
     <E2><94><82>   Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                        
     <E2><94><82>   Estimates: {rows: 89708 (9.46MB), cpu: ?, memory: ?, network: ?}                                                                                                                                                                                                                                                                                                               
     <E2><94><82>   CPU: 1.40s (0.15%), Scheduled: 1.41s (0.01%), Blocked: 0.00ns (0.00%), Output: 15708 rows (122.38MB)                                                                                                                                                                                                                                                                           
     <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>     'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.01}                                                                                                                                                                                                                    
     <E2><94><82>     'Input rows distribution' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=4.77, p95=8.86, p99=17.74, min=0.00, max=46.00}                                                                                                                                                                                                                    
     <E2><94><82>     'Scheduled time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.01}                                                                                                                                                                                                              
     <E2><94><82>   Input avg.: 1.27 rows, Input std.dev.: 291.66%                                                                                                                                                                                                                                                                                                                                 
     <E2><94><94><E2><94><80> InnerJoin[criteria = (key = key_4), distribution = REPLICATED]                                                                                                                                                                                                                                                                                                       
        <E2><94><82>   Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                     
        <E2><94><82>   Reorder joins cost : {rows: 89708 (9.46MB), cpu: 54.29G, memory: 51.95MB, network: 51.95MB}                                                                                                                                                                                                                                                                                 
        <E2><94><82>   Estimates: {rows: 89708 (9.46MB), cpu: 17.33G, memory: 47.23MB, network: 0B}                                                                                                                                                                                                                                                                                                
        <E2><94><82>   CPU: 914.00ms (0.10%), Scheduled: 1.48s (0.01%), Blocked: 6.31h (96.30%), Output: 15708 rows (122.38MB)                                                                                                                                                                                                                                                                     
        <E2><94><82>   Left (probe) metrics:                                                                                                                                                                                                                                                                                                                                                       
        <E2><94><82>     'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                 
        <E2><94><82>     'Input rows distribution' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=4.77, p95=8.86, p99=17.74, min=0.00, max=46.00}                                                                                                                                                                                                                 
        <E2><94><82>     'Scheduled time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                           
        <E2><94><82>   Right (build) metrics:                                                                                                                                                                                                                                                                                                                                                      
        <E2><94><82>     'Blocked time distribution (s)' = {count=3360, p01=0.00, p05=0.07, p10=0.10, p25=0.28, p50=1.29, p75=14.95, p90=16.97, p95=17.54, p99=18.54, min=0.00, max=18.69}                                                                                                                                                                                                         
        <E2><94><82>     'CPU time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.01}                                                                                                                                                                                                                  
        <E2><94><82>     'Input rows distribution' = {count=3360, p01=1887.00, p05=1887.00, p10=1887.00, p25=1935.12, p50=1973.41, p75=1991.05, p90=2019.00, p95=2019.00, p99=2019.00, min=1887.00, max=2019.00}                                                                                                                                                                                   
        <E2><94><82>     'Scheduled time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.05}                                                                                                                                                                                                            
        <E2><94><82>   Left (probe) Input avg.: 1.27 rows, Input std.dev.: 291.66%                                                                                                                                                                                                                                                                                                                 
        <E2><94><82>   Right (build) Input avg.: 1963.50 rows, Input std.dev.: 2.02%                                                                                                                                                                                                                                                                                                               
        <E2><94><82>   Distribution: REPLICATED                                                                                                                                                                                                                                                                                                                                                    
        <E2><94><9C><E2><94><80> ScanFilter[table = iceberg:test.huge_table$data@2617120010605135979, dynamicFilters = {key = #df_732}]                                                                                                                                                                                                                                                            
        <E2><94><82>      Layout: [key:varchar, data:array(real)]                                                                                                                                                                                                                                                                                                                                  
        <E2><94><82>      Estimates: {rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}/{rows: 167329754 (17.24GB), cpu: 17.24G, memory: 0B, network: 0B}                                                                                                                                                                                                                           
        <E2><94><82>      CPU: 12.06m (75.60%), Scheduled: 6.14h (93.41%), Blocked: 0.00ns (0.00%), Output: 15708 rows (122.38MB)                                                                                                                                                                                                                                                                  
        <E2><94><82>      connector metrics:                                                                                                                                                                                                                                                                                                                                                       
        <E2><94><82>        'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=948643070448}                                                                                                                                                                                                                                                                                                  
        <E2><94><82>      metrics:                                                                                                                                                                                                                                                                                                                                                                 
        <E2><94><82>        'CPU time distribution (s)' = {count=12392, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.01, p75=0.01, p90=0.25, p95=0.45, p99=0.62, min=0.00, max=0.80}                                                                                                                                                                                                              
        <E2><94><82>        'Dynamic Filter CPU time' = {duration=21.19s}                                                                                                                                                                                                                                                                                                                          
        <E2><94><82>        'Dynamic Filter input positions' = LongCount{total=167329754}                                                                                                                                                                                                                                                                                                          
        <E2><94><82>        'Input rows distribution' = {count=12392, p01=321.35, p05=1528.10, p10=2950.51, p25=6352.94, p50=12873.86, p75=23121.95, p90=23693.88, p95=23764.48, p99=23893.34, min=60.00, max=24217.00}                                                                                                                                                                            
        <E2><94><82>        'Projection CPU time' = {duration=75.59ms}                                                                                                                                                                                                                                                                                                                             
        <E2><94><82>        'Scheduled time distribution (s)' = {count=12392, p01=0.03, p05=0.04, p10=0.05, p25=0.07, p50=0.10, p75=0.18, p90=8.67, p95=13.57, p99=17.06, min=0.02, max=18.68}                                                                                                                                                                                                     
        <E2><94><82>      Input avg.: 13503.05 rows, Input std.dev.: 59.11%                                                                                                                                                                                                                                                                                                                        
        <E2><94><82>      key := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                     
        <E2><94><82>      data := 2:data:array(real)                                                                                                                                                                                                                                                                                                                                               
        <E2><94><82>      Input: 167329754 rows (88.38GB), Filtered: 99.99%, Physical input: 106.68GB, Physical input time: 4.52h                                                                                                                                                                                                                                                                  
        <E2><94><82>      Dynamic filters:                                                                                                                                                                                                                                                                                                                                                         
        <E2><94><82>          - df_732, [ SortedRangeSet[type=varchar, ranges=15708, {[0000307A1D21839898E5EC4EB741E25C], ..., [FFF6E9E4F34AC21A3F691F5DA3F0699E]}] ], collection time=1.50s                                                                                                                                                                                                       
        <E2><94><94><E2><94><80> LocalExchange[partitioning = HASH, arguments = [key_4::varchar]]                                                                                                                                                                                                                                                                                                  
           <E2><94><82>   Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                  
           <E2><94><82>   Estimates: {rows: 89290 (4.72MB), cpu: 4.72M, memory: 0B, network: 0B}                                                                                                                                                                                                                                                                                                   
           <E2><94><82>   CPU: 793.00ms (0.08%), Scheduled: 925.00ms (0.00%), Blocked: 4.06m (1.03%), Output: 6597360 rows (232.79MB)                                                                                                                                                                                                                                                              
           <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                 
           <E2><94><82>     'CPU time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                               
           <E2><94><82>     'Input rows distribution' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=1303.49, p75=2560.46, p90=4355.33, p95=7238.53, p99=15708.00, min=0.00, max=15708.00}                                                                                                                                                                                             
           <E2><94><82>     'Scheduled time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.05}                                                                                                                                                                                                         
           <E2><94><82>   Input avg.: 1963.50 rows, Input std.dev.: 155.24%                                                                                                                                                                                                                                                                                                                        
           <E2><94><94><E2><94><80> RemoteSource[sourceFragmentIds = [3]]                                                                                                                                                                                                                                                                                                                          
                  Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                          
                  CPU: 1.50s (0.16%), Scheduled: 8.61s (0.04%), Blocked: 1.57m (0.40%), Output: 6597360 rows (232.79MB)                                                                                                                                                                                                                                                                            
                  metrics:                                                                                                                                                                                                                                                                                                                                                                         
                    'Blocked time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.01, p25=0.01, p50=0.01, p75=0.02, p90=0.03, p95=0.06, p99=0.53, min=0.00, max=0.73}                                                                                                                                                                                                                   
                    'CPU time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.01}                                                                                                                                                                                                                       
                    'Input rows distribution' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=1303.49, p75=2560.46, p90=4355.33, p95=7238.53, p99=15708.00, min=0.00, max=15708.00}                                                                                                                                                                                                     
                    'Scheduled time distribution (s)' = {count=3360, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.01, p95=0.01, p99=0.01, min=0.00, max=0.05}                                                                                                                                                                                                                 
                  Input avg.: 1963.50 rows, Input std.dev.: 155.24%                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                   
 Fragment 3 [HASH]                                                                                                                                                                                                                                                                                                                                                                                 
     CPU: 324.13ms, Scheduled: 787.93ms, Blocked 16.11s (Input: 7.79s, Output: 0.00ns), Input: 15708 rows (567.57kB); per task: avg.: 628.32 std.dev.: 25.71, Output: 15708 rows (567.57kB)                                                                                                                                                                                                        
     Task output distribution: {count=25.00, p01=21.28kB, p05=21.39kB, p10=21.68kB, p25=21.86kB, p50=22.58kB, p75=23.45kB, p90=23.78kB, p95=24.21kB, p99=24.82kB, max=24.82kB}                                                                                                                                                                                                                     
     Task input distribution: {count=25.00, p01=21.28kB, p05=21.39kB, p10=21.68kB, p25=21.86kB, p50=22.58kB, p75=23.45kB, p90=23.78kB, p95=24.21kB, p99=24.82kB, max=24.82kB}                                                                                                                                                                                                                      
     Output layout: [key_4]                                                                                                                                                                                                                                                                                                                                                                        
     Output partitioning: BROADCAST []                                                                                                                                                                                                                                                                                                                                                             
     DynamicFilterSource[dynamicFilterAssignments = {key_4 -> #df_732}]                                                                                                                                                                                                                                                                                                                            
     <E2><94><82>   Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                        
     <E2><94><82>   Estimates: {rows: 89290 (4.72MB), cpu: ?, memory: ?, network: ?}                                                                                                                                                                                                                                                                                                               
     <E2><94><82>   CPU: 77.00ms (0.01%), Scheduled: 110.00ms (0.00%), Blocked: 0.00ns (0.00%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                      
     <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>     'CPU time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                      
     <E2><94><82>     'Input rows distribution' = {count=200, p01=60.00, p05=66.00, p10=69.00, p25=72.88, p50=77.83, p75=83.98, p90=89.50, p95=93.00, p99=101.00, min=59.00, max=103.00}                                                                                                                                                                                                           
     <E2><94><82>     'Scheduled time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                
     <E2><94><82>   Input avg.: 78.54 rows, Input std.dev.: 10.49%                                                                                                                                                                                                                                                                                                                                 
     <E2><94><94><E2><94><80> Aggregate[type = FINAL, keys = [key_4]]                                                                                                                                                                                                                                                                                                                              
        <E2><94><82>   Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                     
        <E2><94><82>   Estimates: {rows: 89290 (4.72MB), cpu: 4.72M, memory: 4.72MB, network: 0B}                                                                                                                                                                                                                                                                                                  
        <E2><94><82>   CPU: 56.00ms (0.01%), Scheduled: 89.00ms (0.00%), Blocked: 0.00ns (0.00%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                    
        <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                    
        <E2><94><82>     'CPU time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                   
        <E2><94><82>     'Input rows distribution' = {count=200, p01=60.00, p05=66.00, p10=69.00, p25=72.88, p50=77.83, p75=83.98, p90=89.50, p95=93.00, p99=101.00, min=59.00, max=103.00}                                                                                                                                                                                                        
        <E2><94><82>     'Scheduled time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                             
        <E2><94><82>   Input avg.: 78.54 rows, Input std.dev.: 10.49%                                                                                                                                                                                                                                                                                                                              
        <E2><94><94><E2><94><80> LocalExchange[partitioning = HASH, arguments = [key_4::varchar]]                                                                                                                                                                                                                                                                                                  
           <E2><94><82>   Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                  
           <E2><94><82>   Estimates: {rows: 89290 (4.72MB), cpu: 4.72M, memory: 0B, network: 0B}                                                                                                                                                                                                                                                                                                   
           <E2><94><82>   CPU: 3.00ms (0.00%), Scheduled: 8.00ms (0.00%), Blocked: 8.33s (0.04%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                    
           <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                 
           <E2><94><82>     'CPU time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                
           <E2><94><82>     'Input rows distribution' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=18.25, p50=65.20, p75=109.67, p90=181.50, p95=234.00, p99=329.00, min=0.00, max=348.00}                                                                                                                                                                                                      
           <E2><94><82>     'Scheduled time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                          
           <E2><94><82>   Input avg.: 78.54 rows, Input std.dev.: 94.53%                                                                                                                                                                                                                                                                                                                           
           <E2><94><94><E2><94><80> RemoteSource[sourceFragmentIds = [4]]                                                                                                                                                                                                                                                                                                                          
                  Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                          
                  CPU: 163.00ms (0.02%), Scheduled: 531.00ms (0.00%), Blocked: 7.79s (0.03%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                        
                  metrics:                                                                                                                                                                                                                                                                                                                                                                         
                    'Blocked time distribution (s)' = {count=200, p01=0.02, p05=0.02, p10=0.02, p25=0.04, p50=0.04, p75=0.04, p90=0.04, p95=0.05, p99=0.05, min=0.02, max=0.05}                                                                                                                                                                                                                    
                    'CPU time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.01, p99=0.01, min=0.00, max=0.01}                                                                                                                                                                                                                        
                    'Input rows distribution' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=18.25, p50=65.20, p75=109.67, p90=181.50, p95=234.00, p99=329.00, min=0.00, max=348.00}                                                                                                                                                                                                              
                    'Scheduled time distribution (s)' = {count=200, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.01, p95=0.01, p99=0.01, min=0.00, max=0.03}                                                                                                                                                                                                                  
                  Input avg.: 78.54 rows, Input std.dev.: 94.53%                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                   
 Fragment 4 [SOURCE]                                                                                                                                                                                                                                                                                                                                                                               
     CPU: 15.87s, Scheduled: 3.81m, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 166223343 rows (1.83GB); per task: avg.: 7915397.29 std.dev.: 645639.68, Output: 15708 rows (567.57kB)                                                                                                                                                                                                  
     Task output distribution: {count=21.00, p01=18.79kB, p05=21.64kB, p10=22.76kB, p25=25.26kB, p50=27.97kB, p75=29.09kB, p90=30.13kB, p95=32.88kB, p99=33.53kB, max=33.53kB}                                                                                                                                                                                                                     
     Task input distribution: {count=21.00, p01=59.48MB, p05=81.02MB, p10=85.37MB, p25=88.63MB, p50=89.90MB, p75=92.07MB, p90=95.89MB, p95=96.86MB, p99=97.31MB, max=97.31MB}                                                                                                                                                                                                                      
     Output layout: [key_4]                                                                                                                                                                                                                                                                                                                                                                        
     Output partitioning: HASH [key_4]                                                                                                                                                                                                                                                                                                                                                             
     Aggregate[type = PARTIAL, keys = [key_4]]                                                                                                                                                                                                                                                                                                                                                     
     <E2><94><82>   Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                        
     <E2><94><82>   Estimates: {rows: 89290 (4.72MB), cpu: ?, memory: ?, network: ?}                                                                                                                                                                                                                                                                                                               
     <E2><94><82>   CPU: 220.00ms (0.02%), Scheduled: 274.00ms (0.00%), Blocked: 0.00ns (0.00%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                     
     <E2><94><82>   metrics:                                                                                                                                                                                                                                                                                                                                                                       
     <E2><94><82>     'CPU time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.00}                                                                                                                                                                                                                      
     <E2><94><82>     'Input rows distribution' = {count=640, p01=0.00, p05=2.89, p10=7.07, p25=14.65, p50=23.54, p75=33.50, p90=42.05, p95=48.75, p99=61.00, min=0.00, max=78.00}                                                                                                                                                                                                                 
     <E2><94><82>     'Scheduled time distribution (s)' = {count=640, p01=0.00, p05=0.00, p10=0.00, p25=0.00, p50=0.00, p75=0.00, p90=0.00, p95=0.00, p99=0.00, min=0.00, max=0.05}                                                                                                                                                                                                                
     <E2><94><82>   Input avg.: 24.54 rows, Input std.dev.: 56.78%                                                                                                                                                                                                                                                                                                                                 
     <E2><94><94><E2><94><80> ScanFilterProject[table = iceberg:test.query_table6$data@2728742793164734417, filterPredicate = (something_5 = varchar 'FQ6RU285SEF4')]                                                                                                                                                                                                                              
            Layout: [key_4:varchar]                                                                                                                                                                                                                                                                                                                                                                
            Estimates: {rows: 167329754 (8.64GB), cpu: 9.85G, memory: 0B, network: 0B}/{rows: 89290 (4.72MB), cpu: 9.85G, memory: 0B, network: 0B}/{rows: 89290 (4.72MB), cpu: 4.72M, memory: 0B, network: 0B}                                                                                                                                                                                     
            CPU: 15.59s (1.63%), Scheduled: 3.81m (0.96%), Blocked: 0.00ns (0.00%), Output: 15708 rows (567.57kB)                                                                                                                                                                                                                                                                                  
            connector metrics:                                                                                                                                                                                                                                                                                                                                                                     
              'ParquetReaderCompressionFormat_ZSTD' = LongCount{total=3273800425}                                                                                                                                                                                                                                                                                                                  
            metrics:                                                                                                                                                                                                                                                                                                                                                                               
              'CPU time distribution (s)' = {count=640, p01=0.00, p05=0.01, p10=0.01, p25=0.02, p50=0.02, p75=0.03, p90=0.04, p95=0.04, p99=0.07, min=0.00, max=0.09}                                                                                                                                                                                                                              
              'Filter CPU time' = {duration=214.63ms}                                                                                                                                                                                                                                                                                                                                              
              'Input rows distribution' = {count=640, p01=0.00, p05=41920.50, p10=196940.60, p25=225920.03, p50=256447.66, p75=303461.96, p90=365779.18, p95=412466.45, p99=523515.00, min=0.00, max=535961.00}                                                                                                                                                                                    
              'Projection CPU time' = {duration=493.48us}                                                                                                                                                                                                                                                                                                                                          
              'Scheduled time distribution (s)' = {count=640, p01=0.10, p05=0.18, p10=0.21, p25=0.26, p50=0.33, p75=0.42, p90=0.61, p95=0.65, p99=0.71, min=0.09, max=0.81}                                                                                                                                                                                                                        
            Input avg.: 259723.97 rows, Input std.dev.: 36.08%                                                                                                                                                                                                                                                                                                                                     
            key_4 := 1:key:varchar                                                                                                                                                                                                                                                                                                                                                                 
            something_5 := 2:something:varchar                                                                                                                                                                                                                                                                                                                                                     
            Input: 166223343 rows (1.83GB), Filtered: 99.99%, Physical input: 3.08GB, Physical input time: 3.44m                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                   
(1 row)

Query 20240917_111730_00050_wi368, FINISHED, 11 nodes
Splits: 21,137 total, 21,137 done (100.00%)
27.01 [508M rows, 132GB] [18.8M rows/s, 4.88GB/s]

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Join reads full table when I am only interested in a subset #23442

{{title}}

Replies: 1 comment 4 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Join reads full table when I am only interested in a subset #23442

HaraldVanWoerkom Sep 16, 2024

Replies: 1 comment · 4 replies

Praveen2112 Sep 16, 2024 Collaborator

HaraldVanWoerkom Sep 16, 2024 Author

raunaqmorarka Sep 16, 2024 Collaborator

HaraldVanWoerkom Sep 17, 2024 Author

HaraldVanWoerkom Sep 17, 2024 Author

HaraldVanWoerkom
Sep 16, 2024

Replies: 1 comment 4 replies

Praveen2112
Sep 16, 2024
Collaborator

HaraldVanWoerkom Sep 16, 2024
Author

raunaqmorarka Sep 16, 2024
Collaborator

HaraldVanWoerkom Sep 17, 2024
Author

HaraldVanWoerkom Sep 17, 2024
Author