Postgresql查询成本计算初探

Postgresql查询成本计算初探摘要关系数据库很重要的一个方面是查询速度。查询速度的好坏，直接影响一个系统的好坏。查询速度一般需要通过查询规划来窥视执行的过程。查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。主要关注的参数和表参数:来自postgresql.conf文件，可以通过show 来查看seq_page_cost = 1.0 # measured on an arbitrary scalerandom_page_cost = 4.0 # same scale as abovecpu_tuple_cost = 0.01 # same scale as abovecpu_index_tuple_cost = 0.005 # same scale as abovecpu_operator_cost = 0.0025 # same scale as aboveparallel_tuple_cost = 0.1 # same scale as aboveparallel_setup_cost = 1000.0 # same scale as above表(视图): pg_class（主要关注relpages, reltuples）, pg_stats分析简单的查询的成本计算过程建立模拟数据，插入100000条数据进入一个表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)查询pg_class表，查看test表的page数量和行数postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test'; relpages | reltuples----------+----------- 834 | 100000成本为1834.00是怎么算出来的？这个过程，实际上是顺序扫描了834个page，节点发射了100000行查看配置参数seq_page_cost = 1.0cpu_tuple_cost = 0.01得出的结果就是postgres=# select 834 * 1.0 + 100000 * 0.01; ?column?---------- 1834.00得出来的查询成本就是 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是怎么算出来的？查询pg_class表, pages,tuples和上面的例子一样这个过程就是顺序test表，发射100000行，然后通过云存过滤了100000行查看过滤运算一行的代价cpu_operator_cost = 0.0025得出的结果是postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025; ?column?----------- 2084.0000加入索引的情况```create index on test(id);```对比下面的四种情况Index Only Scanpostgres=# 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 Scanpostgres=# 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 Scanpostgres=# explain select * from test where id把数据乱序插入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 Bitmap Index Scan on test_id_idx (cost=0.00..5.19 rows=102 width=0) Index Cond: (id结论有索引的时候，成本会大大减少。执行计划跟数据的分布有很大的关系。有索引的分析相对复杂一点，可以先参考官方源码实现。后面再补充上来