EXPLAIN ANALYZE

Syntax

EXPLAIN ANALYZE [VERBOSE] statement

Description

This statement is used to execute an SQL statement and display the distributed execution plan and the cost of each operation in the process.

VERBOSE is optional. If this parameter is specified, more detailed information and bottom-layer statistics are displayed. The statistics may not be accurate, especially for statements that are executed quickly.

Remarks

EXPLAIN ANALYZE does not support DDL statements.

Example

In the following example, you can view the CPU time consumed by each stage and the cost of each plan node.

The cost is based on the actual time (wall time) instead of the CPU-related time.

For each plan node, you can see additional statistics, such as the average input value of each node instance and the average number of hash collisions. The statistics are useful for analyzing data exceptions (such as data skewness and abnormal hash collisions) in an SQL statement.

EXPLAIN  ANALYZE  SELECT count(*),sum(totalprice) FROM new_orders GROUP BY orderstatus; 
                                                      Query Plan                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Fragment 1 [HASH]                                                                                                             
     CPU: 29.19ms, Scheduled: 134.78ms, Input: 2 rows (77B); per task: avg.: 1.00 std.dev.: 1.00, Output: 2 rows (36B)         
     Output layout: [count, sum]                                                                                               
     Output partitioning: SINGLE []                                                                                            
     Stage Execution Strategy: UNGROUPED_EXECUTION                                                                             
     Project[]                                                                                                                 
     │   Layout: [count:bigint, sum:double]                                                                                    
     │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                               
     │   CPU: 4.00ms (2.34%), Scheduled: 10.00ms (33.33%), Output: 2 rows (36B)                                                
     │   Input avg.: 0.06 rows, Input std.dev.: 387.30%                                                                        
     └─ Aggregate(FINAL)[orderstatus][$hashvalue]                                                                              
        │   Layout: [orderstatus:varchar, $hashvalue:bigint, count:bigint, sum:double]                                         
        │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                            
        │   CPU: 6.00ms (3.51%), Scheduled: 17.00ms (56.67%), Output: 2 rows (77B)                                             
        │   Input avg.: 0.06 rows, Input std.dev.: 387.30%                                                                     
        │   count := count("count_9")                                                                                          
        │   sum := sum("sum_10")                                                                                               
        └─ LocalExchange[HASH][$hashvalue] ("orderstatus")                                                                     
           │   Layout: [orderstatus:varchar, sum_10:double, count_9:bigint, $hashvalue:bigint]                                 
           │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                         
           │   CPU: 2.00ms (1.17%), Scheduled: 3.00ms (10.00%), Output: 2 rows (77B)                                           
           │   Input avg.: 0.06 rows, Input std.dev.: 556.78%                                                                  
           └─ RemoteSource[2]                                                                                                  
                  Layout: [orderstatus:varchar, sum_10:double, count_9:bigint, $hashvalue_11:bigint]                           
                  CPU: 1.00ms (0.58%), Scheduled: 3.00ms (10.00%), Output: 2 rows (77B)                                        
                  Input avg.: 0.06 rows, Input std.dev.: 556.78%                                                               

 Fragment 2 [SOURCE]                                                                                                           
     CPU: 17.35ms, Scheduled: 80.04ms, Input: 4 rows (81B); per task: avg.: 4.00 std.dev.: 0.00, Output: 2 rows (77B)          
     Output layout: [orderstatus, sum_10, count_9, $hashvalue_12]                                                              
     Output partitioning: HASH [orderstatus][$hashvalue_12]                                                                    
     Stage Execution Strategy: UNGROUPED_EXECUTION                                                                             
     Aggregate(PARTIAL)[orderstatus][$hashvalue_12]                                                                            
     │   Layout: [orderstatus:varchar, $hashvalue_12:bigint, sum_10:double, count_9:bigint]                                    
     │   CPU: 1.00ms (0.58%), Scheduled: 6.00ms (20.00%), Output: 2 rows (77B)                                                 
     │   Input avg.: 4.00 rows, Input std.dev.: 0.00%                                                                          
     │   sum_10 := sum("totalprice")                                                                                           
     │   count_9 := count(*)                                                                                                   
     └─ ScanProject[table = hive:default:new_orders, grouped = false]                                                          
            Layout: [orderstatus:varchar, totalprice:double, $hashvalue_12:bigint]                                             
            Estimates: {rows: 4 (292B), cpu: 256, memory: 0B, network: 0B}/{rows: 4 (292B), cpu: 548, memory: 0B, network: 0B} 
            CPU: 16.00ms (9.36%), Scheduled: 132.00ms (440.00%), Output: 4 rows (117B)                                         
            Input avg.: 4.00 rows, Input std.dev.: 0.00%                                                                       
            $hashvalue_12 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("orderstatus"), 0))                     
            orderstatus := orderstatus:string:1:REGULAR                                                                        
            totalprice := totalprice:double:2:REGULAR                                                                          
            Input: 4 rows (81B), Filtered: 0.00%                                                                               
(1 row)