PostgreSQL教程（十）：性能增强技术

1。使用说明：

PostgreSQL生成每个查询的查询计划，因为它是选择正确的查询path.postgresql本身性能的影响包含了规划寻找最优计划的关键。我们可以通过使用解释命令来查看每个查询生成的计划者的查询计划。

产生在PostgreSQL查询规划是一个由1到N规划节点规划树。底部节点是表扫描节点，这是用来返回从数据表中检索数据行。然而，不同的扫描节点类型代表不同的表的访问模式，如连续扫描，扫描和索引，位图索引扫描。如果查询需要连接、聚集、排序，或对原行其他操作，在扫描节点的其他节点。这些操作通常有多种方法，这样有可能会在这些locations.explain不同节点类型将输出一行信息在规划树的每个节点，显示基本的节点类型和估计成本计算的计划执行规划节点。第一行（上层节点）是程序总执行开销的估计，这是计划者试图最小化的数量。

下面是一个简单的示例，如下所示：

复制代码代码如下所示：

解释选择*从tenk1；

查询计划

-------------------------------------------------------------

序列扫描tenk1（成本= 0。458行= 10000宽度= 244）
解释引用的数据是：

1）。预期的引导开销（在输出扫描开始之前消耗的时间，例如，在排序节点中）。

2）。估计总成本。

3）。预计将由规划节点输出的行数。

4）。规划节点的期望行平均宽度（单位：字节）。

成本单位的磁盘页面的数量，如1，这将表明磁盘页面的顺序读，上层节点的开销将包括其所有子结点的开销。输出的行数（行）这里不行，计划节点处理/扫描，通常数量少。一般来说，预期的行数在顶层更接近于行，实际上都是由查询返回的数。

现在我们根据系统表执行下面的查询：

复制代码代码如下所示：

选择relpages，从pg_class哪里relname = 'tenk1reltuples；
从查询结果中，我们可以看到，tenk1桌子占358页和10000盘记录。然而，为了计算成本值，我们还需要知道另一个系统参数值。

复制代码代码如下所示：

Postgres = #显示cpu_tuple_cost；

cpu_tuple_cost

----

零点零一

（1行）

成本= 358（磁盘页面数）+ 10000（行数）×0.01（cpu_tuple_cost系统参数值）
现在让我们看一下查询计划，以及条件。

复制代码代码如下所示：

解释选择*从tenk1哪里unique1＜7000；
查询计划

------------------------------------------------------------

序列扫描tenk1（成本= 0。483行= 7033宽度= 244）

过滤器：（unique1＜7000）
解释的输出表明WHERE子句被应用为筛选器，由于WHERE子句的存在，预期输出行数减少了。即使如此，扫描仍然访问所有10000行数据，因此成本并没有真正降低。事实上，由于数据过滤，它还增加了一些CPU成本。

上述数据只是预期的数量，即使每次都执行分析命令，因为分析产生的统计数据是通过从表中提取的随机样本计算出来的。

如果我们为上述查询条件设置更严格的条件，我们将获得不同的查询计划，例如：

复制代码代码如下所示：

解释选择*从tenk1哪里unique1＜100；

查询计划

------------------------------------------------------------------------------

位图堆扫描tenk1（成本= 2.37。232.35行= 106宽度= 244）

检查条件：（unique1＜100）

索引扫描tenk1_unique1 ->位图（成本= 0。2.37行= 106宽度= 0）

指数条款：（unique1＜100）
在这里，策划者决定用两步计划，和内心的规划节点访问索引，找到的行匹配指数条件下位置，然后上层规划节点读取来自内部和外部的行。它比阅读顺序读取数据行单独昂贵得多，但因为他们不能访问表中的所有磁盘页面，该方法的成本仍然低于顺序扫描，使用两层规划的原因是上层规划节点排序检索的索引行的物理位置，可以最大限度地减少读磁盘页面单独的成本。位图中的节点名称排序机制。

现在我们还可以设置更严格的条件，例如：

复制代码代码如下所示：

解释选择*从tenk1哪里unique1＜3；

查询计划

------------------------------------------------------------------------------

在使用tenk1_unique1 tenk1索引扫描（成本= 0。10行= 2宽度= 244）

指数条款：（unique1＜3）
在这个SQL中，表的数据行按索引顺序读取，这将使读取它们的成本更大。但是，要读取的行数非常少，所以不需要根据行物理位置对它们进行排序。

