還有一個很重要但我們還沒聊過的點，就是 window functions 查詢的效能。就算寫得再帥的查詢，如果沒顧到優化，還是會慢得像烏龜一樣。今天我們就來搞定這個問題！
window functions 超級靈活又強大。但這種靈活不只是一種禮物，也可能是效能的地雷。PostgreSQL 可不是什麼「魔法」系統，它還是需要資源來處理資料。尤其當你把 window functions 用在超大 table 上時，你的查詢可能會像在原地跑馬拉松一樣。
優化可以讓你：
- 加速處理大量資料的查詢。
- 減少 database 的負擔。
- 讓你的查詢對 server（還有一起用 database 的同事）更友善。
來吧，直接 dive in 看看怎麼讓你的查詢飛起來，像賽車一樣快！
window functions 的基本運作
在我們開始優化之前，先搞清楚到底是什麼拖慢了查詢。PostgreSQL 處理 window functions 的流程大致是這樣：
- 如果
OVER()裡有ORDER BY，就會先排序資料。 - 針對每一列，在指定的 window frame 或 group 裡做運算。
- 每一列都會回傳一個結果。
想像一下，我們有個 sales table，裡面有一千萬筆資料。如果查詢沒加任何 filter，PostgreSQL 就會一筆一筆處理。這已經不是馬拉松，是無限跑步機了啦。
window functions 怎麼加速？
- 用 index 加速排序
大部分 window functions 都會在 OVER() 裡用 ORDER BY 來決定列的順序。這代表 PostgreSQL 需要先把資料排序，才能跑 window function。
如果你在 ORDER BY 用到的 column（或多個 column）上有 index，PostgreSQL 排序就會快很多。
範例
CREATE INDEX idx_sales_date ON sales (sale_date);
現在，如果你寫查詢用 sale_date 排序，index 就會派上用場：
SELECT
sale_date,
product_id,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
如果 sale_date 沒有 index，每次查詢都要做很重的排序 —— PostgreSQL 會很崩潰地找最快的排序方式。
- 用
WHERE過濾資料
縮小資料量是優化的關鍵。如果你不需要處理全部一千萬筆資料，只要最近一年的就好 —— 那就用 WHERE 把範圍縮小！
範例
SELECT
sale_date,
product_id,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';
這就像你用濾網把髒水過濾掉，只留下有用的資訊。
- 選對 window frame
用 window functions 做聚合（像 SUM()）時，選對 window frame 很重要。如果你用預設的 frame（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），PostgreSQL 會把所有到目前為止的列都算進來。對大 table 來說，這很沒效率。
範例：用 ROWS
如果你只想包含目前列前面幾筆資料，直接用 ROWS 指定：
SELECT
sale_date,
product_id,
SUM(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_sum
FROM sales;
這樣 PostgreSQL 每次只處理三筆資料（前兩筆＋現在這筆）。比起預設要處理上百筆，效率好多了。
- 減少 window functions 數量
每個 window function，PostgreSQL 都會分開處理。如果你用了好幾個 window function，PostgreSQL 可能會為每個都排序一次，這會拖慢速度。但如果 window 的參數（像 PARTITION BY 和 ORDER BY）一樣，PostgreSQL 可以更有效率地處理。
範例：用同一個 window 優化
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;
這兩個 function（SUM() 跟 ROW_NUMBER()）用的是同一個 window。PostgreSQL 只會排序一次 —— 超讚。
- table partition
如果你的 table 超大，可以考慮把它物理上切成小塊。PostgreSQL 支援 partitioned table，讓資料分散在不同 segment。這樣處理起來會快很多。
建立 partitioned table 的範例
CREATE TABLE sales_partitioned (
sale_date DATE NOT NULL,
product_id INT NOT NULL,
amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);
接下來可以依年份建立 partition：
CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');這樣你用 WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31' 查詢時，PostgreSQL 只會去找對應的 partition。
partition table 的細節，課程後面還會再聊 :P
- 避免多餘資料（
SELECT只撈需要的）
只選你 window function 跟結果需要的 column。如果只需要 product_id、sale_date 跟 amount，就不要把整個 table 的所有資料都拉進來。
「省省用」查詢範例
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
資料越少，PostgreSQL 工作量就越小。
- 用 materialization（
MATERIALIZED VIEW）
如果你常常重複做一樣的 window function 計算，可以把結果存成 materialized view。Materialized View 會把資料寫到硬碟，這樣就不用每次都重算複雜查詢。
建立 materialized view 的範例
CREATE MATERIALIZED VIEW sales_running_total AS
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
之後直接查這個 view 就好：
SELECT * FROM sales_running_total WHERE product_id = 10;
- 用
EXPLAIN跟EXPLAIN ANALYZE規劃查詢
跟 SQL 其他地方一樣，你可以用 EXPLAIN 或 EXPLAIN ANALYZE 來看 PostgreSQL 怎麼執行你的查詢 —— 也能找出瓶頸在哪。
查詢分析範例
EXPLAIN ANALYZE
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
這個工具會告訴你 PostgreSQL 哪裡花最多時間，你就能針對瓶頸優化。
window functions 超強大，但用的時候要小心。想要快？記得用 index、加 filter、別忘了 partition，也可以用 materialized view。PostgreSQL 最愛你用得聰明又有效率啦！
GO TO FULL VERSION