.jpg)
期有人提出想查看postgresql的執(zhí)行計(jì)劃，下面分析下PG執(zhí)行計(jì)劃中的cost等相關(guān)值是怎么計(jì)算出來的： PG的版本是9.1.2 1.終端工具PGADMIN,對(duì)執(zhí)行的語(yǔ)句按F7即可，然后看數(shù)據(jù)輸出和解釋 2.命令行分析：explain select * from table_name; 一般我們會(huì)比較關(guān)注消耗
期有人提出想查看Postgresql的執(zhí)行計(jì)劃，下面分析下PG執(zhí)行計(jì)劃中的cost等相關(guān)值是怎么計(jì)算出來的：
PG的版本是9.1.2
?
1.終端工具PGADMIN,對(duì)執(zhí)行的語(yǔ)句按F7即可，然后看數(shù)據(jù)輸出和解釋
2.命令行分析：explain select * from table_name;
一般我們會(huì)比較關(guān)注消耗值cost和掃描的方式，如走索引或者full scan全表掃描.當(dāng)COST值消耗比較大時(shí)需要注意是否有優(yōu)化的可能。
與執(zhí)行計(jì)劃相關(guān)的幾個(gè)參數(shù)，參看下面的示例：
kenyon=# select count(1) from dba.website ; ???????????? –普通堆棧表，無任何索引約束
count
——-
??? 20
(1 row)
kenyon=# explain select * from dba.website ;
?????????????????????? QUERY PLAN?????????????????????
——————————————————–
Seq Scan on website? (cost=0.00..1.20 rows=20 width=4)
(1 row)
–relpages磁盤頁(yè)，reltuples是行數(shù)(與實(shí)際不一定相符，一般略小)
kenyon=# select relpages,reltuples from pg_class where relname = ‘website’;
relpages | reltuples
———-+———–
??????? 1 |??????? 20
(1 row)
kenyon=# select 1*1+20*0.01;???????????????????????????????????????????????????????????????????
–cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost
?column?
———-
???? 1.20
(1 row)
kenyon=# show cpu_tuple_cost ;
cpu_tuple_cost
—————-
0.01
(1 row)
kenyon=# show seq_page_cost;
seq_page_cost
—————
1
(1 row)
–加限制條件的執(zhí)行計(jì)劃
kenyon=# select count(1) from dba.website where hits >15;
count
——-
???? 5
(1 row)
kenyon=# explain select * from dba.website where hits >15;
????????????????????? QUERY PLAN?????????????????????
——————————————————-
Seq Scan on website? (cost=0.00..1.25 rows=5 width=4)
?? Filter: (hits > 15)
(2 rows)
kenyon=# show cpu_operator_cost ;
cpu_operator_cost
——————-
0.0025
(1 row)
因?yàn)閽呙璧目倲?shù)是20行，不變的，所以COST不會(huì)下降，相反反而增加了0.05，這是因?yàn)轭~外消耗了CPU的時(shí)間去檢查符合約束條件數(shù)據(jù)，即cost 在原來的基礎(chǔ)上再增加 20 * 0.0025 = 0.05? （reltuples * cpu_operator_cost）
–加索引的執(zhí)行計(jì)劃
kenyon=# select count(1) from dba.website_2 ;
count
——-
? 8000
(1 row)
kenyon=# explain select * from dba.website_2 ;
????????????????????????? QUERY PLAN????????????????????????
————————————————————–
Seq Scan on website_2? (cost=0.00..112.00 rows=8000 width=4)
(1 row)
kenyon=# select relpages,reltuples from pg_class where relname = ‘website_2’;
relpages | reltuples
———-+———–
?????? 32 |????? 8000
(1 row)
kenyon=# explain select * from dba.website_2 where hits >7900;? –走的索引
??????????????????????????????????? QUERY PLAN??????????????????????????????????
———————————————————————————-
Index Scan using ind_website_2 on website_2? (cost=0.00..10.00 rows=100 width=4)
?? Index Cond: (hits > 7900)
(2 rows)
（）
kenyon=# explain select * from dba.website_2 where hits >10;??? –未走索引（不滿足索引條件，full scan）
????????????????????????? QUERY PLAN????????????????????????
————————————————————–
Seq Scan on website_2? (cost=0.00..132.00 rows=7991 width=4)?? — 132 = 112+8000*0.0025
?? Filter: (hits > 10)
(2 rows)
雖然讀取的COST更大，但是因?yàn)樗饕木壒剩L問的數(shù)據(jù)量變小了，所以總體COST是下降的。
–多表JOIN的執(zhí)行計(jì)劃 示例： 若想看實(shí)際的一個(gè)執(zhí)行時(shí)間，可以加上 analyze 參數(shù)
kenyon=# explain analyze select * from dba.website a ,dba.website_2 b where a.hits = b.hits and a.hits >18;
???????????????????????????????????????????? QUERY PLAN
—————————————————————————————————————————————
Merge Join (cost=1.26..1.90 rows=2 width=8) (actual time=0.070..0.075 rows=2 loops=1)
? Merge Cond: (b.hits = a.hits)
? -> Index Scan using ind_website_2 on website_2 b (cost=0.00..235.25 rows=8000 width=4) (actual time=0.013..0.020 rows=21 loops=1)
? -> Sort (cost=1.26..1.26 rows=2 width=4) (actual time=0.035..0.037 rows=2 loops=1)
???? Sort Key: a.hits
???? Sort Method: quicksort Memory: 17kB
???? -> Seq Scan on website a (cost=0.00..1.25 rows=2 width=4) (actual time=0.009..0.011 rows=2 loops=1)
????? Filter: (hits > 18)
Total runtime : 0.120 ms
(9 rows)
total runtime 是執(zhí)行器啟動(dòng)和關(guān)閉的時(shí)間，但不包括解析，重寫和規(guī)劃的時(shí)間
注意： pg_class中的relpages,reltuples數(shù)據(jù)不是實(shí)時(shí)更新的，一般在vacuum analyze和少部分DDL（如建立索引）后更新。
示例1：
kenyon=# insert into dba.website select generate_series(8000,9000);
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like ‘%website%’;
relpages | reltuples |??? relname??? | relkind
———-+———–+—————+———
??????? 1 |??????? 20 | website?????? | r
?????? 32 |????? 8000 | website_2???? | r
?????? 20 |????? 8000 | ind_website_2 | i
(3 rows)
kenyon=# vacuum analyze dba.website;
VACUUM
kenyon=# vacuum analyze dba.website;
VACUUM
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like ‘%website%’;
relpages | reltuples |??? relname??? | relkind
———-+———–+—————+———
??????? 5 |????? 1021 | website?????? | r
?????? 36 |????? 8999 | website_2???? | r
?????? 22 |????? 8999 | ind_website_2 | i
(3 rows)
示例2：
kenyon=# insert into dba.website select generate_series(8000,9000);
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like ‘%website%’;
relpages | reltuples |??? relname??? | relkind
———-+———–+—————+———
??????? 1 |??????? 21 | website?????? | r
?????? 36 |????? 8999 | website_2???? | r
?????? 22 |????? 8999 | ind_website_2 | i
(3 rows)
kenyon=# create index ind_website on dba.website(hits);
CREATE INDEX
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like ‘%website%’;
relpages | reltuples |??? relname??? | relkind
———-+———–+—————+———
??????? 5 |????? 1022 | website?????? | r
?????? 36 |????? 8999 | website_2???? | r
?????? 22 |????? 8999 | ind_website_2 | i
??????? 5 |????? 1022 | ind_website?? | i
(4 rows)
所涉及的系統(tǒng)表：
pg_stats
pg_statistic
pg_class
pg_stat是任何人都可以看的，而且可讀性高，比較直觀，pg_statistic只有superuser才能讀，并且可讀性差，普通人員建議看pg_stats，pg_stats是pg_statistic的視圖。 這兩個(gè)表也不是實(shí)時(shí)更新的，需要vacuum analyze時(shí)會(huì)更新
所涉及的系統(tǒng)變量：
default_statistics_target
geqo_threshold
join_collapse_limit
from_collapse_limit
kenyon=# show default_statistics_target ;
default_statistics_target
—————————
100
(1 row)
kenyon=# show geqo_threshold ;???????? –這個(gè)參數(shù)的大小會(huì)設(shè)置執(zhí)行計(jì)劃從窮舉搜索到概率選擇性搜索的臨界值
geqo_threshold
—————-
12
(1 row)
kenyon=# show join_collapse_limit ;??? –join連接走執(zhí)行計(jì)劃上限
join_collapse_limit
———————
8
(1 row)
kenyon=# show from_collapse_limit ;
from_collapse_limit
———————
8
(1 row)
EXPLAIN
Name
EXPLAIN— show the execution plan of a statement
Synopsis
EXPLAIN [ ( option [, …] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
?? ANALYZE [ boolean ]
?? VERBOSE [ boolean ]
?? COSTS [ boolean ]
?? BUFFERS [ boolean ]
?? FORMAT { TEXT | XML | JSON | YAML }
例子：
kenyon=# explain (analyze,verbose,costs,buffers) select id from dba.test222 order by id desc limit 1;
????????????????????????????????????????????????????????? QUERY PLAN????????????????????????????????????????????????????????
——————————————————————————————————————————
Limit? (cost=1807.80..1807.80 rows=1 width=4) (actual time=87.167..87.168 rows=1 loops=1)
?? Output: id
?? Buffers: shared hit=393
?? ->? Sort? (cost=1807.80..2043.60 rows=94320 width=4) (actual time=87.165..87.165 rows=1 loops=1)
???????? Output: id
???????? Sort Key: test222.id
???????? Sort Method: top-N heapsort? Memory: 17kB
???????? Buffers: shared hit=393
???????? ->? Seq Scan on dba.test222? (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.036..42.847 rows=100000 loops=1)
?????????????? Output: id
?????????????? Buffers: shared hit=393
Total runtime: 87.183 ms
(12 rows)
kenyon=# explain (analyze,verbose,costs,buffers) select max(id) from dba.test222;
?????????????????????????????????????????????????????? QUERY PLAN?????????????????????????????????????????????????????
————————————————————————————————————————
Aggregate? (cost=1572.00..1572.01 rows=1 width=4) (actual time=77.679..77.680 rows=1 loops=1)
?? Output: max(id)
?? Buffers: shared hit=393
?? ->? Seq Scan on dba.test222? (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.012..36.908 rows=100000 loops=1)
???????? Output: id
???????? Buffers: shared hit=393
Total runtime: 77.701 ms
(7 rows)
explain參數(shù)解釋：
ANALYZE ：執(zhí)行命令并顯示執(zhí)行事件，默認(rèn)false
VERBOSE ：對(duì)執(zhí)行計(jì)劃提供額外的信息，如查詢字段信息等，默認(rèn)false
COSTS ：顯示執(zhí)行計(jì)劃的，默認(rèn)true
BUFFERS ：默認(rèn)false，前置條件是analyze
FORMAT ：默認(rèn)格式是text?
.png)
