postgresql 创建数据库并授权（详解postgresql数据库执行计划）

概述

PostgreSQL查询规划过程中，查询请求的不同执行方案是通过建立不同的路径来表达的，在生成许多符合条件的路径之后，要从中选择出代价最小的路径（基于成本运算），把它转化为一个计划，传递给执行器执行，规划器的核心工作就是生成多条路径，然后从中找出最优的那一条。而这也就是今天要讲的内容，PG数据库执行计划。

1、查看执行计划

explain 子句可以展示和分析执行计划。

其语法如下：

EXPLAIN Name EXPLAIN-- show the execution plan of a statement Synopsis EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] BUFFERS [ boolean ] FORMAT { TEXT | XML | JSON | YAML }

说明：

ANALYZE：真正执行SQL，默认:F
VERBOSE：显示计划的附加消息，包括：计划树每个节点的输出的各列、触发器名字（如有），默认：F
COSTS:每个计划节点的启动和总成本，以及估计行数和每行宽度。默认:T。第一个数字代表本步骤成本，后一个数字代表的是总成本。
SETTINGS：包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值与内置默认值不同。此参数默认为FALSE
BUFFERS：显示关于缓冲区的使用消息，需要与analyze一同使用；显示的缓冲区信息包括：共享块、本地块和临时块读和写的块数；共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。

》》执行计划解读，自底向上，自右向左，常用方法如：

--添加analyze实际执行来获得执行计划，可不加 explain analyze select * from test_table; --只看执行路径，不看cost explain (costs false) select * from test_table; --通过实际执行来看代价和缓冲区命中情况 explain (analyze true,buffers true) select * from test_table;

2、代价评估

评估路径优劣的依据是用系统表pg_statistic中的统计信息估算出来的不同路径的代价(cost)，PostgreSQL估计计划成本的方式：基于统计信息估计计划中各个节点的成本。PostgreSQL会分析各个表来获取一个统计信息样本（这个操作通常是由autovacuum这个守护进程周期性的执行analyze，来收集这些统计信息，然后保存到pg_statistic和pg_class里面）

用于估算代价的参数（postgresql.conf）如下：

postgresql 创建数据库并授权（详解postgresql数据库执行计划）(1)

postgresql 创建数据库并授权（详解postgresql数据库执行计划）(2)

postgresql 创建数据库并授权（详解postgresql数据库执行计划）(3)

》》代价计算

一个路径的估算由三部分组成：启动代价(startup cost)，总代价(totalcost)，执行结果的排序方式(pathkeys)

代价估算公式：

总代价=启动代价 I/O代价 CPU代价（cost=S P W*T） P:执行时要访问的页面数，反应磁盘的I/O次数 T:表示在执行时所要访问的元组数，反映了cpu开销 W:表示磁盘I/O代价和CPU开销建的权重因子

3、与执行计划相关的配置项

enable_seqscan:是否选择全表扫描

enable_indexscan:是否选择索引扫描

enable_bitmapscan:是否选择位图扫描

enable_tidscan:是否tid扫描（类似oracle rowid）

enable_nestloop:多表连接时，是否选择嵌套循环连接

enable_hashjoin:多表连接时，是否选择hash连接

enable_mergejoin:多表连接时，是否选择merge连接

enable_hashagg:多表连接时，是否使用hash聚合

enable_sort:是否使用明确的排序。

4、GEQO(遗传查询优化)

GEQO是一个使用探索式搜索来执行查询规划的算法。它可以降低负载查询的规划时间。同时，GEQO的检索是随机的，因此它的规划可能会不可确定。遗传查询规划器（GEQO）是一种使用启发式搜索来进行查询规划的算法。它可以降低对于复杂查询（连接很多表的查询）的规划时间，但是代价是它产生的计划有时候要差于使用穷举搜索算法找到的计划。

PostgreSQL中GEQO实现的特点有：

• 一种稳态 GA（遗传算法）（在种群中替换适应度最差的个体，而不是整代替换）的使用允许对改进的查询计划快速收敛。这对在合理时间内处理查询是最重要的；

• 边重组杂交的使用特别适合于通过GA为TSP的解决方案保持低丢边率；

• 遗传操作符变异被废弃，这样不需要修补机制来产生合法的TSP旅行。