Postgresql查询效率计算初探_PostgreSQL

Postgresql查询效率计算初探

2020-05-03 16:22轨迹 PostgreSQL

这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

摘要

关系数据库很重要的一个方面是查询速度。查询速度的好坏，直接影响一个系统的好坏。

查询速度一般需要通过查询规划来窥视执行的过程。

查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。

主要关注的参数和表

参数:来自postgresql.conf文件，可以通过show 来查看

									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

表(视图): pg_class（主要关注relpages, reltuples）, pg_stats

分析简单的查询的成本计算过程

建立模拟数据，插入100000条数据进入一个表

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

1.查询pg_class表，查看test表的page数量和行数

									postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test';

									 relpages | reltuples 

									----------+-----------

									  834 | 100000

成本为1834.00是怎么算出来的？

2.这个过程，实际上是顺序扫描了834个page，节点发射了100000行

3.查看配置参数

1 2	`seq_page_cost = 1.0` `cpu_tuple_cost = 0.01`

4.得出的结果就是

									postgres=# select 834 * 1.0 + 100000 * 0.01;

									 ?column? 

									----------

									 1834.00

5.得出来的查询成本就是 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是怎么算出来的？

1.查询pg_class表, pages,tuples和上面的例子一样

2.这个过程就是顺序test表，发射100000行，然后通过云存过滤了100000行

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

1	`cpu_operator_cost = 0.0025`

4.得出的结果是

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

									 ?column? 

									-----------

									 2084.0000

加入索引的情况

									```

									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)

把数据乱序插入

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