《PostgreSQL配置优化技巧》由会员分享，可在线阅读，更多相关《PostgreSQL配置优化技巧（8页珍藏版）》请在金锄头文库上搜索。
1、一、使用 EXPLAIN：PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响 是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN 命令来查看规划器为每个查询生成的查询规划。PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节 点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同的扫描节点类型代表着 不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连 接、聚集、排序，或者是对原始行的其它操作，那么就会在扫描节点之上有其它额外的节 点。并且
2、这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。 EXPLAIN将为规划树中的每个节点都输出一行信息，显示基本的节点类型和规划器为执行 这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计， 这个数值就是规划器试图最小化的数值。这里有一个简单的例子，如下：EXPLAIN SELECT * FROM ten k1;QUERY PLANSeq Sca n on tenk1 (cost=0.00.458.00 rows=10000 width=244)EXPLAIN引用的数据是：1) .预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点
3、里做排续的 时间)。2) .预计的总开销。3) .预计的该规划节点输出的行数。4) .预计的该规划节点的行平均宽度(单位：字节)。这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读 取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数rows)并不是规划节 点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际 返回的行数。现在我们执行下面基于系统表的查询：SELECT relpages, reltuples FROM pg_class WHERE relname = tenk1;从查询结果中可以看出tenk1表占有358
4、个磁盘页面和10000条记录，然而为了计算 cost的值，我们仍然需要知道另外一个系统参数值。postgres=# show cpu_tuple_cost;cpu_tuple_cost0.01（1 row）cost = 358（磁盘页面数）+ 10000 （行数）* 0.01（cpu_tuple_cost 系统参数值）下面我们再来看一个带有WHERE条件的查询规划。EXPLAIN SELECT * FROM tenk1 WHERE uniq ue1 7000;QUERY PLANSeq Sea n on tenkl (cost=0.00.483.00 rows=7033 width=244)F
5、ilter: (u ni quel 7000)EXPLAIN的输出显示，WHERE子句被当作一个filter应用，这表示该规划节点将扫描 表中的每一行数据，之后再判定它们是否符合过滤的条件，最后仅输出通过过滤条件的行数。这里由于WHERE 子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有真正降低，实际 上它还增加了一些因数据过滤而产生的额外CPU开销。上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变， 因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。如果我们将上面查询的条件设置的更为严格一些的话，将会
6、得到不同的查询规划，如：EXPLAIN SELECT * FROM tenkl WHERE un iquel 100;QUERY PLANBitmap Heap Sea n on ten k1 (cost=2.37.232.35 rows=106 width=244) Recheck Cond: (uniquel Bitmap Index Sca n on ten k1_u ni quel (cost=0.00.2.37 rows=106 width=0)In dex Co nd: (uni quel 100)这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件 的行的
7、位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺序地读取它们的开销要高很多，但 是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的 原因是因为上层规划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的 开销。节点名称里面提到的位图(bitmap)是进行排序的机制。现在我们还可以将WHERE的条件设置的更加严格，如：EXPLAIN SELECT * FROM tenk1 WHERE uniq ue1 3;QUERY PLANIn dex Sea n usi ng ten k1_u ni quel on t
8、en k1 (cost=0.00.10.00 rows=2 width=244)In dex Cond: (uniq ue1 3)在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得 更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。现在我们需要向WHERE子句增加另外一个条件，如：EXPLAIN SELECT * FROM tenkl WHERE un iquel 3 AND stri ngul = xxx;QUERY PLANIndex Sean using tenk1_uniquel on tenkl (cost=0.00.1
9、0.01 rows=1 width=244)In dex Cond: (uniq ue1 3)Filter: (stri ngul = xxx: name)新增的过滤条件str in gu1 = xxx只是减少了预计输出的行数，但是并没有减少实际开销, 因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过 滤条件来看待。如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR 的组合，如：EXPLAIN SELECT * FROM tenkl WHERE un iquel 9000;QUERY PLANBitmap Heap Se
10、an on tenk1 (cost=11.27.49.11 rows=11 width=244)Recheck Cond: (uniquel 9000)- BitmapA nd (cost=11.27.11.27 rows=11 width=0)- Bitmap In dex Sca n on ten k1_u ni quel (cost=0.00.2.37 rows=106 width=0) In dex Co nd: (uni quel Bitmap Index Sca n on tenk1_uni que2 (cost=0.00.8.65 rows=1042 width=0) In de
11、x Co nd: (uni que2 9000)这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤 器相比，这个方法未必是更优。现在让我们来看一下基于索引字段进行表连接的查询规划，如：EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE tl.uniquel Bitmap Heap Sca n on tenk1 t1 (cost=2.37.232.35 rows=106 width=244) Recheck Cond: (unique1 Bitmap In dex Sca n on ten k1_u ni que1 (cost=
12、0.00.2.37 rows=106 width=0) In dex Co nd: (uni que1 Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00.3.01 rows=1 width=244)In dex Cond: (outer. uniq ue2 = t2. uniq ue2)从查询规划中可以看出(Nested Loop)该查询语句使用了嵌套循环。外层的扫描是一个位 图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1 100发挥了作用。这个时候 t1.uniq ue2 =t2.unique2条件子句
13、还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内 层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描中，并生成类似的条件t2.u nique2 = con sta nt。所 以，内层扫描将得到和 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42一样的计划和开销。最后，以 外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是106 * 3.01)，以及连接处理需要的 一点点CPU时间。如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置, 以关闭嵌套循环，如：SET en able _n
14、estloop = off;EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 Seq Scan on tenk2 t2 (cost=0.00.458.00 rows=10000 width=244)- Hash (cost=232.35.232.35 rows=106 width=244)- Bitmap Heap Sca n on tenk1 t1 (cost=2.37.232.35 rows=106 width=244)Recheck Cond: (uniquel Bitmap Index Sean on tenk1_uniquel (cost=0.00.2.37 rows=106 width=0)In dex Co nd: (uni quel 100)这个规划仍然试图用同样的索引扫描从tenkl里面取出符合要求的100行，并把它们存 储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫描，并为每一条tenk2中的记录查询散列(哈希)表， 寻找可能匹配tl.unique2 = t2.unique2的行。读取tenk
