根據執行計劃分析優化查詢：EXPLAIN ANALYZE

來了來了，真相時刻：SQL 查詢不是單純的程式碼行，而是你跟資料庫的對話。如果你輕聲細語地對它說 "SELECT *"，資料庫大概會乖乖聽話把資料吐給你。但如果你丟給它一篇亂七八糟的 SQL 小說，資料庫可能會想很久……然後開始變慢。

查詢優化就是要用資料庫聽得懂又簡潔的語言跟它溝通。寫得好的查詢，執行起來又快又不會拖累系統，也不會影響其他 process。反過來，寫得爛的查詢會讓整個系統卡住：資料庫 CPU 跟記憶體吃爆，硬碟一直在讀寫，連用資料庫的 app 都會 lag。

EXPLAIN ANALYZE 就是用來找出這些卡關點，讓你知道查詢到底卡在哪裡。這就像診斷一樣——沒診斷就沒辦法救效能。

查詢常見問題怎麼抓？

現在來認識一下那些讓效能變爛的嫌疑犯。我們要用 EXPLAIN ANALYZE 這把武器。

問題 1：全表掃描（Seq Scan）

Seq Scan（全表掃描）就是 PostgreSQL 一筆一筆把整張表都翻過一遍找資料。表小還好，表大就超痛苦。

怎麼知道有沒有 Seq Scan？ 直接用 EXPLAIN ANALYZE 看看。舉例：

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

結果可能長這樣（注意 Seq Scan）：

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

怎麼解？

如果還沒建 index，就幫 student_id 建一個：

CREATE INDEX idx_student_id ON students(student_id);

然後再跑一次 EXPLAIN ANALYZE。你應該會看到 Index Scan 取代 Seq Scan。

問題 2：條件選擇性太低

選擇性（selectivity）就是要處理多少筆資料才能找到你要的。如果你的 filter 幾乎把整張表都選進來，index 也救不了你。

低選擇性查詢範例：

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

如果有 90% 學生都在 Computer Science，這查詢就算有 program index 也可能還是 Seq Scan。

怎麼優化？

1. 重新檢查查詢邏輯：也許你該加點 filter 條件讓查詢更精確。
2. 確保 table 統計資料是新的（這樣 PostgreSQL 才能正確評估選擇性）：

ANALYZE students;

3. 如果查詢硬是用 index 但其實該全表掃描，可以強制 PostgreSQL 用 index：

SET enable_seqscan = OFF;

問題 3：多餘的排序操作

排序（Sort）很花資源，尤其資料大到塞不進記憶體時。最常見的就是 ORDER BY。

問題範例：

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

你可能會看到這種：

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

怎麼讓排序快一點？ 如果你常常照某個欄位排序，可以建 index：

CREATE INDEX idx_last_name ON students(last_name);

這樣 PostgreSQL 可以直接用 index 把資料照順序抓出來，省掉額外排序。

問題 4：沒有限制（LIMIT）

如果你用 SELECT 查資料沒加限制，查詢可能會把整張表都掃一遍，就算你只要第一筆。

像這樣：

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

如果資料表有一百萬筆，gpa > 3.5 又選到 80%，你就等著慢慢等吧。

如果你只要前 10 名學生，用 LIMIT：

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

另外，LIMIT 可以搭配 OFFSET 做分頁。

執行參數怎麼調：SET

PostgreSQL 的 SET 指令是用來改 session 或查詢的參數。這有點像臨時設定，只影響你現在這個連線。

簡單說，SET 就是隨時調整 PostgreSQL「心情」，不用動到全域設定。

什麼時候會用到？

• 跑報表前換語言或日期格式。
• 某個大查詢臨時加大記憶體。
• 大量匯入時暫時關掉 log。
• 臨時換 schema 搜尋路徑（search_path）。
• 控管安全性（例如暫時降權）。

基本語法

SET 參數 = 值;

要看目前參數值：

SHOW 參數;

要恢復預設值：

RESET 參數;

綜合優化範例

假設我們有個任務：找出 Computer Science 裡 GPA 最高的最後 10 位學生。原始查詢如下：

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. 查詢分析： 先跑 EXPLAIN ANALYZE：

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   如果你看到全表掃描加排序，就是該優化的訊號。

2. 針對 filter 跟排序建複合 index：

   建一個同時包含兩個欄位的 index：

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. 檢查優化效果：

   再跑一次 EXPLAIN ANALYZE。這次查詢應該會用到你剛建的 index，省掉排序和全表掃描。

查詢優化步驟

1. 先分析現有執行計劃。 用 EXPLAIN ANALYZE 找出卡關的操作。

2. 找出瓶頸。 看哪些計劃節點最花時間或資源。

3. 建 index。 檢查哪些欄位有 filter 或排序，然後建對應的 index。

4. 減少資料量。 用 LIMIT、OFFSET，還有精確的 filter 條件。

5. 更新統計資料。 跑 ANALYZE，讓 PostgreSQL 拿到最新的資料分布資訊。

6. 測試變更。 優化後再跑 EXPLAIN ANALYZE，確認效能真的有變好。

接下來呢？

你剛剛已經衝完一輪查詢優化速成班，恭喜啦！多玩玩 EXPLAIN ANALYZE，你會越來越懂 PostgreSQL 裡面的運作細節。記住：再神的 index 也救不了太複雜或寫得很模糊的查詢。SQL 跟其他語言一樣，最愛的就是清楚明白。