PostgreSQL配置优化技巧

一、使用EXPLAIN：PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那么就会在扫描节点之上有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数值就是规划器试图最小化的数值。这里有一个简单的例子，如下：EXPLAINSELECT*FROMtenk1;QUERYPLAN-------------------------------------------------------------SeqScanontenk1(cost=0.00..458.00rows=10000width=244)EXPLAIN引用的数据是：1).预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。2).预计的总开销。3).预计的该规划节点输出的行数。4).预计的该规划节点的行平均宽度(单位：字节)。这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。现在我们执行下面基于系统表的查询：SELECTrelpages,reltuplesFROMpg_classWHERErelname='tenk1';从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。postgres=#showcpu_tuple_cost;cpu_tuple_cost----------------0.01(1row)cost=358(磁盘页面数)+10000(行数)*0.01(cpu_tuple_cost系统参数值)下面我们再来看一个带有WHERE条件的查询规划。EXPLAINSELECT*FROMtenk1WHEREunique17000;QUERYPLAN------------------------------------------------------------SeqScanontenk1(cost=0.00..483.00rows=7033width=244)Filter:(unique17000)EXPLAIN的输出显示，WHERE子句被当作一个filter应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：EXPLAINSELECT*FROMtenk1WHEREunique1100;QUERYPLAN------------------------------------------------------------------------------BitmapHeapScanontenk1(cost=2.37..232.35rows=106width=244)RecheckCond:(unique1100)-BitmapIndexScanontenk1_unique1(cost=0.00..2.37rows=106width=0)IndexCond:(unique1100)这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的位图(bitmap)是进行排序的机制。现在我们还可以将WHERE的条件设置的更加严格，如：EXPLAINSELECT*FROMtenk1WHEREunique13;QUERYPLAN------------------------------------------------------------------------------IndexScanusingtenk1_unique1ontenk1(cost=0.00..10.00rows=2width=244)IndexCond:(unique13)在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。现在我们需要向WHERE子句增加另外一个条件，如：EXPLAINSELECT*FROMtenk1WHEREunique13ANDstringu1='xxx';QUERYPLAN------------------------------------------------------------------------------IndexScanusingtenk1_unique1ontenk1(cost=0.00..10.01rows=1width=244)IndexCond:(unique13)Filter:(stringu1='xxx'::name)新增的过滤条件stringu1='xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：EXPLAINSELECT*FROMtenk1WHEREunique1100ANDunique29000;QUERYPLAN-------------------------------------------------------------------------------------BitmapHeapScanontenk1(cost=11.27..49.11rows=11width=244)RecheckCond:((unique1100)AND(unique29000))-BitmapAnd(cost=11.27..11.27rows=11width=0)-BitmapIndexScanontenk1_unique1(cost=0.00..2.37rows=106width=0)IndexCond:(unique1100)-BitmapIndexScanontenk1_unique2(cost=0.00..8.65rows=1042width=0)IndexCond:(unique29000)这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。现在让我们来看一下基于索引字段进行表连接的查询规划，如：EXPLAINSELECT*FROMtenk1t1,tenk2t2WHEREt1.unique1100ANDt1.unique2=t2.unique2;QUERYPLAN--------------------------------------------------------------------------------------NestedLoop(cost=2.37..553.11rows=106width=488)-BitmapHeapScanontenk1t1(cost=2.37..232.35rows=106width=244)RecheckCond:(unique1100)-BitmapIndexScanontenk1_unique1(cost=0.00..2.37rows=106width=0)IndexCond:(unique1100)-IndexScanusingtenk2_unique2ontenk2t2(cost=0.00..3.01rows=1width=244)IndexCond:(outer.unique2=t2.unique2)从查询规划中可以看出(NestedLoop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1100发挥了作用。这个时候t1.unique2=t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描中，并生成类似的条件t2.unique2=constant。所以，内层扫描将得到和EXPLAINSELECT*FROMtenk2WHEREunique2=42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是106*3.01)，以及连接处理需要的一点点CPU时间。如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：SETenable_nestloop=off;EXPLAINSELECT*FROMtenk1t1,tenk2t2WHEREt1.unique1100ANDt1.unique2=t2.unique2;QUERYPLAN------------------------------------------------------------------------------------------HashJoin(cost=232.61..741.67rows=106width=488)HashCond:(outer.unique2=inner.unique2)-SeqScanontenk2t2(cost=0.00..458.00rows=10000width=244)-Hash(cost=232.35..232.35rows=106width=244)-BitmapHeapScanontenk1t1(cost=2.37..232.35rows=106width=244)RecheckCond:(unique1100)-BitmapIndexScanontenk1_unique1(cost=0.00..2.37rows=106width=0)IndexCond:(unique1100)这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2=t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取ten