#### 3.2單表查詢的代價估計

costsize.c中的函式用於估算各種操作的代價，所有被執行器執行的操作都有著相應的代價函式。例如，函式cost_seqscan()和cost_index()分別用於估算順序掃描和索引掃描的代價。
在PostgreSQL中有三種代價
1. 啟動代價：在讀取到第一條元組前花費的代價，比如索引掃描節點的啟動代價就是讀取目標表的索引頁，獲取到第一個元組的代價
2. 執行代價：獲取全部元組的代價
3. 總代價：前兩者之和

環境準備：

```
create table tb1(id int primary key,data int);
create index tb1_data_idx on tb1(data);
insert into tb1 select generate_series(1,10000),generate_series(1,10000);
analyze;
\d tb1
```

##### 順序掃描

```
mydb=# explain select * from tb1 where id<8000;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on tb1  (cost=0.00..170.00 rows=8000 width=8)
   Filter: (id < 8000)
(2 rows)
--啟動代價和總代價0.00和170.0
--順序掃描中，啟動代價等於0，執行代價由以下公式定義
run_cost=cpu_run_cost+disk_run_cost=(cpu_tuple_cost+cpu_operator_cost)xN(tuple)+seq_page_costxN(pages)
--其中seq_page_cost,cup_tuple_cost和cpu_operator_cost是在postgresql.conf中配置的引數，預設值為1.0,0.01和0.0025,N(tuple) N(pages)分別表示元組總數與頁面總數，兩個值可以通過以下查詢
mydb=# select relpages,reltuples from pg_class where relname='tb1';
 relpages | reltuples 
----------+-----------
       45 |     10000
(1 row)
```
#####  索引掃描
儘管PG支援很多索引方法， 比如B樹，GiST,GIN和BRin，但掃描代價估計都是用一個共用代價函式cost_index()

```
--獲取值
mydb=# select relpages,reltuples from pg_class where relname='tb1_data_idx';
 relpages | reltuples 
----------+-----------
       30 |     10000
(1 row)
mydb=# explain select id,data from tb1 where data<240;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Index Scan using tb1_data_idx on tb1  (cost=0.29..13.49 rows=240 width=8)
   Index Cond: (data < 240)
(2 rows)
```
- > 選擇率

查詢謂詞的選擇率是通過直方圖界值與高頻值估計的，這些資訊都儲存在系統目錄pg_statistics中，並可通過pg_stats檢視查詢。
  
   表中每一列的高頻值都在pg_class檢視中的most_common_vals和most_common_freqs中成對儲存。
   1、高頻值：該列上最常出現的取值列表
   2、高頻值頻率：高頻值相應出現頻率的列表

```
--檢視高頻值相關的
select most_common_vals,most_common_freqs from pg_stats from pg_stats
where tablename='' and attname='';
--直方圖資訊
select histogram_bounds from pg_stats where tablename='tb1' and attname='data';
```
   seq_page_cost/random_page_cost
   預設值分別是1.0和4.0，這意味著Postgresql假設隨機掃描的進度是順序掃描的1/4，pg預設值是基於HDD(普通硬碟)設定的。如果使用SSD時，最好將random_page_cost的值設定為1.0

#####  排序

```
--如果工作記憶體放不下，就會建立臨時檔案，使用檔案排序
mydb=# explain select id,data from tb1 where data <240 order by id;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Sort  (cost=22.97..23.57 rows=240 width=8)
   Sort Key: id
   ->  Index Scan using tb1_data_idx on tb1  (cost=0.29..13.49 rows=240 width=8)
         Index Cond: (data < 240)
(4 rows)
```

讀書筆記】《PostgreSQL指南-內幕探索》-3.2單表查詢的代價估計

相關文章