现在我们需要向WHERE子句添加另一个条件，例如：

复制代码代码如下所示：

解释选择*从tenk1哪里unique1＜3和stringu1 = 'xxx；
查询计划

------------------------------------------------------------------------------

在使用tenk1_unique1 tenk1索引扫描（成本= 0。10.01行= 1宽度= 244）

指数条款：（unique1＜3）

过滤器：（stringu1 = 'xxx '：：名字）
新的过滤条件stringu1 = 'xxx'only减少行预计的数量，但不降低实际成本，因为我们仍然需要访问数据的行数相同。条件不被视为一个指标的情况，但作为一个对指数结果过滤条件。

如果多个字段在WHERE条件下进行索引，则规划程序可以使用索引的组合和或（或），如：

复制代码代码如下所示：

解释选择*从tenk1哪里unique1 9000；
查询计划

-------------------------------------------------------------------------------------

位图堆扫描tenk1（成本= 11.27。49.11行= 11宽度= 244）

检查条件：（（unique1 9000））

-> bitmapand（成本= 11.27。11.27行= 11宽度= 0）

索引扫描tenk1_unique1 ->位图（成本= 0。2.37行= 106宽度= 0）

指数条款：（unique1＜100）

索引扫描tenk1_unique2 ->位图（成本= 0。8.65行= 1042宽度= 0）

指数条款：（unique2 > 9000）
这样的结果将导致访问两个索引，这不一定比仅使用一个索引更好，而另一个条件仅用作筛选器。

现在让我们来看一下基于表连接的索引字段的查询规划，例如：

复制代码代码如下所示：

解释选择*从tenk1 T1，T2，tenk2 t1.unique1＜100和t1.unique2 = t2.unique2；
查询计划

--------------------------------------------------------------------------------------

嵌套循环（成本= 2.37….. 553.11行= 106宽度= 488）

位图扫描tenk1 T1 >堆（成本= 2.37。232.35行= 106宽度= 244）

检查条件：（unique1＜100）

索引扫描tenk1_unique1 ->位图（成本= 0。2.37行= 106宽度= 0）

指数条款：（unique1＜100）

索引扫描使用tenk2_unique2在tenk2 -> T2（成本= 0。3.01行= 1宽度= 244）

指数条款：（外。unique2 = T2。unique2）
它可以从查询计划中看到（嵌套循环），查询语句使用一个嵌套循环。外扫描是一个位图索引，所以它的开销是行计数和先前的查询一样，因为条件unique1＜100起了重要的作用。在这个时候，t1.unique2 = t2.unique2从句没有任何影响，因此不影响外部的线数扫描。然而，对于内层扫描，目前外扫描数据将插入内索引扫描和生成一个类似条件t2.unique2 =常数。因此，内部扫描会得到同样的计划和开销解释选择*从tenk2哪里unique2 = 42。最后，基于外上方扫描，建立循环节点的成本，再加上一个ITER每个外部行（这里是106×3.01），以及连接处理所需的CPU时间。

如果您不想使用嵌套循环来规划上面的查询，我们可以通过执行以下系统设置来关闭嵌套循环，例如：

复制代码代码如下所示：

集enable_nestloop =关闭；

解释选择*从tenk1 T1，T2，tenk2 t1.unique1＜100和t1.unique2 = t2.unique2；
查询计划

------------------------------------------------------------------------------------------

散列连接（成本= 232.61….. 741.67行= 106宽度= 488）

哈希条件：（外。unique2 =内。unique2）

在tenk2 T2 >序列扫描（成本= 0。458行= 10000宽度= 244）

>散列（成本= 232.35….. 232.35行= 106宽度= 244）

位图扫描tenk1 T1 >堆（成本= 2.37。232.35行= 106宽度= 244）

检查条件：（unique1＜100）

索引扫描tenk1_unique1 ->位图（成本= 0。2.37行= 106宽度= 0）

指数条款：（unique1＜100）
这个计划仍在试图使用相同的索引扫描了100线的要求tenk1里面，并把它们存储在内存中的哈希表（哈市），然后做一个全表扫描tenk2，在tenk2表为每个查询哈希记录（哈市），寻找可能的匹配t1.unique2 = t2.unique2线。阅读tenk1建立哈希表的哈希连接的完整的启动开销，因为我们不能得到任何输出线在开始读tenk2。

此外，我们还可以使用解释分析命令检查龙门刨床的预测精度，该命令首先执行查询，然后在每个规划节点中显示实际运行时间，以及由简单解释命令显示的预期开销，例如：

