介紹
了解 PostgreSQL 執(zhí)行計(jì)劃對于開發(fā)人員來說是一項(xiàng)關(guān)鍵技能，執(zhí)行計(jì)劃是我們優(yōu)化查詢宝剖，驗(yàn)證我們的優(yōu)化查詢是否確實(shí)按照我們期望的方式運(yùn)行的重要方式洁闰。
1、PostgreSQL 數(shù)據(jù)庫中的查詢生命周期
每個(gè)查詢都會(huì)經(jīng)歷不同的階段万细，了解每個(gè)階段對數(shù)據(jù)庫的意義很重要扑眉。
第一階段是通過Postgres 的客戶端連接到數(shù)據(jù)庫。
第二階段是將查詢轉(zhuǎn)換為稱為解析樹的中間格式赖钞。
第三階段就是我們所說的重寫系統(tǒng)/規(guī)則系統(tǒng)腰素。它采用從第二階段生成的解析樹，并以規(guī)劃器/優(yōu)化器可以開始在其中工作的方式重新編寫它雪营。
第四階段是最重要的階段弓千。如果沒有規(guī)劃器，執(zhí)行器將在如何執(zhí)行查詢献起、使用什么索引洋访、是否掃描較小的表以消除更多不必要的行等問題上一頭霧水。
第五個(gè)也是最后一個(gè)階段是執(zhí)行器谴餐，它實(shí)際執(zhí)行并返回結(jié)果姻政。
2、數(shù)據(jù)準(zhǔn)備
用假數(shù)據(jù)設(shè)置一些表來為后面的測試做準(zhǔn)備岂嗓。
--建表
create table users
(
id serial PRIMARY KEY,
name varchar(255) not null,
mobile varchar(255) not null,
age integer,
created_at timestamp default now() not null,
updated_at timestamp default now() not null
);
--導(dǎo)入1000000數(shù)據(jù)
insert into users(name,mobile,age) select gen_random_zh(2,3), floor(random()*(13799999999-13700000000)+ 13700000000) as mobile, random()*(30-20)+20 from generate_series(1,1000000) as id;
--建表
create table comments
(
id serial PRIMARY KEY,
article_id bigint not null,
user_id bigint not null,
content varchar(255) not null,
created_at timestamp default now() not null,
updated_at timestamp default now() not null
);
--導(dǎo)入1000000數(shù)據(jù)
insert into comments(article_id,user_id,content) select floor((select min(id) from articles) + RANDOM() * ((select max(id) from articles)- (select min(id) from articles))) as article_id, floor((select min(id) from users) + RANDOM() * ((select max(id) from users)- (select min(id) from users))) as article_id, gen_random_zh(1,10) from generate_series(1,1000000) as id;
3扶歪、執(zhí)行計(jì)劃參數(shù)
PostgreSQL 和許多其他數(shù)據(jù)庫系統(tǒng)一樣，可以在數(shù)據(jù)庫實(shí)際執(zhí)行查詢之前查看它們運(yùn)行的計(jì)劃摄闸。我們可以通過運(yùn)行所謂的EXPLAIN命令來做到這一點(diǎn)。
3.1妹萨、解釋一個(gè)查詢
EXPLAIN SELECT * FROM users LIMIT 10;
3.2年枕、解釋并執(zhí)行
EXPLAIN ANALYSE SELECT * FROM users LIMIT 10;
與EXPLAIN不同的是，EXPLAIN ANALYSE會(huì)在數(shù)據(jù)庫中運(yùn)行查詢乎完。
3.3熏兄、緩存
加上參數(shù)BUFFERS，可以顯示有多少數(shù)據(jù)來自 PostgreSQL 緩存树姨，多少來自磁盤摩桶。
EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM users LIMIT 10 OFFSET 200
Buffers : shared hit=5表示從 PostgreSQL 緩存中獲取了五個(gè)頁，如果是shared read=5帽揪，則意味著數(shù)據(jù)來自磁盤而不是緩存硝清。我們調(diào)整查詢的行偏移再試一下。
EXPLAIN (ANALYSE,BUFFERS) SELECT * FROM users LIMIT 10 OFFSET 500
Buffers: shared hit=7 read=5顯示有 5 頁來自磁盤转晰。如果我們再次執(zhí)行相同的查詢芦拿，那么所有數(shù)據(jù)現(xiàn)在都來自緩存士飒。
PostgreSQL 使用一種稱為 LRU（最近最少使用）緩存的機(jī)制將經(jīng)常使用的數(shù)據(jù)存儲在內(nèi)存中。
3.4蔗崎、VERBOSE 參數(shù)
EXPLAIN (ANALYSE,BUFFERS,VERBOSE) SELECT * FROM users LIMIT 10 OFFSET 500
Verbose是一個(gè)提供額外信息的參數(shù)酵幕，用于獲取有關(guān)查詢的詳細(xì)信息和其他信息。缓苛。
請注意芳撒，Output: id, name, mobile等是額外的字段信息。
4未桥、執(zhí)行計(jì)劃的結(jié)構(gòu)
任何執(zhí)行計(jì)劃笔刹，無論其復(fù)雜性如何，都有一些基本結(jié)構(gòu)钢属。
查詢的節(jié)點(diǎn)
EXPLAIN SELECT * FROM users LIMIT 10 OFFSET 500;
一個(gè)節(jié)點(diǎn)可以被認(rèn)為是數(shù)據(jù)庫執(zhí)行的一個(gè)階段徘熔。節(jié)點(diǎn)大多是嵌套的，如上圖淆党；先完成Seq Scan酷师，然后在進(jìn)行Limit。添加一個(gè)Where子句來理解進(jìn)一步的嵌套染乌。
EXPLAIN SELECT * FROM users where NAME = '張三' LIMIT 10
執(zhí)行是由內(nèi)而外進(jìn)行的：
- 篩選name = '張三' 的行
- 使用上述過濾器進(jìn)行順序掃描
- 在頂部應(yīng)用limit
5山孔、掃描
PostgreSQL 執(zhí)行計(jì)劃中有幾種節(jié)點(diǎn)類型，包括Scans荷憋、Joins台颠、Sort等，本次將說一下Scans勒庄。
在本文中串前，將主要探討Scan節(jié)點(diǎn)類型。為了便于理解实蔽，禁用了并行掃描荡碾。
SET max_parallel_workers_per_gather = 0;
5.1、順序掃描（seq scan）
讓我們搜索一下 user_id=20001 的數(shù)據(jù)局装。
如果沒有索引或行數(shù)較少坛吁，則 Planner 會(huì)掃描所有的行。通常情況下我們不應(yīng)該使用順序掃描铐尚，因?yàn)樗浅Ｂ⑶译S著數(shù)據(jù)的增加而變慢拨脉。當(dāng)表太小或順序掃描足夠快時(shí)，也會(huì)出現(xiàn)例外情況宣增。
5.2索引掃描 Index Scan
讓我們在列上創(chuàng)建一個(gè)簡單的 BTree 索引來加速上述查詢玫膀。
CREATE INDEX id_idx ON comments USING BTREE(user_id)
創(chuàng)建索引后，Planner 現(xiàn)在使用索引來執(zhí)行索引掃描爹脾。
它比Seq Scan掃描快得多匆骗。
5.3僅索引掃描 Index Only Scan
Index Only Scan 與 Index Scan 非常相似劳景，僅當(dāng)SELECT查詢的字段在WHERE子句中都包含索引列時(shí)，才能使用碉就。
5.4Bitmap Heap Scan 和 Bitmap Index Scan
即 位圖堆掃描 和 位圖索引掃描
普通的索引掃描一次讀一條索引項(xiàng)盟广，而BitMap Heap Scan一次性將滿足條件的索引項(xiàng)全部取出，并在內(nèi)存中進(jìn)行排序, 然后根據(jù)取出的索引項(xiàng)訪問表數(shù)據(jù)瓮钥。BitMap Heap Scan 針對有多個(gè)索引項(xiàng)滿足條件時(shí)筋量，通過飽和式的索引頁讀取結(jié)合排序大幅減少隨機(jī)讀取，提升I/O效率碉熄。
但在某些情況下Bitmap And或Bitmap Or將不起作用桨武，我們將不得不創(chuàng)建復(fù)合索引。在許多情況下锈津，Planner 可以非常有效地組合兩個(gè)單獨(dú)的索引呀酸。
5.5并行掃描
順序掃描是迄今為止我們看到的所有計(jì)劃中最慢的。計(jì)劃器按順序檢查數(shù)據(jù)并嘗試找到結(jié)果琼梆。PostgreSQL 通過在查詢中添加并行查詢優(yōu)化了一些性能性誉。
并行查詢默認(rèn)配置是2，一般建議是讓并行查詢的數(shù)量與 CPU 中的核心數(shù)量相等茎杂，以獲最高的效率错览。
6、EXPLAIN 成本
在查看EXPLAIN命令的輸出時(shí)煌往，首先會(huì)注意到成本統(tǒng)計(jì)信息倾哺，因此很自然地想知道它們的含義、它們是如何計(jì)算的刽脖，以及它們是如何使用的羞海。
6.1成本的單位是什么
成本是任意單位。常見的誤解是把它們認(rèn)為是以毫秒或其他時(shí)間單位為單位曲管，但事實(shí)并非如此却邓。
默認(rèn)情況下，成本單位錨定到成本為 1.0 單位的單個(gè)順序頁面讀取seq_page_cost翘地。
配置及說明：
seq_page_cost: 1 連續(xù)塊掃描操作的單個(gè)塊的cost，例如全表掃描
random_page_cost: 4 隨機(jī)塊掃描操作的單個(gè)塊的cost癌幕，例如索引掃描
cpu_tuple_cost: 0.01 處理每條記錄的cpu開銷（tuple：關(guān)系中的一行記錄）
cpu_index_tuple_cost: 0.005 掃描每個(gè)索引條目帶來的CPU開銷
cpu_operator_cost: 0.0025 操作符或函數(shù)帶來的cpu開銷
6.2啟動(dòng)成本
您看到的第一個(gè)數(shù)字cost=被稱為“啟動(dòng)成本”衙耕。這是獲取第一行所需時(shí)間的估計(jì)值。因此勺远，一個(gè)節(jié)點(diǎn)的啟動(dòng)成本包括其子項(xiàng)的成本橙喘。
對于順序掃描，啟動(dòng)成本通常接近于零胶逢，因?yàn)樗梢粤⒓撮_始獲取行厅瞎。對于排序操作饰潜，它會(huì)更高，因?yàn)樵陂_始返回行之前需要完成大部分工作和簸。
6.3總成本
在啟動(dòng)成本和兩個(gè)點(diǎn)之后彭雾，被稱為“總成本”。這是對返回所有行所需時(shí)間的估計(jì)锁保。
7薯酝、如何計(jì)算成本
7.1順序掃描
順序掃描的成本由函數(shù) cost_seqscan()來估算。
順序掃描爽柒，由于是掃描所有數(shù)據(jù)頁吴菠，不需要準(zhǔn)備工作，start-up的成本為0浩村，run的成本計(jì)算公式為：
run cost = cpu run cost + disk run cost
=(cpu_tuple_cost + cpu_operator_cost) * Ntuple + seq_page_cost * Npage
Ntuple做葵，Npage可在pg_class中查詢出來。
postgres=# select relpages, reltuples FROM pg_class WHERE relname='users';
relpages | reltuples
----------+-----------
9346 | 1e+06
最終開銷是：(0.01 + 0.0025)* 1000000 + 1 * 9346 = 21846.0
7.2索引掃描
以查詢語句 select * from users where id < 999;來估算索引掃描成本心墅。
由查詢條件id<999酿矢，可走users_pkey索引，查詢pg_class可得Nindex,page=2745嗓化，Nindex,tuple=1000000棠涮。
postgres=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'users_pkey';
relpages | reltuples
----------+-----------
2745 | 1e+06
(1 row)
7.2.1 start-up成本
索引掃描start-up成本指讀取索引頁拿到目標(biāo)表第一個(gè)tuple的成本，其估算公式為：
start-up cost={ceil(log2(Nindex,tuple)) + (Hindex + 1) * 50} * cpu_operator_cost
其中Hindex為索引樹的高度刺覆。
# 用pageinspect插件查詢索引高度严肪，level為2，高度為2
postgres=# select * from bt_metap('test_idx');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+--------+-------+------------+-----------+-------------+-------------------------
340322 | 4 | 412 | 2 | 412 | 2 | 0 | -1
start-up cost={ceil(log2(1000000)) + (2+1) * 50} * 0.0025= 0.425谦屑。
7.2.2 run成本
索引掃描的run成本指表和索引cpu成本驳糯、IO成本之和：
run cost=(index cpu cost + table cpu cost) + (index IO cost + table IO cost)
注：僅索引掃描的話不需估算 table cpu cost 和 table IO cost。
其中三個(gè)成本估算公式如下：
index cpu cost=Selectivity* Nindex,tuple * (cpu_index_tuple_cost+qual_op_cost);
table cpu cost=Selectivity* Ntuple *cpu_tuple_cost;
index IO cost= ceil(Selectivity* Nindex,page) *random_page_cost;
table IO cost=max_IO_cost+indexCorrelation2*(min_IO_cost-max_IO_cost);
qual_op_cost氢橙，評估索引的成本酝枢，默認(rèn)為0.0025。
Selectivity：
權(quán)重因子悍手，表明I/O到CPU的相關(guān)性帘睦，又稱為選擇率，指where子句的索引的搜索范圍的比例坦康，它是從0到1的浮點(diǎn)數(shù)竣付。如 (Selectivity * Ntuple) 指讀取表中行的數(shù)量， (Selectivity * Nindex,page) 指讀取索引頁的數(shù)量滞欠。
Selectivity使用 histogram_bounds 或 MCV(Most Common Value) 來估算古胆，這兩者可在pg_stats中查詢出來。
表的每個(gè)字段的MCV存儲在pg_stats的most_common_vals 和 most_common_freqs字段筛璧，如查詢語句：
select * from users where id < 999;
SELECT most_common_vals, most_common_freqs FROM pg_stats where tablename='users' and attname='id';
可查出表 users 字段 id 值 999 對應(yīng)的頻率逸绎，將該頻率作為Selectivity值惹恃。
如果 MCV 沒有查詢出結(jié)果，則使用 histogram_bounds 來估算棺牧。
histogram_bounds：將字段的值分成近似相等的級別的值列表巫糙。
查看表users字段id的histogram_bounds：
postgres=# SELECT histogram_bounds FROM pg_stats where tablename='users' and attname='id';
histogram_bounds
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{24,9948,20772,31475,41663,50095,60571,70420,79825,89302,99882,109310,119327,129438,139089,149055,158695,168809,179415,189557,198869,208360,218839,229109,238739,249205,259831,269518,279599,289155,298695,308020,317203,327240,337306,347137,357224,366999,377090,387940,397380,408293,419071,428211,438488,448486,458984,469359,478653,488477,499100,508761,518178,529688,539247,549696,559948,569816,580518,591073,602060,611942,622714,632301,643046,654112,665432,675455,685635,695258,705031,715577,724843,735068,744714,753645,763120,772899,782906,791836,802342,811931,821667,830875,840692,850233,860378,869772,879845,889899,898914,909238,918837,929341,938615,949433,959099,968353,978552,989834,999909}
histogram_bounds默認(rèn)分為100個(gè)桶(采樣組)，桶的編號從0開始陨帆，histogram_bounds的值是桶的邊界值曲秉，如第0個(gè)桶的histogram_bounds為24，則存儲在0號桶的最小值為24疲牵，第1個(gè)桶的histogram_bounds為9948承二，則存儲在1號桶的最小值為9948，則桶0存儲的數(shù)據(jù)為 24 <= value < 9948纲爸。
查詢條件 id < 999亥鸠，999 存儲在第0個(gè)桶，然后用公式可計(jì)算：
max_IO_cost是IO成本的最壞情況识啦，即隨機(jī)掃描所有表頁的成本负蚊，公式如下 ：
max_IO_cost=Npage * random_page_cost=9346 * 4.0 = 37384.0
min_IO_cost是IO成本的最佳情況，即順序掃描所選表頁的成本颓哮，公式如下：
min_IO_cost=1 * random_page_cost + (ceil(Selectivity * Npage) - 1) * seq_page_cost=1 * 4.0 + (ceil(0.0009824667472793228* 9346) - 1) * 1.0 = 13.0
indexCorrelation家妆，從pg_stats可查到，等于 1冕茅。
postgres=# SELECT tablename,attname, correlation FROM pg_stats WHERE tablename = 'users' and attname='id';
tablename | attname | correlation
------------+--------+-------------
users | id | 1
所以：
index cpu cost=0.0009824667472793228* 1000000 * (0.005 + 0.0025) =7.368500604594921
table cpu cost=0.0009824667472793228* 1000000 * 0.01 =9.824667472793228
index IO cost= ceil(0.0009824667472793228* 2745) * 4.0 = 12.0
table IO cost= 37384.0 + 12 * (13.0 - 37384.0) = 13.0
最后：
run cost = (7.368500604594921 + 9.824667472793228) + (12.0 +13.0) = 42.19316807738815
total cost = 0.425 + 42.19316807738815 = 42.61816807738815
