期有人提出想查看Postgresql的執(zhí)行計(jì)劃，下面分析下PG執(zhí)行計(jì)劃中的cost等相關(guān)值是怎么計(jì)算出來(lái)的： 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ì)算出來(lái)的：
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 ; --普通堆棧表，無(wú)任何索引約束
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 在原來(lái)的基礎(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)樗饕木壒?，訪問的數(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
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
