3.2单表查询的代价估计

costsize.c中的函数用于估算各种操作的代价，所有被执行器执行的操作都有着相应的代价函数。例如，函数cost_seqscan()和cost_index()分别用于估算顺序扫描和索引扫描的代价。
在PostgreSQL中有三种代价

启动代价：在读取到第一条元组前花费的代价，比如索引扫描节点的启动代价就是读取目标表的索引页，获取到第一个元组的代价
运行代价：获取全部元组的代价
总代价：前两者之和

环境准备：

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单表查询的代价估计

3.2单表查询的代价估计

顺序扫描

索引扫描

排序