快捷導(dǎo)航

Postgresql查詢效率計算初探

更新時間：2019年05月09日 11:20:33 作者：軌跡

這篇文章主要給大家介紹了關(guān)于Postgresql查詢效率計算的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用Postgresql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

摘要

關(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é)論