PostgreSQLのEXPLAIN ANALYSE で実行スピードを比較してみた。

副問い合わせsubqueryを使ったSQL文

db_name=#
EXPLAIN ANALYSE SELECT
    employee_id,
    employee_name,
    年月,                               
    SUM(                                                                                                                    
        CASE
            WHEN 販売合計金額 IS NULL THEN 0 
            ELSE 販売合計金額
        END                  
    ) AS 月別販売合計金額    
FROM                                
    (                                                                                                                      
        SELECT               
            e.employee_id,
            e.employee_name,
            to_char(s.sale_date, 'yyyy-mm') AS 年月,    
            s.quantity * p.price AS 販売合計金額
        FROM                                         
            employees AS e                      
                LEFT OUTER JOIN                 
            sales AS s ON  e.employee_id = s.employee_id
                LEFT OUTER JOIN
            products AS p ON  s.product_id = p.product_id
    ) AS 販売合計金額                                    
GROUP BY                                    
    employee_id,                            
    employee_name,                          
    年月                                    
ORDER BY          
    employee_id   
;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=106.17..138.67 rows=1000 width=29) (actual time=9.294..10.175 rows=228 loops=1)
   ->  Sort  (cost=106.17..108.67 rows=1000 width=29) (actual time=9.283..9.344 rows=1023 loops=1)
         Sort Key: e.employee_id, e.employee_name, (to_char((s.sale_date)::timestamp with time zone, 'yyyy-mm'::text))
         Sort Method: quicksort  Memory: 104kB
         ->  Hash Left Join  (cost=5.84..56.34 rows=1000 width=29) (actual time=0.102..1.571 rows=1023 loops=1)
               Hash Cond: (s.product_id = p.product_id)
               ->  Hash Right Join  (cost=2.70..34.45 rows=1000 width=26) (actual time=0.037..0.517 rows=1023 loops=1)
                     Hash Cond: (s.employee_id = e.employee_id)
                     ->  Seq Scan on sales s  (cost=0.00..18.00 rows=1000 width=17) (actual time=0.005..0.116 rows=1018 loops=1)
                     ->  Hash  (cost=2.31..2.31 rows=31 width=13) (actual time=0.025..0.025 rows=31 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 2kB
                           ->  Seq Scan on employees e  (cost=0.00..2.31 rows=31 width=13) (actual time=0.008..0.017 rows=31 loops=1)
               ->  Hash  (cost=2.51..2.51 rows=51 width=11) (actual time=0.050..0.050 rows=51 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 3kB
                     ->  Seq Scan on products p  (cost=0.00..2.51 rows=51 width=11) (actual time=0.004..0.035 rows=51 loops=1)
 Total runtime: 10.274 ms
(16 rows)

副問い合わせsubqueryを使わないSQL文

db_name=#
EXPLAIN ANALYSE SELECT
    e.employee_id,
    e.employee_name,
    TO_CHAR(s.sale_date, 'yyyy-mm') AS 年月,
    SUM((CASE WHEN s.quantity IS NULL THEN 0 ELSE s.quantity END) * (CASE WHEN p.price IS NULL THEN 0 ELSE p.price END)) AS 販売合計金額
FROM
    employees AS e                           
        LEFT OUTER JOIN      
    sales AS s ON  e.employee_id = s.employee_id
        LEFT OUTER JOIN
    products AS p ON  s.product_id = p.product_id
GROUP BY                                                                                                                                   
    e.employee_id,
    e.employee_name,
    TO_CHAR(s.sale_date, 'yyyy-mm')
ORDER BY                                                
    e.employee_id
;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=106.17..136.17 rows=1000 width=29) (actual time=9.589..10.292 rows=228 loops=1)
   ->  Sort  (cost=106.17..108.67 rows=1000 width=29) (actual time=9.581..9.645 rows=1023 loops=1)
         Sort Key: e.employee_id, e.employee_name, (to_char((s.sale_date)::timestamp with time zone, 'yyyy-mm'::text))
         Sort Method: quicksort  Memory: 104kB
         ->  Hash Left Join  (cost=5.84..56.34 rows=1000 width=29) (actual time=0.076..2.115 rows=1023 loops=1)
               Hash Cond: (s.product_id = p.product_id)
               ->  Hash Right Join  (cost=2.70..34.45 rows=1000 width=26) (actual time=0.033..0.692 rows=1023 loops=1)
                     Hash Cond: (s.employee_id = e.employee_id)
                     ->  Seq Scan on sales s  (cost=0.00..18.00 rows=1000 width=17) (actual time=0.004..0.163 rows=1018 loops=1)
                     ->  Hash  (cost=2.31..2.31 rows=31 width=13) (actual time=0.023..0.023 rows=31 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 2kB
                           ->  Seq Scan on employees e  (cost=0.00..2.31 rows=31 width=13) (actual time=0.006..0.015 rows=31 loops=1)
               ->  Hash  (cost=2.51..2.51 rows=51 width=11) (actual time=0.030..0.030 rows=51 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 3kB
                     ->  Seq Scan on products p  (cost=0.00..2.51 rows=51 width=11) (actual time=0.003..0.018 rows=51 loops=1)
 Total runtime: 10.351 ms
(16 rows)

感想

テスト用のデータベースでレコード数が少ないので大した差は出ていないが、
基本的にSQL文を組み立てるときは副問い合わせで中間結果を得たうえで
その結果を元に選択リスト内で再計算を行ったほうが実行スピードが短い、
という解釈でよい？？