PostgreSQL 學習筆記：認識資料庫索引與 EXPLAIN ANALYZE

本篇為六角學院2026年node.js直播班中，關於資料庫索引的學習筆記整理，課中使用的資料庫為PostgreSQL，筆記內容來自課程、PostgreSQL官方文件整合而來。

索引是甚麼?

相當於一本書的目錄，記錄某章節在第幾頁。

下過索引後，資料庫在進行該索引條件的搜尋時，只需要找到該條件在索引中對應的資料庫位置，直接到那個位置拿資料即可。不用看跟條件不相關的資料，以此減少搜尋的時間。

建立索引

建立的重點

1. 優先替經常查詢，且能有效縮小結果範圍的欄位評估索引
2. 可下多條件(複合索引)，當多條件時，會優先從最左邊的欄位開始找資料，所以欄位的排列順序很重要。

什麼時候不需要建立索引?

1. 資料表本身很小
2. 查詢時會取出大部分資料
3. 欄位選擇性低，且沒有其他條件輔助
4. 幾乎不會被查詢的欄位
5. 資料需要大量且頻繁的寫入，但查詢收益有限
6. 已有功能重疊的索引
7. 條件寫法無法匹配索引

怎麼判斷資料庫有沒有用我的索引?

PostgreSQL提供兩種方式

EXPLAIN

EXPLAIN
SELECT * FROM bookings WHERE member_id = 1;
-- 可能得到
Index Scan using idx_bookings_member_id
(cost=0.42..8.44 rows=200 width=20)

以下使用 EXPLAIN ANALYZE 作為建立索引前後的範例

EXPLAIN ANALYZE
SELECT * FROM bookings WHERE member_id = 1;

Seq Scan on bookings  (cost=0.00..1834.00 rows=200 width=20) (actual time=0.015..18.432 rows=200 loops=1)
  Filter: (member_id = 1)
  Rows Removed by Filter: 99800
Planning Time: 0.089 ms
Execution Time: 20.104 ms

以這份資料表而言，當系統累積到十萬筆預約紀錄後，查詢某會員的預約紀錄就要花費20.104ms

針對這張表建立索引

CREATE
INDEX idx_bookings_member_id ON bookings(member_id);

EXPLAIN ANALYZE
SELECT * FROM bookings WHERE member_id = 1;

Index Scan using idx_bookings_member_id on bookings  (cost=0.42..8.44 rows=200 width=20) (actual time=0.021..0.115 rows=200 loops=1)
  Index Cond: (member_id = 1)
Planning Time: 0.102 ms
Execution Time: 0.203 ms

建立索引後，查詢某會員的預約紀錄時間僅需 0.203ms

此案例時間僅代表本次測試環境，重點是比較相同條件下建立索引前後的執行計畫與相對差異。

EXPLAIN ANALYZE在說甚麼？

JOIN的時候怎麼規劃索引?

對症下藥才能有效提升效能

• 先查看完整執行計畫
• 找出真正成本較高或掃描資料過多的節點
• 評估連接鍵、篩選欄位及資料量
• 再決定哪張表需要哪種索引

多表連接時，應透過執行計畫逐一確認各資料表的掃描與連接成本，再針對常用連接鍵或篩選欄位評估索引，而不是直接替所有欄位建立索引。

為什麼建立 Index 但資料庫還是逐筆搜尋？

資料庫本身也有思考

即使建立索引，但在執行查詢時，PostgreSQL 內會有 planner，負責比較不同計畫的估算成本，選擇它認為總成本最低的方案。

且因索引是獨立於資料表的資料結構，一般 Index Scan 還可能先從索引找到資料位置，再回原資料表取得完整欄位。

如果他判斷逐筆查詢成本較低，就不會使用索引。

PostgreSQL devises a query plan for each query it receives. Choosing the right plan to match the query structure and the properties of the data is absolutely critical for good performance, so the system includes a complex planner that tries to choose good plans. You can use the [**EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)** command to see what query plan the planner creates for any query. Plan-reading is an art that requires some experience to master, but this section attempts to cover the basics.

索引的成本有哪些?

索引不免費

前面提到過索引是獨立於資料表的資料結構，所以會占用儲存空間；資料新增、修改或刪除時，也可能需要額外同步更新索引。

可以說

建立索引就是犧牲部分增刪修，讓查的效能最大化。

其實每次的優化就是一個取捨的過程，假設有一張資料表在建立索引前，每次查詢都需要花費5s，寫入需要1.35s，建立索引後查詢只要0.05s，寫入變成1.47s。這樣該表的業務情境下是否值得建立索引呢?