PostgreSQL学习手册(性能提升技巧)

一、使用EXPLAIN：

 

    PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。  www.2cto.com  

    PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数值就是规划器试图最小化的数值。 

    这里有一个简单的例子，如下：

    EXPLAIN SELECT * FROM tenk1;

                             QUERY PLAN

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

     Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

     

    EXPLAIN引用的数据是：

    1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。

    2). 预计的总开销。

    3). 预计的该规划节点输出的行数。

    4). 预计的该规划节点的行平均宽度(单位：字节)。 

    这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。

    现在我们执行下面基于系统表的查询：  www.2cto.com  

    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 width=244)