檢查在 PostgreSQL 中產生相同結果的查詢的執行計劃

前言

本文章將確認使用相同結果的 UNION 和 CASE 標準來執行計畫，並檢視哪一種的效能較佳！
若您不清楚如何確認執行計畫，請參考「PostgreSQL 中的執行計畫確認」🙌

準備

首先，準備用於查詢的資料表☝️

-- 建立資料表
CREATE TABLE Items
(   item_id     INTEGER  NOT NULL, 
       year     INTEGER  NOT NULL, 
  item_name     CHAR(32) NOT NULL, 
  price_tax_ex  INTEGER  NOT NULL, 
  price_tax_in  INTEGER  NOT NULL, 
  PRIMARY KEY (item_id, year));

-- 新增資料
INSERT INTO Items VALUES(100,  2000,   '杯子'        ,500,   525);
INSERT INTO Items VALUES(100,   2001,   '杯子'        ,520,   546);
INSERT INTO Items VALUES(100,   2002,   '杯子'        ,600,   630);
INSERT INTO Items VALUES(100,   2003,   '杯子'        ,600,   630);
INSERT INTO Items VALUES(101,   2000,   '湯匙'      ,500,   525);
INSERT INTO Items VALUES(101,   2001,   '湯匙'      ,500,   525);
INSERT INTO Items VALUES(101,   2002,   '湯匙'      ,500,   525);
INSERT INTO Items VALUES(101,   2003,   '湯匙'      ,500,   525);
INSERT INTO Items VALUES(102,   2000,   '刀子'        ,600,   630);
INSERT INTO Items VALUES(102,   2001,   '刀子'        ,550,   577);
INSERT INTO Items VALUES(102,   2002,   '刀子'        ,550,   577);
INSERT INTO Items VALUES(102,   2003,   '刀子'        ,400,   420);

使用 UNION 和 CASE 取得相同結果

UNION

SELECT item_name, year, price_tax_ex AS price
FROM Items
WHERE year <= 2001
UNION
SELECT item_name, year, price_tax_in AS price
FROM Items
WHERE year >= 2002
ORDER BY item_name, year;

CASE

SELECT item_name, year,
CASE WHEN year <= 2001 THEN price_tax_ex
     WHEN year >= 2002 THEN price_tax_in END AS price
FROM Items;

執行結果（UNION / CASE）

如下所示，UNION 和 CASE 都返回相同的結果👍

              item_name               | year | price 
--------------------------------------+------+-------
 杯子                                 | 2000 |   500
 杯子                                 | 2001 |   520
 杯子                                 | 2002 |   630
 杯子                                 | 2003 |   630
 湯匙                                 | 2000 |   500
 湯匙                                 | 2001 |   500
 湯匙                                 | 2002 |   525
 湯匙                                 | 2003 |   525
 刀子                                 | 2000 |   600
 刀子                                 | 2001 |   550
 刀子                                 | 2002 |   577
 刀子                                 | 2003 |   420
(12 rows)

確認執行計畫

UNION

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=51.02..51.78 rows=306 width=140) (actual time=0.775..0.778 rows=12 loops=1)
   Sort Key: items.item_name, items.year
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=35.33..38.39 rows=306 width=140) (actual time=0.489..0.497 rows=12 loops=1)
         Group Key: items.item_name, items.year, items.price_tax_ex
         Batches: 1  Memory Usage: 37kB
         ->  Append  (cost=0.00..33.03 rows=306 width=140) (actual time=0.377..0.396 rows=12 loops=1)
               ->  Seq Scan on items  (cost=0.00..15.75 rows=153 width=140) (actual time=0.376..0.380 rows=6 loops=1)
                     Filter: (year <= 2001)
                     Rows Removed by Filter: 6
               ->  Seq Scan on items items_1  (cost=0.00..15.75 rows=153 width=140) (actual time=0.009..0.011 rows=6 loops=1)
                     Filter: (year >= 2002)
                     Rows Removed by Filter: 6
 Planning Time: 3.541 ms
 Execution Time: 1.305 ms
(15 rows)

CASE

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on items  (cost=0.00..16.90 rows=460 width=140) (actual time=0.132..0.136 rows=12 loops=1)
 Planning Time: 0.505 ms
 Execution Time: 0.302 ms
(3 rows)