摘要
關(guān)系數(shù)據(jù)庫很重要的一個方面是查詢速度。查詢速度的好壞，直接影響一個系統(tǒng)的好壞。
查詢速度一般需要通過查詢規(guī)劃來窺視執(zhí)行的過程。
查詢路徑會選擇查詢代價最低的路徑執(zhí)行。而這個代價是怎么算出來的呢。
主要關(guān)注的參數(shù)和表
參數(shù):來自postgresql.conf文件，可以通過show 來查看
seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 4.0 # same scale as above
cpu_tuple_cost = 0.01 # same scale as above
cpu_index_tuple_cost = 0.005 # same scale as above
cpu_operator_cost = 0.0025 # same scale as above
parallel_tuple_cost = 0.1 # same scale as above
parallel_setup_cost = 1000.0 # same scale as above
表(視圖): pg_class（主要關(guān)注relpages, reltuples）, pg_stats
分析簡單的查詢的成本計算過程
建立模擬數(shù)據(jù)，插入100000條數(shù)據(jù)進(jìn)入一個表
create table test(id int, info text);
insert into test(id, info) select i, md5(i::text) from generate_series(1, 100000) t(i);
沒有索引的情況
分析全表查詢的成本計算過程
postgres=# analyze test; #防止沒有分析
postgres=# explain select * from test;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on test (cost=0.00..1834.00 rows=100000 width=37)
1.查詢pg_class表，查看test表的page數(shù)量和行數(shù)
postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test';
relpages | reltuples
----------+-----------
834 | 100000
成本為1834.00是怎么算出來的？
2.這個過程，實(shí)際上是順序掃描了834個page，節(jié)點(diǎn)發(fā)射了100000行
3.查看配置參數(shù)
seq_page_cost = 1.0
cpu_tuple_cost = 0.01
4.得出的結(jié)果就是
postgres=# select 834 * 1.0 + 100000 * 0.01;
?column?
----------
1834.00
5.得出來的查詢成本就是 1834.00。和上面的查詢計劃算出來的一致。
全表加入條件的成本計算過程
postgres=# explain select * from test where id = 100;
QUERY PLAN
--------------------------------------------------------
Seq Scan on test (cost=0.00..2084.00 rows=1 width=37)
Filter: (id = 100)
成本 2084.00是怎么算出來的？
1.查詢pg_class表, pages,tuples和上面的例子一樣
2.這個過程就是順序test表，發(fā)射100000行，然后通過云存過濾了100000行
3.查看過濾運(yùn)算一行的代價
cpu_operator_cost = 0.0025
4.得出的結(jié)果是
postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025;
?column?
-----------
2084.0000
加入索引的情況
```
create index on test(id);
```
對比下面的四種情況
Index Only Scan
postgres=# explain select id from test where id = 100;
QUERY PLAN
-----------------------------------------------------------------------------
Index Only Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=4)
Index Cond: (id = 100)
Index Scan
postgres=# explain select * from test where id = 100;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=37)
Index Cond: (id = 100)
Index Scan
postgres=# explain select * from test where id 100;
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using test_id_idx on test (cost=0.29..10.11 rows=104 width=37)
Index Cond: (id 100)
把數(shù)據(jù)亂序插入
truncate table test;
insert into test(id, info) select i, md5(i::text) from generate_series(1, 1000000) t(i) order by random();
postgres=# explain select * from test where id 100;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=5.22..380.64 rows=102 width=37)
Recheck Cond: (id 100)
-> Bitmap Index Scan on test_id_idx (cost=0.00..5.19 rows=102 width=0)
Index Cond: (id 100)
結(jié)論
- 有索引的時候，成本會大大減少。
- 執(zhí)行計劃跟數(shù)據(jù)的分布有很大的關(guān)系。
- 有索引的分析相對復(fù)雜一點(diǎn)，可以先參考官方源碼實(shí)現(xiàn)。后面再補(bǔ)充上來
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- PostgreSQL中使用dblink實(shí)現(xiàn)跨庫查詢的方法
- 在PostgreSQL中實(shí)現(xiàn)遞歸查詢的教程
- PostgreSQL樹形結(jié)構(gòu)的遞歸查詢示例
- pgsql查詢優(yōu)化之模糊查詢實(shí)例詳解
