postgresql φ(..)メモメモ 実行計画 LEFT JOIN と EXISTS

実行計画 LEFT JOIN

WITH                                                                                                                     
test1 AS (                                                                                                               
SELECT generate_series AS no                                                                                             
     , generate_series AS line                                                                                           
     , RANDOM()        AS num                                                                                            
  FROM GENERATE_SERIES(1, 10000)                                                                                         
),                                                                                                                       
test2 AS (                                                                                                               
SELECT generate_series AS no                                                                                             
     , generate_series AS line                                                                                           
     , RANDOM()        AS num                                                                                            
  FROM GENERATE_SERIES(1, 10000)                                                                                         
)                                                                                                                        

SELECT *                                                                                                                 
  FROM (SELECT TO_CHAR(no, '000000') AS no, line, num FROM test1 ) t1                                                    
       LEFT JOIN (SELECT TO_CHAR(no, '000000') AS no, line, num FROM test2 ) t2 ON t1.no   = t2.no AND t1.line = t2.line 

----- 実行計画 -----                                                                                                     
Merge Left Join  (cost=164.66..194.91 rows=1000 width=88)                                                                
  Merge Cond: (((to_char(test1.no, '000000'::text)) = (to_char(test2.no, '000000'::text))) AND (test1.line = test2.line))
  CTE test1                                                                                                              
    ->  Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=16)                                          
  CTE test2                                                                                                              
    ->  Function Scan on generate_series generate_series_1  (cost=0.00..12.50 rows=1000 width=16)                        
  ->  Sort  (cost=69.83..72.33 rows=1000 width=16)                                                                       
        Sort Key: (to_char(test1.no, '000000'::text)), test1.line                                                        
        ->  CTE Scan on test1  (cost=0.00..20.00 rows=1000 width=16)                                                     
  ->  Sort  (cost=69.83..72.33 rows=1000 width=16)                                                                       
        Sort Key: (to_char(test2.no, '000000'::text)), test2.line                                                        
        ->  CTE Scan on test2  (cost=0.00..20.00 rows=1000 width=16)                                                     

実行計画 EXISTS

WITH 
test1 AS (
SELECT generate_series AS no
     , generate_series AS line
     , RANDOM()        AS num
  FROM GENERATE_SERIES(1, 10000)
),
test2 AS (
SELECT generate_series AS no
     , generate_series AS line
     , RANDOM()        AS num
  FROM GENERATE_SERIES(1, 10000)
)

SELECT *
  FROM (SELECT TO_CHAR(no, '000000') AS no, line, num FROM test1 ) t1
 WHERE EXISTS (SELECT * FROM test2 t2 WHERE  t1.no = TO_CHAR(no, '000000') AND t1.line = t2.line )


----- 実行計画 -----
Hash Join  (cost=55.00..85.68 rows=250 width=44)
  Hash Cond: ((to_char(test1.no, '000000'::text) = to_char(t2.no, '000000'::text)) AND (test1.line = t2.line))
  CTE test1
    ->  Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=16)
  CTE test2
    ->  Function Scan on generate_series generate_series_1  (cost=0.00..12.50 rows=1000 width=16)
  ->  CTE Scan on test1  (cost=0.00..20.00 rows=1000 width=16)
  ->  Hash  (cost=27.00..27.00 rows=200 width=8)
        ->  HashAggregate  (cost=25.00..27.00 rows=200 width=8)
              Group Key: to_char(t2.no, '000000'::text), t2.line
              ->  CTE Scan on test2 t2  (cost=0.00..20.00 rows=1000 width=8)