复制代码代码如下所示：

解释分析选择*从tenk1 T1，T2，tenk2 t1.unique1＜100和t1.unique2 = =；

查询计划

----------------------------------------------------------------------------------------------------------------------------------

嵌套循环（成本= 2.37….. 553.11行= 106宽度= 488）（实际时间= 1.392….. 12.700行= 100循环= 1）

位图扫描tenk1 T1 >堆（成本= 2.37。232.35行= 106宽度= 244）（实际时间= 0.878。2.367行= 100圈= 1）

检查条件：（unique1＜100）

位图索引扫描（tenk1_unique1，成本= 0。2.37行= 106宽度= 0（实际）时间= 0.546。0.546行= 100圈= 1）

指数条款：（unique1＜100）

索引扫描使用tenk2_unique2在tenk2 -> T2（成本= 0。3.01行= 1宽度= 244）（实际时间= 0.067。0.078行= 1圈= 100）

指数条款：（外。unique2 = T2。unique2）

总运行时间：14.452毫秒
请注意，实际时间值是以实时毫秒计算的，而成本估计是由读取的磁盘页数计算的，因此它们可能是不一致的。但是，我们需要注意的是，这两组数据的比率是否一致。

在某些查询规划、子规划节点可能要跑很多次，如以前的嵌套循环的计划，和内部的索引扫描将被执行一次外排。在这种情况下，循环将报告时间节点执行的总数，而显示的行数实际时间是每个执行的平均水平，这是为了让这些真正的价值更具有可比性的开销预期值。如果你想获得的总花费的时间量的节点，计算乘以这个值由环的价值。

解释分析显示的总运行时包括执行器启动和关闭的时间以及行处理的时间，但不包括分析、重写或计划的时间。

如果解释命令只能用于测试环境，不能用在实际的环境中，它没有什么用它做。例如，执行解释桌上用更少的数据不能应用于许多大型的表，因为调度器计算成本不是线性的，所以它可能会选择更大或更小的表不同的计划。一个极端的例子是一个表，只占用一个磁盘页。在这样的表上，不管它是否有索引，你总能得到连续的扫描计划，计划者知道不管怎样，它都需要读一个磁盘页面，所以添加几个磁盘页读取索引是没有意义的。

两。批数据插入：

有几种方法可以优化批量插入数据。

1。关闭自动提交：

当批量插入数据，如果每个数据自动提交，当系统出现故障时，不仅能确保批量插入数据的一致性，但也会因多提交操作发生的插入效率造成了很大的打击。解决的办法是关闭系统的自动提交，并执行BEGIN TRANSACTION命令插入开始之前。完成所有插入操作之后，执行提交命令来提交所有插入操作。
2。使用复制：

使用复制来加载命令中的所有记录，而不是一系列插入命令。它不像插入命令那样灵活，但是在加载大量数据时系统的开销要小得多。因为复制是一个单独的命令，所以在填充表时不必关闭自动提交。
三.删除索引：

如果你加载一个新创建的表，最快的方法是创建一个表格，复制加载它，然后创建索引的表的需要。它是与现有的数据创建一个表上的索引比用线增加维修线更快。当然，一个指标不在，桌上的其他查询操作的性能会受到一定的影响，可能会破坏和唯一性约束。
4。删除外键约束：

与索引一样，外键约束的批处理检查比行检查更有效。因此，我们首先可以删除外键约束，加载数据，然后重建约束。
5。增加maintenance_work_mem：

临时在maintenance_work_mem系统变量的值的增加可以提高性能，加载大量数据时，系统参数可以提高执行效率，创建索引的命令和修改表添加外键的命令，但不复制操作本身造成太大的影响。
6。增加checkpoint_segments：

一个临时的checkpoint_segments系统变量的值的增加还可以提高大量数据装载的效率。这是因为加载大量数据PostgreSQL的时候，它会导致检查点操作（由系统变量checkpoint_timeout宣布）比平常多。当每个检查点时，所有的脏数据必须刷新到磁盘，通过增加的checkpoint_segments变量的值，检查点的数量可以有效降低。
7。事件后运行分析：

在添加或更新大量数据之后，应该立即运行分析命令，以确保计划者根据表获得最新的数据统计信息，换句话说，如果没有统计数据或统计数据太旧，计划者可能会选择较差的查询计划，从而导致查询效率低下。