什么是執(zhí)行計(jì)劃
對于每個(gè)收到的查詢請求，PostgreSQL 都會(huì)為其上設(shè)置執(zhí)行計(jì)劃（查詢計(jì)劃），能夠正確的通過查詢語句結(jié)構(gòu)與數(shù)據(jù)結(jié)構(gòu)進(jìn)行查詢計(jì)劃的設(shè)定會(huì)顯著提升系統(tǒng)性能响驴，對于我們來說，經(jīng)常會(huì)通過查詢計(jì)劃來找出慢查詢的原因，可以說是最重要的性能工具了磨德。當(dāng)然，執(zhí)行計(jì)劃的分析與優(yōu)化是很難的吆视，所以本篇嘗試覆蓋一些基礎(chǔ)的點(diǎn)典挑。
一般來說，使用 EXPLAIN 可以顯示查詢計(jì)劃了啦吧，當(dāng)然你也可以使用一些 option 對其進(jìn)行改變您觉，例如常見的 ANALYZE COSTS 等等。最常用的可能是 ANALYZE 了授滓，因?yàn)槠鋵?shí)通過實(shí)際執(zhí)行的 SQL 并且獲取真正的執(zhí)行計(jì)劃琳水，所以你可以看到具體的時(shí)間花費(fèi)以及返回行數(shù)，如果 INSERT 或者 DELETE 了數(shù)據(jù)般堆，那就會(huì)修改數(shù)據(jù)庫在孝。所以你可以使用 BEGIN 將 EXPLAIN ANALYZE 包入一個(gè)事務(wù)，執(zhí)行完后回滾淮摔。如果你不使用 ANALYZE PostgreSQL 會(huì)根據(jù)一些隨機(jī)的樣本浑玛，已有的性能數(shù)據(jù)，或者估計(jì)等手段去推測 cost噩咪，所以是不精確的顾彰，但是足夠分析性能极阅，特別是慢查詢可能會(huì)等待很久，使用 ANALYZE 會(huì)很沒有效率涨享。
其實(shí)官方文檔是最好的解釋筋搏，對其他 options 或者想深入學(xué)習(xí)的，還是請參考厕隧。
輸出結(jié)果的解釋
我們先來實(shí)際試試 EXPLAIN：
CREATE SEQUENCE user_id_seq;
CREATE TABLE users (
id BIGINT NOT NULL DEFAULT nextval('user_id_seq'),
type VARCHAR(10) NOT NULL,
name VARCHAR(128) NOT NULL,
address TEXT,
married BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (id)
);
# 準(zhǔn)備十萬條測試數(shù)據(jù)
INSERT INTO users (type, name, address)
SELECT 'testing', left(md5(i::text), 10), left(md5(random()::text), 50)
FROM generate_series(1, 100000) s(i);
所以我們進(jìn)行 EXPLAIN 的結(jié)果如下：
EXPLAIN select * from users;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on users (cost=0.00..1600.80 rows=26680 width=369)
(1 row)
-
Seq Scan表示我們常說的全表掃描奔脐，從頭到尾的順序掃描。 -
cost=0.00..1600.80表示花費(fèi)吁讨，0.00表示獲取第一行需要多少 cost,1600.80則表示全部返回需要多少 cost髓迎。 -
rows表示返回多少行 -
width表示平均每行多少字節(jié)（你可以用 rows 和 width 來估算數(shù)據(jù)大小）
在講 cost 之前，我們發(fā)現(xiàn) rows 的數(shù)據(jù)并不正確建丧，這張表里有 100,000 條數(shù)據(jù)排龄，怎么查詢計(jì)劃里面只有 26,680 條？還記得我們之前提到的 EXPLAIN 會(huì)根據(jù)很多性能數(shù)據(jù)推測翎朱，因?yàn)檫@張表剛剛建立橄维，數(shù)據(jù)不充分所導(dǎo)致結(jié)果偏差很大。你可以做一個(gè) SELECT COUNT(*) 后再試試剛才的 EXPLAIN 語句拴曲，結(jié)果會(huì)有很大的不同争舞。
cost 并不與我們的實(shí)際執(zhí)行時(shí)間掛鉤，只是 PostgreSQL 用來描述執(zhí)行成本的單位澈灼，這個(gè)東西不能和任何時(shí)間單位進(jìn)行換算竞川，PSQL 是這樣定義的，例如 seq_page_cost 順序的掃描一頁 的代價(jià)就是 1.0, 處理一行數(shù)據(jù)的代價(jià)是 0.01叁熔，當(dāng)然這些常量你也可以自己修改委乌，其他的表示可以參考官方文檔，一般情況下者疤，你不需要特別了解 cost 到底是什么福澡，它只要使你能進(jìn)行性能分析就足夠了叠赦。
下來我們看看使用了 ANALYZE 后的效果：
EXPLAIN ANALYZE select * from users;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on users (cost=0.00..2334.00 rows=100000 width=77) (actual time=0.013..631.094 rows=100000
loops=1)
Planning time: 0.068 ms
Execution time: 1249.210 ms
(3 rows)
結(jié)果中有了具體的查詢時(shí)間以及返回?cái)?shù)據(jù)的總時(shí)間驹马，你也可以使用 buffers 去查看緩存命中的情況：
EXPLAIN (ANALYZE true, BUFFERS true) select * from users;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on users (cost=0.00..2334.00 rows=100000 width=77) (actual time=0.020..634.054 rows=100000
loops=1)
Buffers: shared hit=1334
Planning time: 0.223 ms
Execution time: 1254.985 ms
(4 rows)
可以看到命中 1334 個(gè) block，特別是你做了數(shù)據(jù)表的 prewarm 后除秀，查看緩存的命中也是很有必要的糯累，以此可以來來評估 pg_prewarm 的效果。
掃描
PostgreSQL 的掃描有三種册踩，順序掃描 seq scan泳姐，索引掃描 index scan 與位圖掃描 bitmap scan，順序掃描也稱為全表掃描暂吉，是把表的所有數(shù)據(jù)塊從頭到尾掃一遍胖秒，然后獲取符合條件的數(shù)據(jù)缎患，而索引掃描是在索引中找出需要的數(shù)據(jù)的位置，然后再去將數(shù)據(jù)取出的過程阎肝，以下有兩個(gè)例子：
EXPLAIN select * from users;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on users (cost=0.00..2334.00 rows=100000 width=77)
(1 row)
EXPLAIN select * from users where id = 9999;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.29..8.31 rows=1 width=77)
Index Cond: (id = 9999)
(2 rows)
第一個(gè)查詢將會(huì)掃描表中的 100,000 條數(shù)據(jù)挤渔，由于我們沒有 where 子句，所以這個(gè)查詢計(jì)劃只有 seq scan风题，而第二個(gè)查詢直接在索引中進(jìn)行搜索（還記得第二篇中的 B-Tree Index 嗎判导？對它進(jìn)行搜索的過程就是 Index Scan）。
而位圖掃描 bitmap scan 也是一種走索引掃描的方式沛硅，原理是進(jìn)行索引掃描眼刃，把滿足條件的行的指針 tuple-pointer 立刻取出，存放在內(nèi)存中的位圖里摇肌，當(dāng)掃描結(jié)束后擂红，再對位圖中的 tuple-pointer 去讀取實(shí)際數(shù)據(jù)塊中的數(shù)據(jù)。這種查詢方式常常用于非等值查詢的情況下朦蕴，而且如果走了兩個(gè)索引篮条，可以將兩個(gè)索引的位圖進(jìn)行 and 與 or 的計(jì)算，合并成新的位圖吩抓，再根據(jù)其位置取出實(shí)際數(shù)據(jù)涉茧，這個(gè)過程中每個(gè)數(shù)據(jù)塊只在掃描中被讀取了一次。比如說疹娶，我們對某個(gè)索引進(jìn)行一個(gè)條件查詢：
# 手動(dòng)關(guān)閉這兩個(gè) scan伴栓，強(qiáng)制使用 bitmap scan
SET enable_indexscan = off;
SET enable_seqscan = off;
# 建立索引
CREATE INDEX users_name ON users (name);
# 進(jìn)行查詢
EXPLAIN SELECT * FROM users WHERE name IN ('a', 'b', 'c');
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=13.28..24.89 rows=3 width=77)
Recheck Cond: ((name)::text = ANY ('{a,b,c}'::text[]))
-> Bitmap Index Scan on users_name (cost=0.00..13.28 rows=3 width=0)
Index Cond: ((name)::text = ANY ('{a,b,c}'::text[]))
(4 rows)
Recheck 的原因是由于 MVCC，我們還需要將數(shù)據(jù)讀出后雨饺，再檢查一下條件钳垮。
EXPLAIN SELECT * FROM users WHERE name IN ('a', 'b', 'c') OR name IN ('p', 'b', 'k');
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=26.56..49.45 rows=6 width=77)
Recheck Cond: (((name)::text = ANY ('{a,b,c}'::text[])) OR ((name)::text = ANY ('{p,b,k}'::text[])
))
-> BitmapOr (cost=26.56..26.56 rows=6 width=0)
-> Bitmap Index Scan on users_name (cost=0.00..13.28 rows=3 width=0)
Index Cond: ((name)::text = ANY ('{a,b,c}'::text[]))
-> Bitmap Index Scan on users_name (cost=0.00..13.28 rows=3 width=0)
Index Cond: ((name)::text = ANY ('{p,b,k}'::text[]))
(7 rows)
這是一個(gè) bitmap scan OR 的例子，我們使用 OR 將兩個(gè) bitmap 合并额港，再最后進(jìn)行數(shù)據(jù)讀取饺窿。
條件、排序等
對于條件移斩，排序肚医，limit 等操作，也有相應(yīng)的查詢計(jì)劃向瓷，比起 scan 來容易理解肠套，比如下面這個(gè)查詢計(jì)劃：
EXPLAIN SELECT * FROM users WHERE id > 100 ORDER BY created_at DESC LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------
Limit (cost=4742.89..4742.91 rows=10 width=77)
-> Sort (cost=4742.89..4992.65 rows=99904 width=77)
Sort Key: created_at
-> Seq Scan on users (cost=0.00..2584.00 rows=99904 width=77)
Filter: (id > 100)
(5 rows)
limit sort filter 都在計(jì)劃中，可以看到排序是非常貴的操作（cost=4742.89..4992.65）猖任，合理的使用排序可以顯著提高性能你稚。
Join
PostgreSQL 有三種 JOIN 的方式 nestloop join hash join merge join，PostgreSQL 會(huì)根據(jù)數(shù)據(jù)量的大小與性能的統(tǒng)計(jì)數(shù)據(jù)等選擇合適的方式。
nestloop join 也叫嵌套循環(huán)刁赖，顧名思義搁痛，就是使用嵌套循環(huán)的方式，先在外表獲取數(shù)據(jù)宇弛，再去內(nèi)表尋找進(jìn)行匹配落追。所以這種方式并不適合數(shù)據(jù)量比較大的情況，例如行數(shù)上萬的情況涯肩。顯而易見復(fù)雜度是 O(M*N)轿钠，M 和 N 是需要 join 的數(shù)據(jù)量，但是即使看起來 nestloop join 不是很高效病苗，但是這種方式是可以計(jì)算任何的多表聯(lián)查情況的疗垛，你可以認(rèn)為這是一個(gè)最基本的功能。
但是如果 inner 表中用于 join 的字段存在索引硫朦，那么 nestloop join 的效率就會(huì)稍微好一點(diǎn)贷腕，我們知道 B-Tree 的查詢復(fù)雜度是 log(N)，所以在 index scan 中 O(M*log(N)) 是優(yōu)于 nestloop join 的 O(M*N) 的咬展。
以下是一個(gè) nestloop JOIN 的實(shí)例泽裳，并且?guī)в?Index Scan，可以看到在對 users 進(jìn)行全表掃描后破婆，對每一條數(shù)據(jù)進(jìn)行 JOIN 去查詢 users_2 表是否存在滿足條件的數(shù)據(jù)涮总，即使用 Index Cond。
# 強(qiáng)制走 nestloop
SET enable_nestloop = ON;
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
EXPLAIN SELECT * FROM users JOIN users_2 ON users.id = users_2.id;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.42..79748.00 rows=100000 width=154)
-> Seq Scan on users (cost=0.00..2334.00 rows=100000 width=77)
-> Index Scan using users_2_pkey on users_2 (cost=0.42..0.76 rows=1 width=77)
Index Cond: (id = users.id)
hash join 比 nestloop 先進(jìn)了一些祷舀，一般來說 PostgreSQL 查詢優(yōu)化器會(huì)使用兩個(gè)表中較小的表瀑梗，將其放入內(nèi)存建立一個(gè)散列表，然后再掃描大表并查詢內(nèi)存中的散列表裳扯，然后找出數(shù)據(jù)抛丽。這種情況很適合 JOIN 時(shí)小的表完全放入內(nèi)存之中，但是如果小的數(shù)據(jù)表無法全部放入內(nèi)存中饰豺，PostgreSQL 會(huì)將其分區(qū)并將無法放入內(nèi)存的部分寫入臨時(shí)段亿鲜，那就自然需要消耗大量的 I/O 成本。
# 走 hash join
SET enable_nestloop = OFF;
SET enable_hashjoin = ON;
SET enable_mergejoin = OFF;
EXPLAIN SELECT * FROM users JOIN users_2 ON users.id = users_2.id;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=4854.00..36487.00 rows=100000 width=154)
Hash Cond: (users_2.id = users.id)
-> Seq Scan on users_2 (cost=0.00..11667.00 rows=500000 width=77)
-> Hash (cost=2334.00..2334.00 rows=100000 width=77)
-> Seq Scan on users (cost=0.00..2334.00 rows=100000 width=77)
這個(gè)查詢計(jì)劃就非常清楚了冤吨，先對表 users 進(jìn)行了 seq scan 然后將其結(jié)果放入內(nèi)存中的 hash蒿柳，因?yàn)楸?users 只有100,000 條數(shù)據(jù)，是一個(gè)小表锅很，再對 users_2 表進(jìn)行 seq scan其馏，然后去 hash 中進(jìn)去 hash join凤跑，所以它的結(jié)果的總 cost 是小于 nestloop 的爆安，即：36487.00 < 79748.00，但是啟動(dòng)時(shí)間（第一條數(shù)據(jù)返回的時(shí)間）卻大于 nestloop 即 4854.00 > 0.42仔引，因?yàn)樾枰?hash table扔仓。
一般來說 hash join 的效果是好于 merge join 的褐奥，但是如果需要 JOIN 的字段上有索引，或者已經(jīng)被進(jìn)行過排序（例如說 JOIN 一個(gè)結(jié)果表）翘簇，那么久不需要為其建立 hash 了撬码，這時(shí)候 merge join 的性能會(huì)好于 hash join。所以在我們上面的例子中版保，如果打開全部的 JOIN 選項(xiàng)呜笑，PostgreSQL 會(huì)默認(rèn)的去走更高效的 merge join。
# 打開所有的 JOIN 開關(guān)
SET enable_nestloop = ON;
SET enable_hashjoin = ON;
SET enable_mergejoin = ON;
EXPLAIN SELECT * FROM users JOIN users_2 ON users.id = users_2.id;
QUERY PLAN
--------------------------------------------------------------------------------------------
Merge Join (cost=1.60..9351.82 rows=100000 width=154)
Merge Cond: (users.id = users_2.id)
-> Index Scan using users_pkey on users (cost=0.29..3941.29 rows=100000 width=77)
-> Index Scan using users_2_pkey on users_2 (cost=0.42..19666.42 rows=500000 width=77)
因?yàn)樗饕呀?jīng)是排好序的彻犁，所以兩個(gè) Index Scan 直接掃描了索引叫胁，可以看到預(yù)計(jì)的 cost 是三種情況中最好的。
關(guān)于性能優(yōu)化
我們在之前的例子中汞幢，使用了類似 enable_mergejoin 的參數(shù)來控制 PostgreSQL 強(qiáng)制執(zhí)行不同的查詢計(jì)劃驼鹅，那是因?yàn)槿绻贿@樣做，PostgreSQL 的優(yōu)化器會(huì)選擇最優(yōu)的情況進(jìn)行執(zhí)行具滴，例如 JOIN 的例子中往往會(huì)走 merge join浓领。
在通常情況下翼虫，PostgreSQL 都不會(huì)走錯(cuò)查詢計(jì)劃，有時(shí)候走錯(cuò)往往發(fā)生在剛剛啟動(dòng)數(shù)據(jù)庫服務(wù)或者使用一張新表的時(shí)候买乃，因?yàn)槟菚r(shí)候還沒有足夠多的統(tǒng)計(jì)信息來幫助優(yōu)化器進(jìn)行決策。所以钓辆，在項(xiàng)目中为牍，如果沒有特殊情況，不需要手動(dòng)的使用這種 enable_mergejoin 參數(shù)來強(qiáng)制執(zhí)行計(jì)劃岩馍。
另外碉咆，PostgreSQL 可以使用 analyze 命令來收集統(tǒng)計(jì)表的信息，這些統(tǒng)計(jì)信息會(huì)幫助優(yōu)化器選擇合適的執(zhí)行計(jì)劃蛀恩。但是一般來說疫铜，PostgreSQL 默認(rèn)會(huì)運(yùn)行一個(gè) autovacuum 的進(jìn)程進(jìn)行 vacuum 操作，并自動(dòng)的分析所有表双谆，有時(shí)候我們也會(huì)關(guān)閉這個(gè)進(jìn)程壳咕，周期性的、手動(dòng)的進(jìn)行 vacuum 與 analyze （比如在半夜）顽馋。但是谓厘，如果沒有特殊的情況，我傾向于使用 autovacuum 避免手動(dòng) analyze 時(shí)容易犯的人為錯(cuò)誤寸谜。如果你想進(jìn)一步學(xué)習(xí)竟稳，可以參考官方文檔，我們也會(huì)在下來的更新中具體說說 VACUUM。
我們面對慢查詢第一件事就是分析執(zhí)行計(jì)劃他爸，例如常常做的套路就是將 Seq Scan 變?yōu)?Index Scan聂宾，或者取消掉 Nestloop 這種。一般情況下诊笤，如果查詢語句已經(jīng)無法改變系谐，通過執(zhí)行計(jì)劃獲取到具體的執(zhí)行路徑與成本，再進(jìn)行針對性的優(yōu)化讨跟。
