Postgresql查询效率计算初探

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 

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) 

postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test';
 relpages | reltuples 
----------+-----------
  834 | 100000 

seq_page_cost = 1.0 
cpu_tuple_cost = 0.01 

postgres=# select 834 * 1.0 + 100000 * 0.01;
 "htmlcode">

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表，发射100000行，然后通过云存过滤了100000行


3.查看过滤运算一行的代价




cpu_operator_cost = 0.0025 


4.得出的结果是




postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025;
 "htmlcode">

```
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) 


把数据乱序插入


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)


结论



  
有索引的时候，成本会大大减少。
  
执行计划跟数据的分布有很大的关系。
  
有索引的分析相对复杂一点，可以先参考官方源码实现。后面再补充上来

总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。