Postgresql查询效率计算初探
摘要
关系数据库很重要的一个方面是查询速度。查询速度的好坏，直接影响一个系统的好坏。
查询速度一般需要通过查询规划来窥视执行的过程。
查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。
主要关注的参数和表
参数:来自postgresql.conf文件，可以通过show来查看
seq_page_cost=1.0#measuredonanarbitraryscale random_page_cost=4.0#samescaleasabove cpu_tuple_cost=0.01#samescaleasabove cpu_index_tuple_cost=0.005#samescaleasabove cpu_operator_cost=0.0025#samescaleasabove parallel_tuple_cost=0.1#samescaleasabove parallel_setup_cost=1000.0#samescaleasabove
表(视图):pg_class（主要关注relpages,reltuples）,pg_stats
分析简单的查询的成本计算过程
建立模拟数据，插入100000条数据进入一个表
createtabletest(idint,infotext); insertintotest(id,info)selecti,md5(i::text)fromgenerate_series(1,100000)t(i);
没有索引的情况
分析全表查询的成本计算过程
postgres=#analyzetest;#防止没有分析 postgres=#explainselect*fromtest; QUERYPLAN ------------------------------------------------------------- SeqScanontest(cost=0.00..1834.00rows=100000width=37)
1.查询pg_class表，查看test表的page数量和行数
postgres=#selectt.relpages,t.reltuplesfrompg_classtwheret.relname='test'; relpages|reltuples ----------+----------- 834|100000
成本为1834.00是怎么算出来的？
2.这个过程，实际上是顺序扫描了834个page，节点发射了100000行
3.查看配置参数
seq_page_cost=1.0 cpu_tuple_cost=0.01
4.得出的结果就是
postgres=#select834*1.0+100000*0.01; ?column? ---------- 1834.00
5.得出来的查询成本就是1834.00。和上面的查询计划算出来的一致。
全表加入条件的成本计算过程
postgres=#explainselect*fromtestwhereid=100; QUERYPLAN -------------------------------------------------------- SeqScanontest(cost=0.00..2084.00rows=1width=37) Filter:(id=100)
成本2084.00是怎么算出来的？
1.查询pg_class表,pages,tuples和上面的例子一样
2.这个过程就是顺序test表，发射100000行，然后通过云存过滤了100000行
3.查看过滤运算一行的代价
cpu_operator_cost=0.0025
4.得出的结果是
postgres=#select834*1.0+100000*0.01+100000*0.0025; ?column? ----------- 2084.0000
加入索引的情况
``` createindexontest(id); ```
对比下面的四种情况
IndexOnlyScan
postgres=#explainselectidfromtestwhereid=100; QUERYPLAN ----------------------------------------------------------------------------- IndexOnlyScanusingtest_id_idxontest(cost=0.29..8.31rows=1width=4) IndexCond:(id=100)
IndexScan
postgres=#explainselect*fromtestwhereid=100; QUERYPLAN ------------------------------------------------------------------------- IndexScanusingtest_id_idxontest(cost=0.29..8.31rows=1width=37) IndexCond:(id=100)
IndexScan
postgres=#explainselect*fromtestwhereid<100; QUERYPLAN ---------------------------------------------------------------------------- IndexScanusingtest_id_idxontest(cost=0.29..10.11rows=104width=37) IndexCond:(id<100)
把数据乱序插入
truncatetabletest; insertintotest(id,info)selecti,md5(i::text)fromgenerate_series(1,1000000)t(i)orderbyrandom();
postgres=#explainselect*fromtestwhereid<100; QUERYPLAN ---------------------------------------------------------------------------- BitmapHeapScanontest(cost=5.22..380.64rows=102width=37) RecheckCond:(id<100) ->BitmapIndexScanontest_id_idx(cost=0.00..5.19rows=102width=0) IndexCond:(id<100)
结论
- 有索引的时候，成本会大大减少。
- 执行计划跟数据的分布有很大的关系。
- 有索引的分析相对复杂一点，可以先参考官方源码实现。后面再补充上来
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。