postgreSql性能优化提升技巧指南

一、使用EXPLAIN：

    PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。
    PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数值就是规划器试图最小化的数值。
    这里有一个简单的例子，如下：
    EXPLAIN SELECT * FROM tenk1;
                             QUERY PLAN
    -------------------------------------------------------------
     Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 )
    
    EXPLAIN引用的数据是：
    1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。
    2). 预计的总开销。
    3). 预计的该规划节点输出的行数。
    4). 预计的该规划节点的行平均宽度(单位：字节)。
    这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。
    现在我们执行下面基于系统表的查询：
    SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
    从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。
    postgres=# show cpu_tuple_cost;
     cpu_tuple_cost
    ----------------
     0.01
    (1 row)
     cost = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)
    
     下面我们再来看一个带有WHERE条件的查询规划。
    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
   
                             QUERY PLAN
    ------------------------------------------------------------
     Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 )
       Filter: (unique1 < 7000)
   
    EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。
    上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。
    如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：
    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 )
       Recheck Cond: (unique1 < 100)
       ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 )
             Index Cond: (unique1 < 100)
   
    这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的"位图(bitmap)"是进行排序的机制。
    现在我们还可以将WHERE的条件设置的更加严格，如：
    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;

                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.00 rows=2 )
       Index Cond: (unique1 < 3)
   
    在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。
    现在我们需要向WHERE子句增加另外一个条件，如：
    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
   
                                      QUERY PLAN
    ------------------------------------------------------------------------------
     Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.01 rows=1 )
       Index Cond: (unique1 < 3)
       Filter: (stringu1 = 'xxx'::name)
   
    新增的过滤条件stringu1 = 'xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。
    如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：
    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
    
                                         QUERY PLAN
    -------------------------------------------------------------------------------------
     Bitmap Heap Scan on tenk1  (cost=11.27..49.11 rows=11 )
       Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
       ->  BitmapAnd  (cost=11.27..11.27 rows=11 )
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 )
                   Index Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 )
                   Index Cond: (unique2 > 9000)
   
    这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。
    现在让我们来看一下基于索引字段进行表连接的查询规划，如：
    EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
     
                                          QUERY PLAN
    --------------------------------------------------------------------------------------
     Nested Loop  (cost=2.37..553.11 rows=106 )
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 )
             Recheck Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 )
                   Index Cond: (unique1 < 100)
       ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 )
             Index Cond: ("outer".unique2 = t2.unique2)
   
    从查询规划中可以看出(Nested Loop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1 < 100发挥了作用。 这个时候t1.unique2 = t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描中，并生成类似的条件t2.unique2 = constant。所以，内层扫描将得到和EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是 106 * 3.01)，以及连接处理需要的一点点CPU时间。   
    如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：
    SET enable_nestloop = off;
    EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
     
                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
     Hash Join  (cost=232.61..741.67 rows=106 )
       Hash Cond: ("outer".unique2 = "inner".unique2)
       ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 )
       ->  Hash  (cost=232.35..232.35 rows=106 )
             ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 )
                   Recheck Cond: (unique1 < 100)
                   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 )
                         Index Cond: (unique1 < 100)
   
    这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2 = t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取tenk2之前不可能获得任何输出行。

    此外，我们还可以用EXPLAIN ANALYZE命令检查规划器预估值的准确性。这个命令将先执行该查询，然后显示每个规划节点内实际运行时间，以及单纯EXPLAIN命令显示的预计开销，如：
    EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;     
                                                                QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=2.37..553.11 rows=106 ) (actual time=1.392..12.700 rows=100 loops=1)
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 ) (actual time=0.878..2.367 rows=100 loops=1)
             Recheck Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 ) (actual time=0.546..0.546 rows=100 loops=1)
                   Index Cond: (unique1 < 100)
       ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 ) (actual time=0.067..0.078 rows=1 loops=100)
             Index Cond: ("outer".unique2 = t2.unique2)
     Total runtime: 14.452 ms
   
    注意"actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。
    在一些查询规划里，一个子规划节点很可能会运行多次，如之前的嵌套循环规划，内层的索引扫描会为每个外层行执行一次。在这种情况下，"loops"将报告该节点执行的总次数，而显示的实际时间和行数目则是每次执行的平均值。这么做的原因是令这些真实数值与开销预计显示的数值更具可比性。如果想获得该节点所花费的时间总数，计算方式是用该值乘以"loops"值。
    EXPLAIN ANALYZE显示的"Total runtime"包括执行器启动和关闭的时间，以及结果行处理的时间，但是它并不包括分析、重写或者规划的时间。
    如果EXPLAIN命令仅能用于测试环境，而不能用于真实环境，那它就什么用都没有。比如，在一个数据较少的表上执行EXPLAIN，它不能适用于数量很多的大表，因为规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再增加几个磁盘页面读取用以查找索引是毫无意义的。

二、批量数据插入：

    有以下几种方法用于优化数据的批量插入。
    1. 关闭自动提交：
    在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效率也会受到很大的打击。解决方法是，关闭系统的自动提交，并且在插入开始之前，显示的执行begin transaction命令，在全部插入操作完成之后再执行commit命令提交所有的插入操作。
   
    2. 使用COPY：
    使用COPY在一条命令里装载所有记录，而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的，它不像INSERT命令那样灵活，但是在装载大量数据时，系统开销也要少很多。因为COPY是单条命令，因此在填充表的时就没有必要关闭自动提交了。
   
    3. 删除索引：
    如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。
   
    4. 删除外键约束：
    和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后在重建约束。
   
    5. 增大maintenance_work_mem：
    在装载大量数据时，临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率，但是它不会对COPY操作本身产生多大的影响。
   
    6. 增大checkpoint_segments：
    临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向PostgreSQL装载大量数据时，将会导致检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须flush到磁盘上。通过提高checkpoint_segments变量的值，可以有效的减少检查点的数目。
   
    7. 事后运行ANALYZE：
    在增加或者更新了大量数据之后，应该立即运行ANALYZE命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。