PostgreSQL索引最佳化：從慢查詢到毫秒級回應的6個關鍵調優策略
数据库
一個查詢跑了47秒，老闆說資料庫要換掉
你的訂單列表查詢從2秒變成了47秒，使用者瘋狂投訴；你加了索引，查詢反而更慢了；EXPLAIN ANALYZE輸出一堆看不懂的節點，不知道問題在哪。2026年，PostgreSQL索引最佳化 是從慢查詢到毫秒級回應的關鍵——選對索引型別、理解執行計畫、避免索引失效，能讓查詢效能提升1000倍。
本文將從PostgreSQL索引原理出發，帶你完成6個關鍵調優策略，從索引型別選擇到執行計畫分析，從部分索引到並行建索引，全鏈路實戰。
PostgreSQL索引核心概念
| 概念 | 說明 |
|---|---|
| B-tree索引 | 預設索引型別，適合等值查詢、範圍查詢、排序 |
| Hash索引 | 僅適合等值查詢，不儲存排序資訊，使用場景有限 |
| GIN索引 | 倒排索引，適合全文搜尋、JSONB、陣列等複合資料型別 |
| GiST索引 | 通用搜尋樹，適合地理空間資料、範圍型別 |
| BRIN索引 | 區塊範圍索引，適合物理有序的大表，體積極小 |
| 部分索引（Partial Index） | 只索引滿足條件的行，減少索引體積和維護成本 |
| 覆蓋索引（Covering Index） | INCLUDE列儲存額外列，實現Index-Only Scan |
| 並行建索引（CONCURRENTLY） | 不鎖表建立索引，生產環境必備 |
索引型別選擇決策樹
查詢型別判斷：
├── 等值查詢（=） → B-tree（預設）或Hash
├── 範圍查詢（>、<、BETWEEN） → B-tree
├── 全文搜尋（tsvector） → GIN
├── JSONB查詢（@>、?） → GIN
├── 陣列包含（@>、&&） → GIN
├── 地理空間（ST_Contains） → GiST
├── 範圍重疊（&&） → GiST
├── 物理有序大表（時間序列） → BRIN
└── 模糊搜尋（LIKE 'abc%'） → B-tree（前綴匹配）
問題分析：PostgreSQL索引最佳化的5大挑戰
- 索引選擇困難：5種索引型別各有適用場景，選錯型別導致查詢走全表掃描
- 索引失效：函式轉換、隱式型別轉換、OR條件導致索引無法使用
- 執行計畫難懂：EXPLAIN ANALYZE輸出複雜，Seq Scan vs Index Scan選擇不明
- 索引膨脹：頻繁UPDATE/DELETE導致索引碎片化，查詢效能退化
- 生產建索引鎖表：CREATE INDEX預設鎖寫操作，大表建索引導致服務不可用
分步實作：6個關鍵調優策略
策略1：B-tree索引——最常用的最佳化
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount DECIMAL(12,2),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ
);
INSERT INTO orders (user_id, status, total_amount, created_at)
SELECT
(random() * 100000)::bigint,
(ARRAY['pending','paid','shipped','completed','cancelled'])[floor(random()*5+1)::int],
(random() * 10000)::decimal(12,2),
NOW() - (random() * interval '365 days')
FROM generate_series(1, 5000000);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2025-01-01'
ORDER BY created_at DESC LIMIT 50;
策略2：GIN索引——JSONB與全文搜尋
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
attributes JSONB NOT NULL DEFAULT '{}',
tags TEXT[] DEFAULT '{}',
search_vector TSVECTOR
);
INSERT INTO products (name, attributes, tags)
SELECT
'Product ' || i,
jsonb_build_object(
'color', (ARRAY['red','blue','green','black','white'])[floor(random()*5+1)::int],
'size', (ARRAY['S','M','L','XL'])[floor(random()*4+1)::int],
'price', (random() * 500)::numeric(10,2),
'in_stock', random() > 0.3
),
ARRAY[(ARRAY['electronics','clothing','food','toys'])[floor(random()*4+1)::int]]
FROM generate_series(1, 1000000) AS i;
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_products_tags ON products USING GIN (tags);
EXPLAIN ANALYZE
SELECT * FROM products WHERE attributes @> '{"color": "red", "in_stock": true}';
EXPLAIN ANALYZE
SELECT * FROM products WHERE tags @> ARRAY['electronics'];
ALTER TABLE products ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
EXPLAIN ANALYZE
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'product & 999')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'product & 999')) DESC
LIMIT 20;
策略3：BRIN索引——時間序列大表
CREATE TABLE sensor_data (
id BIGSERIAL,
device_id INT NOT NULL,
temperature DECIMAL(5,2),
humidity DECIMAL(5,2),
recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);
CREATE TABLE sensor_data_2026_q1 PARTITION OF sensor_data
FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE sensor_data_2026_q2 PARTITION OF sensor_data
FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE sensor_data_2026_q3 PARTITION OF sensor_data
FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE sensor_data_2026_q4 PARTITION OF sensor_data
FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');
INSERT INTO sensor_data (device_id, temperature, humidity, recorded_at)
SELECT
(random() * 100)::int,
(15 + random() * 25)::decimal(5,2),
(30 + random() * 50)::decimal(5,2),
'2026-01-01'::timestamptz + (random() * interval '365 days')
FROM generate_series(1, 20000000);
CREATE INDEX idx_sensor_brin_recorded ON sensor_data
USING BRIN (recorded_at) WITH (pages_per_range = 32);
CREATE INDEX idx_sensor_brin_device ON sensor_data
USING BRIN (device_id) WITH (pages_per_range = 32);
EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE recorded_at BETWEEN '2026-03-01' AND '2026-03-15'
ORDER BY recorded_at;
SELECT
pg_size_pretty(pg_relation_size('idx_sensor_brin_recorded')) AS brin_size,
pg_size_pretty(pg_relation_size('sensor_data')) AS table_size;
策略4：部分索引與覆蓋索引
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
CREATE INDEX idx_orders_covering ON orders(user_id)
INCLUDE (status, total_amount, created_at);
EXPLAIN ANALYZE
SELECT user_id, status, total_amount, created_at
FROM orders WHERE user_id = 12345;
CREATE INDEX idx_orders_active_user ON orders(user_id, created_at DESC)
INCLUDE (status, total_amount)
WHERE status IN ('pending', 'paid', 'shipped');
EXPLAIN ANALYZE
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 12345
AND status IN ('pending', 'paid', 'shipped')
ORDER BY created_at DESC
LIMIT 20;
策略5：EXPLAIN ANALYZE深度解讀
EXPLAIN (ANYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, o.created_at
FROM orders o
JOIN (
SELECT user_id, MAX(created_at) AS last_order
FROM orders
WHERE status = 'completed'
GROUP BY user_id
) latest ON o.user_id = latest.user_id AND o.created_at = latest.last_order
WHERE o.total_amount > 1000
ORDER BY o.total_amount DESC
LIMIT 50;
SELECT pg_stat_user_indexes.schemaname,
pg_stat_user_indexes.relname AS table_name,
pg_stat_user_indexes.indexrelname AS index_name,
pg_stat_user_indexes.idx_scan AS index_scans,
pg_stat_user_indexes.idx_tup_read AS tuples_read,
pg_stat_user_indexes.idx_tup_fetch AS tuples_fetched,
pg_indexes.indexdef AS index_definition
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE pg_stat_user_indexes.relname = 'orders'
ORDER BY pg_stat_user_indexes.idx_scan ASC;
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
idx_scan AS scans, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
策略6：並行建索引與索引維護
CREATE INDEX CONCURRENTLY idx_orders_updated_at ON orders(updated_at);
CREATE INDEX CONCURRENTLY idx_products_name_trgm ON products
USING GIN (name gin_trgm_ops);
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
VACUUM ANALYZE orders;
SELECT indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;
CREATE OR REPLACE FUNCTION maintain_indexes()
RETURNS void AS $$
DECLARE
idx_record RECORD;
bloat_ratio NUMERIC;
BEGIN
FOR idx_record IN
SELECT schemaname, relname, indexrelname, indexrelid
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
LOOP
SELECT COALESCE(
(pg_relation_size(idx_record.indexrelid)::numeric /
NULLIF(pg_relation_size(
(SELECT relfilenode FROM pg_class WHERE oid = idx_record.indexrelid)
), 0)) * 100,
0
) INTO bloat_ratio;
IF bloat_ratio > 50 THEN
EXECUTE format('REINDEX INDEX CONCURRENTLY %I', idx_record.indexrelname);
RAISE NOTICE 'Reindexed % (bloat: %%%)', idx_record.indexrelname, bloat_ratio;
END IF;
END LOOP;
ANALYZE;
END;
$$ LANGUAGE plpgsql;
避坑指南
坑1：索引列上使用函式導致索引失效
-- ❌ 錯誤：對索引列使用函式，PostgreSQL無法使用B-tree索引
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-15';
-- ✅ 正確：使用範圍查詢代替函式轉換
SELECT * FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';
坑2：隱式型別轉換導致索引失效
-- ❌ 錯誤：varchar列與text比較時可能隱式轉換
SELECT * FROM orders WHERE status = 'pending'::text;
-- ✅ 正確：確保比較型別與列型別一致
SELECT * FROM orders WHERE status::text = 'pending';
-- 或更好的方式：直接使用相同型別
SELECT * FROM orders WHERE status = 'pending';
坑3：LIKE '%abc%'無法使用B-tree索引
-- ❌ 錯誤：前綴萬用字元無法使用B-tree索引
SELECT * FROM products WHERE name LIKE '%phone%';
-- ✅ 正確：使用pg_trgm擴充套件的GIN索引支援模糊搜尋
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name % 'phone';
坑4：OR條件導致索引選擇困難
-- ❌ 錯誤：OR條件可能導致最佳化器放棄索引
SELECT * FROM orders WHERE user_id = 123 OR status = 'pending';
-- ✅ 正確：使用UNION ALL拆分查詢
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 123;
坑5：過度索引導致寫入效能下降
-- ❌ 錯誤：給每列都加索引，INSERT/UPDATE效能暴跌
CREATE INDEX idx_orders_col1 ON orders(col1);
CREATE INDEX idx_orders_col2 ON orders(col2);
CREATE INDEX idx_orders_col3 ON orders(col3);
CREATE INDEX idx_orders_col4 ON orders(col4);
CREATE INDEX idx_orders_col5 ON orders(col5);
-- ✅ 正確：只建立查詢實際使用的索引，定期清理無用索引
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders' AND idx_scan = 0;
報錯排查
| 序號 | 報錯訊息 | 原因 | 解決方法 |
|---|---|---|---|
| 1 | could not create unique index, duplicate key |
唯一索引列存在重複值 | 先去重再建立索引，或使用CREATE UNIQUE INDEX ... WHERE |
| 2 | index row size exceeds maximum |
GIN索引的行過大 | 使用gin_pending_list_limit或最佳化資料 |
| 3 | concurrent index creation failed |
CONCURRENTLY建索引時表被修改 | 重試，檢查是否有長事務阻塞 |
| 4 | cannot create index on partitioned table |
分割區表不支援直接建索引 | 在每個分割區上分別建索引，或使用PG11+的分割區索引 |
| 5 | operator does not exist: jsonb @> text |
JSONB查詢操作符型別不匹配 | 確保右側也是jsonb型別：'{"key":"val"}'::jsonb |
| 6 | function gin_trgm_ops does not exist |
pg_trgm擴充套件未安裝 | CREATE EXTENSION pg_trgm; |
| 7 | out of memory |
大表建索引記憶體不足 | 增加maintenance_work_mem，或使用CONCURRENTLY |
| 8 | relation already exists |
索引名衝突 | 使用IF NOT EXISTS或更換索引名 |
| 9 | access exclusive lock |
建索引時鎖表 | 使用CREATE INDEX CONCURRENTLY避免鎖表 |
| 10 | index "xxx" is not valid |
CONCURRENTLY建索引失敗後索引無效 | DROP INDEX xxx;後重新建立 |
進階最佳化
1. 索引使用率監控看板
CREATE OR REPLACE VIEW index_health_dashboard AS
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan AS total_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
CASE
WHEN idx_scan = 0 THEN 'UNUSED'
WHEN idx_scan < 100 THEN 'LOW_USAGE'
ELSE 'ACTIVE'
END AS health_status,
COALESCE(
ROUND(
idx_tup_fetch::numeric / NULLIF(idx_scan, 0),
2
),
0
) AS avg_tuples_per_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY
CASE health_status
WHEN 'UNUSED' THEN 0
WHEN 'LOW_USAGE' THEN 1
ELSE 2
END,
pg_relation_size(indexrelid) DESC;
SELECT * FROM index_health_dashboard LIMIT 20;
2. 慢查詢自動捕獲與索引建議
ALTER SYSTEM SET log_min_duration_statement = 1000;
ALTER SYSTEM SET auto_explain.log_min_duration = 1000;
ALTER SYSTEM SET auto_explain.log_analyze = true;
SELECT pg_reload_conf();
CREATE TABLE slow_query_log (
id BIGSERIAL PRIMARY KEY,
query_text TEXT NOT NULL,
duration_ms NUMERIC NOT NULL,
plan_text TEXT,
captured_at TIMESTAMPTZ DEFAULT NOW(),
suggested_index TEXT
);
CREATE OR REPLACE FUNCTION capture_slow_query()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.duration_ms > 5000 THEN
INSERT INTO slow_query_log (query_text, duration_ms, suggested_index)
VALUES (
NEW.query_text,
NEW.duration_ms,
CASE
WHEN NEW.query_text ~* 'WHERE\s+\w+\s*=' THEN
'Consider index on equality column'
WHEN NEW.query_text ~* 'ORDER BY' THEN
'Consider index on sort column'
WHEN NEW.query_text ~* 'LIKE' THEN
'Consider pg_trgm GIN index'
ELSE 'Review EXPLAIN ANALYZE'
END
);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
3. 索引膨脹偵測與自動維護
CREATE OR REPLACE FUNCTION check_index_bloat(
p_schema TEXT DEFAULT 'public',
p_bloat_threshold NUMERIC DEFAULT 30
)
RETURNS TABLE(
table_name TEXT,
index_name TEXT,
index_size TEXT,
bloat_pct NUMERIC,
action TEXT
) AS $$
BEGIN
RETURN QUERY
SELECT
schemaname::TEXT,
indexrelname::TEXT,
pg_size_pretty(pg_relation_size(indexrelid))::TEXT,
COALESCE(
ROUND(
100.0 * (pg_relation_size(indexrelid) -
COALESCE(pg_stat_user_indexes.idx_tup_fetch, 0) * 8
) / NULLIF(pg_relation_size(indexrelid), 0),
1
),
0
),
CASE
WHEN COALESCE(
ROUND(
100.0 * (pg_relation_size(indexrelid) -
COALESCE(pg_stat_user_indexes.idx_tup_fetch, 0) * 8
) / NULLIF(pg_relation_size(indexrelid), 0),
1
),
0
) > p_bloat_threshold
THEN 'REINDEX CONCURRENTLY ' || quote_ident(indexrelname)
ELSE 'OK'
END::TEXT
FROM pg_stat_user_indexes
WHERE schemaname = p_schema
ORDER BY pg_relation_size(indexrelid) DESC;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM check_index_bloat('public', 30);
對比分析
| 維度 | B-tree | Hash | GIN | GiST | BRIN |
|---|---|---|---|---|---|
| 等值查詢 | ⭐快 | ⭐快 | ⚠️慢 | ⚠️慢 | ⚠️粗略 |
| 範圍查詢 | ⭐快 | ❌不支援 | ❌不支援 | ⚠️有限 | ⚠️粗略 |
| 排序 | ⭐支援 | ❌不支援 | ❌不支援 | ❌不支援 | ❌不支援 |
| 全文搜尋 | ❌不支援 | ❌不支援 | ⭐最佳 | ⚠️可用 | ❌不支援 |
| JSONB | ❌有限 | ❌不支援 | ⭐最佳 | ⚠️可用 | ❌不支援 |
| 地理空間 | ❌不支援 | ❌不支援 | ⚠️有限 | ⭐最佳 | ❌不支援 |
| 索引體積 | 中 | 小 | 大 | 大 | 極小 |
| 建立速度 | 快 | 快 | 慢 | 慢 | 極快 |
| 維護成本 | 低 | 低 | 高 | 高 | 極低 |
| 適用資料量 | 百萬~億 | 百萬 | 百萬~億 | 百萬 | 億級 |
總結：PostgreSQL索引最佳化不是「加個索引」那麼簡單——選對型別是前提，理解執行計畫是關鍵，避免索引失效是底線。6個關鍵策略層層遞進：1）B-tree基礎最佳化，2）GIN處理JSONB/全文搜尋，3）BRIN處理時間序列大表，4）部分索引和覆蓋索引減少體積，5）EXPLAIN ANALYZE深度解讀，6）並行建索引和自動維護。核心原則：索引不是越多越好——每個索引都有寫入成本，定期用
pg_stat_user_indexes清理無用索引，用CONCURRENTLY避免鎖表。
線上工具推薦
- JSON格式化：/zh-TW/json/format
- Hash計算：/zh-TW/encode/hash
- Base64編解碼：/zh-TW/encode/base64
本站提供瀏覽器本地工具，免註冊即可試用 →
#PostgreSQL#索引优化#慢查询#数据库性能#SQL调优#2026#执行计划