站長阿川

站長阿川私房教材:
學 JavaScript 前端,帶作品集去面試!

站長精心設計,帶你實作 63 個小專案,得到作品集!

立即開始免費試讀!

前言

本文章將確認使用相同結果的 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)

效能差異要點😳

執行時間
UNION:1.305 ms
CASE:0.302 ms

Seq Scan(全表掃描)
UNION:(掃描 2 次)
 Seq Scan on items
 Seq Scan on items items_1
CASE:(掃描 1 次)
 Seq Scan on items

CASE 表達式的效能通常優於 UNION

即使 SQL 能取得相同結果,在使用 UNION 和 CASE 表達式時,其處理效能可能會有所差異。

CASE 表達式的特點

  • 僅需掃描一個資料表一次
  • 當條件較為簡單時,特別快速

UNION 的特點

  • 為了合併多個 SELECT,通常需要多次掃描資料表
  • 在條件簡單的情況下,相較於 CASE 表達式處理時間可能較長

使用 UNION 可能在某些情況下效能更佳

在 SQL 中若要結合多個條件來擷取資料,使用 CASE 表達式通常會更快,但在某些情況下使用 UNION 會有較好的效能。

例如以下情況:

  • 可以使用索引時
  • 在複雜條件下,將資料表分割會更有效率
    ※ 實際效能會根據資料表大小及條件而有所變動。檢查執行計畫並選擇最佳方法是最佳做法。🧐

總結

  • CASE 表達式在簡單條件下效能較高
  • UNION 當條件或索引適當時效率也可能較高
  • 檢查執行計畫以確認效能是最佳選擇

我仍在持續學習中,但希望能在實踐中持續分享我所學!🐊


原文出處:https://qiita.com/Kiita0430/items/f359f2888168ce648b54


共有 0 則留言


精選技術文章翻譯,幫助開發者持續吸收新知。
站長阿川

站長阿川私房教材:
學 JavaScript 前端,帶作品集去面試!

站長精心設計,帶你實作 63 個小專案,得到作品集!

立即開始免費試讀!