本文章將確認使用相同結果的 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);
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;
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 都返回相同的結果👍
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)
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)
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
即使 SQL 能取得相同結果,在使用 UNION 和 CASE 表達式時,其處理效能可能會有所差異。
CASE 表達式的特點
UNION 的特點
在 SQL 中若要結合多個條件來擷取資料,使用 CASE 表達式通常會更快,但在某些情況下使用 UNION 會有較好的效能。
例如以下情況:
我仍在持續學習中,但希望能在實踐中持續分享我所學!🐊