PostgreSQL学习手册6－10

一、索引的类型：
    PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。
    
    1. B-Tree:
    CREATE TABLE test1 (
        id integer,
        content varchar
    );
    CREATE INDEX test1_id_index ON test1 (id);    
    B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用 BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。 
    
    2. Hash：
    CREATE INDEX name ON table USING hash (column);
    散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。
    这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。
    
    3. GiST：
    GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。 
    
    4. GIN：
    GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索 引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：<@、 @>、=、&&等。

二、复合索引：
    PostgreSQL中的索引可以定义在数据表的多个字段上，如：
    CREATE TABLE test2 (
        major int,
        minor int,
        name varchar
    }
    CREATE INDEX test2_mm_idx ON test2 (major, minor);
    在当前的版本中，只有B-tree、GiST和GIN支持复合索引，其中最多可以声明32个字段。
    1. B-Tree类型的复合索引：
    在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。
    
    2. GiST类型的复合索引：
    在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。 

    3. GIN类型的复合索引：
    与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

    使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。 

三、组合多个索引：
    PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之 间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子 是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。
    为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需 要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的 布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即 使有多个索引可用也会如此。     

    
四、唯一索引：
    目前，只有B-Tree索引可以被声明为唯一索引。
    CREATE UNIQUE INDEX name ON table (column [, ...]);
    如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为NULL值相互间不相等。
    
五、表达式索引：
    表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：
    SELECT * FROM test1 WHERE lower(col1) = 'value';
    此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：
    CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
    如果我们把该索引声明为UNIQUE，那么它会禁止创建那种col1数值只是大小写有区别的数据行，以及col1数值完全相同的数据行。因此，在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。现在让我们再看一个应用表达式索引的例子。
    SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
    和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。
    CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
    CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。
    从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然 而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。 
    
六、部分索引：
    部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。
    由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：
    1. 索引字段和谓词条件字段一致：
    CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)
        WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');
    下面的查询将会用到该部分索引：
    SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
    下面的查询将不会用该部分索引：
    一个不能使用这个索引的查询可以是∶
    SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

    2. 索引字段和谓词条件字段不一致：
    PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么 就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。
    CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;
    下面的查询一定会用到该部分索引：
    SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;
    那么对于如下查询呢？
    SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;
    这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。
    下面的查询将不会用到部分索引。
    SELECT * FROM orders WHERE order_nr = 3501;
    
    3. 数据表子集的唯一性约束：
    CREATE TABLE tests (
        subject text,
        target text,
        success boolean,
        ...
    );
    CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;
    该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。
     
七、检查索引的使用：
    见以下四条建议：
    1. 总是先运行ANALYZE。
    该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。 如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一 次失败的检查。 
    2. 使用真实的数据做实验。
    用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行， 规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划 能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数 据，都会令统计信息偏离实际数据应该具有的特征。    
    3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。
    4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

 

PostgreSQL学习手册(七) 事物隔离

    在SQL的标准中事物隔离级别分为以下四种：
    1. 读未提交(Read uncommitted)
    2. 读已提交(Read committed)
    3. 可重复读(Repeatable read)
    4. 可串行化(Serializable)
    然而PostgreSQL在9.1之前的版本中只是实现了其中两种，即读已提交和可串行化，如果在实际应用中选择了另外两种，那么PostgreSQL 将会自动向更严格的隔离级别调整。在PostgreSQL v9.1的版本中提供了三种实现方式，即在原有的基础上增加了可重复读。在这篇博客中我们将只是针对2)和4)进行说明和比较，因为在9.1中，3)和 4)的差别也是非常小的。

 读已提交可串行化PostgreSQL缺省隔离级别是否其它事物未提交数据是否可见不可见不可见执行效率高低适用场景简单SQL逻辑，如果SQL语句中含有嵌套查询，那么在多次SQL查询中将极有可能获得不同版本的数据。复杂SQL逻辑，特别是带有嵌套的查询比较适用。SELECT查询一致性时间点从该SELECT查询开始执行时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询开始执行时的数据版本相一致。从该SELECT查询所在事物开始时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询所在事物开始时的数据版本相一致。同事物内的数据操作是否可见比如在同一个事物内存在update和select操作，即使当前事物尚未提交，update所作的修改，在当前事物后面的select中依然可见。和读已提交相同。同事物内多次相同的select所见的数据是否相同不同，由于该级别select的一致性时间点是该查询开始执行时，而多次查询的时间点将肯定不相同，如果在第一次查询开始到第二次查询开始之间，其它的并发事物修改并提交或当前事物仅修改了查询将要获取的数据，那么这些数据操作的结果将会在第二个查询中有所体现。需要分两步来说，对于同一事物内的修改如果发 生在两次查询语句之间，那么第二个查询将会看到这些修改的结果。然而对于其它并发事物的修改，将不会造成任何影响，即两次select的结果是相同的。原 因显而易见，该隔离级别的select一致性时间点是与事物开始时相一致的。相同行数据的修改如果此时两个并发事物在修改同一行数据，先修 改的事物将会给该行加行级锁，另外一个事物将进入等待状态，直到第一个事物操作该行结束。那么倘若第一个针对该行的修改操作最终被其事物回滚，第二个修改 操作在结束等待后，将直接修改该数据。然而如果第一个操作是被正常提交的话，那么就需要进一步判断该操作的类型，如果是删除(delete)该行，第二个 修改操作将直接被忽略。如果是update该行的记录，第二个修改操作则需要重新评估该行是否依然符合之前定义的修改条件。和读已提交隔离级别的机制基本相同，只是在第 一个修改操作提交后，第二个操作将不再区分之前的修改是delete还是update，而是直接并返回下面信息：Error: Can't serialize access due to concurrent update. 这是因为一个可串行化的事务在可串行化事务开始之后不能更改或者锁住被其他事务更改过的行。因此，当应用收到这样的错误信息时，它应该退出当前的事务然后 从头开始重新进行整个事务。在应用程序中，也应该有必要的代码来专门处理该类错误。

    最后需要说明的是，在绝大多数的情况下，读已提交级别均可适用，而且该级别的并发效率更高。只有在比较特殊的情况下，才手工将当前的事物隔离级别调整为可串行化或可重复读。

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 width=244)
     
    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 width=244)
       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 width=244)
       Recheck Cond: (unique1 < 100)
       ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
             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 width=244)
       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 width=244)
       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 width=244)
       Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
       ->  BitmapAnd  (cost=11.27..11.27 rows=11 width=0)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                   Index Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 width=0)
                   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 width=488)
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
             Recheck Cond: (unique1 < 100)
             ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                   Index Cond: (unique1 < 100)
       ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
             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 width=488)
       Hash Cond: ("outer".unique2 = "inner".unique2)
       ->  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 Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
                   Recheck Cond: (unique1 < 100)
                   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                         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 width=488) (actual time=1.392..12.700 rows=100 loops=1)
       ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) (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 width=0) (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 width=244) (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命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。

PostgreSQL学习手册(九) 服务器配置

一、服务器进程的启动和关闭：
    下面是pg_ctl命令的使用方法和常用选项，需要指出的是，该命令是postgres命令的封装体，因此在使用上比直接使用postgres更加方便。
    pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]
    pg_ctl start     [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
    pg_ctl stop     [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
    pg_ctl restart  [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
    pg_ctl reload  [-D DATADIR] [-s]
    pg_ctl status  [-D DATADIR]
    pg_ctl promote [-D DATADIR] [-s]

选项描述-D指定数据库存储的路径-l指定服务器进程的日志文件-s仅打印错误信息，不打印普通信息-t SECS当使用-w选项时等待的秒数-w等待直到数据库操作完成(对于stop而言，该选项时缺省选项)-W不等待任何操作的完成--help显示帮助信息--version显示版本信息-m对于stop和restart操作，可以指定关闭模式系统关闭模式 smart不在接收新的连接，直到当前已有的连接都断开之后才退出系统fast不在接收新的连接请求，主动关闭已经建立的连接，之后退出系统immediate立即退出，但是在restart的时候需要有恢复的操作被执行

    
    这里我们只是给出最为常用的使用方式，即数据库服务器的正常启动和关闭。
    #start表示启动postgres服务器进程。
    #-D指定数据库服务器的初始目录的存放路径。
    #-l指定数据库服务器进程的日志文件
    /> pg_ctl -w start -D /opt/PostgreSQL/9.1/data -l /opt/PostgreSQL/9.1/data/pg_log/startup.log
    #stop表示停止postgres服务器进程
    #-m fast在关闭系统时，使用fast的关闭模式。
    /> pg_ctl stop -m fast -w -D /opt/PostgreSQL/9.1/data

二、服务器配置：
    1. 设置参数：
    在PostgreSQL中，所有配置参数名都是大小写不敏感的。每个参数都可以接受四种类型的值，它们分别是布尔、整数、浮点数和字符串。其中布尔值可以是ON、OFF、TRUE、FALSE、YES、NO、1和0。包含这些参数的配置文件是postgresql.conf，该文件通常存放在initdb初始化的数据(data)目录下，见如下配置片段：
    # 这是一个注释
    log_connections = yes
    log_destination = 'syslog'
    search_path = '$user, public'
    井号(#)开始的行为注释行，如果配置值中包含数字，则需要用单引号括起。如果参数值本身包含单引号，我们可以写两个单引号(推荐方法)或用反斜杠包围。    
    这里需要注意的是，并非所有配置参数都可以在服务器运行时执行动态修改，有些参数在修改后，只能等到服务器重新启动后才能生效。
    PostgreSQL还提供了另外一种修改配置参数的方法，即在命令行上直接执行修改命令，如：
    /> postgres -c log_connections=yes -c log_destination='syslog'
    如果此时命令行设置的参数和配置文件中的参数相互冲突，那么命令行中给出的参数将覆盖配置文件中已有的参数值。除此之外，我们还可以通过ALTER DATABASE和ALTER USER等PostgreSQL的数据定义命令来分别修改指定数据库或指定用户的配置信息。其中针对数据库的设置将覆盖任何从postgres命令行或者配置文件中给出的设置，然后又会被针对用户的设置覆盖，最后又都会被每会话的选项覆盖。下面是当服务器配置出现冲突时，PostgreSQL服务器将会采用哪种方式的优先级，如：
    1). 基于会话的配置；
    2). 基于用户的配置；
    3). 基于数据库的配置；
    4). postgres命令行指定的配置；
    5). 配置文件postgresql.conf中给出的配置。
    最后需要说明的是，有些设置可以通过PostgreSQL的set命令进行设置，如在psql中我们可以输入：
    SET ENABLE_SEQSCAN TO OFF;
    也可以通过show命令来显示指定配置的当前值，如：
    SHOW ENABLE_SEQSCAN;
    与此同时，我们也可以手工查询pg_settings系统表的方式来检索感兴趣的系统参数。 

三、内存相关的参数配置：
    1. shared_buffers(integer)：
    设置数据库服务器可以使用的共享内存数量。缺省情况下可以设置为32MB，但是不要少于128KB。因为该值设置的越高对系统的性能越有好处。该配置参数只能在数据库启动时设置。
    此时，如果你有一台专用的数据库服务器，其内存为1G或者更多，那么我们推荐将该值设置为系统内存的25%。
    
    2. work_mem(integer)：
    PostgreSQL在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和work_mem差不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。然而需要指出的是，如果系统中同时存在多个排序操作，那么每个操作在排序时使用的内存数量均为work_mem，因此在我们设置该值时需要注意这一问题。
    
    3. maintence_work_mem(integer)：
    指定在维护性操作中使用的最大内存数，如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等，该配置的缺省值为16MB。因为每个会话在同一时刻只能执行一个该操作，所以使用的频率不高，但是这些指令往往消耗较多的系统资源，因此应该尽快让这些指令快速执行完毕。

PostgreSQL学习手册(十) 角色和权限

    PostgreSQL是通过角色来管理数据库访问权限的，我们可以将一个角色看成是一个数据库用户，或者一组数据库用户。角色可以拥有数据库对象，如表、索引，也可以把这些对象上的权限赋予其它角色，以控制哪些用户对哪些对象拥有哪些权限。
    
一、数据库角色：

    1. 创建角色：
    CREATE ROLE role_name;
    
    2. 删除角色：
    DROP ROLE role_name;
    
    3. 查询角色：
    检查系统表pg_roles，如：
    SELECT rolname FROM pg_roles;
    也可以在psql中执行\du命令列出所有角色。
    
二、角色属性：

    一个数据库角色可以有一系列属性，这些属性定义他的权限，以及与客户认证系统的交互。
    1. 登录权限：
    只有具有LOGIN属性的角色才可以用于数据库连接，因此我们可以将具有该属性的角色视为登录用户，创建方法有如下两种：
    CREATE ROLE name LOGIN PASSWORD '123456‘;
    CREATE USER name PASSWORD '123456';
    
    2. 超级用户：
    数据库的超级用户拥有该数据库的所有权限，为了安全起见，我们最好使用非超级用户完成我们的正常工作。和创建普通用户不同，创建超级用户必须是以超级用户的身份执行以下命令：
    CREATE ROLE name SUPERUSER;
    
    3. 创建数据库：
    角色要想创建数据库，必须明确赋予创建数据库的属性，见如下命令：
    CREATE ROLE name CREATEDB;
    
    4. 创建角色：
    一个角色要想创建更多角色，必须明确给予创建角色的属性，见如下命令：
    CREATE ROLE name CREATEROLE;
    
三、权限：

    数据库对象在被创建时都会被赋予一个所有者，通常而言，所有者就是执行对象创建语句的角色。对于大多数类型的对象，其初始状态是只有所有者(或超级用户)可以对该对象做任何事情。如果要允许其它用户可以使用该对象，必须赋予适当的权限。PostgreSQL中预定义了许多不同类型的内置权限，如：SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。
    我们可以使用GRANT命令来赋予权限，如：
    GRANT UPDATE ON accounts TO joe;
    对于上面的命令，其含义为将accounts表的update权限赋予joe角色。此外，我们也可以用特殊的名字PUBLIC把对象的权限赋予系统中的所有角色。在权限声明的位置上写ALL，表示把适用于该对象的所有权限都赋予目标角色。
    要撤销权限，使用合适的REVOKE命令：
    REVOKE ALL ON accounts FROM PUBLIC;
    其含义为：对所有角色(PUBLIC)撤销在accounts对象上的所有权限(ALL)。

四、角色成员：

    在系统的用户管理中，通常会把多个用户赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤消。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership权限赋给独立的用户角色即可。
    1. 创建一个组角色，通常而言，该角色不应该具有LOGIN属性，如：
    CREATE ROLE name;    
    2. 使用GRANT和REVOKE命令添加和撤消权限：
    GRANT group_role TO role1, ... ;
    REVOKE group_role FROM role1, ... ;

 　 一个角色成员可以通过两种方法使用组角色的权限，如：
    1. 每个组成员都可以用SET ROLE命令将自己临时"变成"该组成员，此后再创建的任何对象的所有者将属于该组，而不是原有的登录用户。
    2. 拥有INHERIT属性的角色成员自动继承它们所属角色的权限。
    见如下示例：
    CREATE ROLE joe LOGIN INHERIT;  --INHERIT是缺省属性。
    CREATE ROLE admin NOINHERIT;
    CREATE ROLE wheel NOINHERIT;
    GRANT admin TO joe;
    GRANT wheel TO admin;
    现在我们以角色joe的身份与数据库建立连接，那么该数据库会话将同时拥有角色joe和角色admin的权限，这是因为joe"继承 (INHERIT)"了admin的权限。然而与此不同的是，赋予wheel角色的权限在该会话中将不可用，因为joe角色只是wheel角色的一个间接成员，它是通过admin角色间接传递过来的，而admin角色却含有NOINHERIT属性，这样wheel角色的权限将无法被joe继承。
　 这样wheel角色的权限将无法被joe继承。此时，我们可以在该会话中执行下面的命令:
    SET ROLE admin;
    在执行之后，该会话将只拥有admin角色的权限，而不再包括赋予joe角色的权限。同样，在执行下面的命令之后，该会话只能使用赋予wheel的权限。
    SET ROLE wheel;
    在执行一段时间之后，如果仍然希望将该会话恢复为原有权限，可以使用下列恢复方式之一：
    SET ROLE joe;
    SET ROLE NONE;
    RESET ROLE;
    注意: SET ROLE命令总是允许选取当前登录角色的直接或间接组角色。因此，在变为wheel之前没必要先变成admin。 
    角色属性LOGIN、SUPERUSER和CREATEROLE被视为特殊权限，它们不会像其它数据库对象的普通权限那样被继承。如果需要，必须在调用SET ROLE时显式指定拥有该属性的角色。比如，我们也可以给admin角色赋予CREATEDB和CREATEROLE权限，然后再以joe的角色连接数据库，此时该会话不会立即拥有这些特殊权限，只有当执行SET ROLE admin命令之后当前会话才具有这些权限。 
    要删除一个组角色，执行DROP ROLE group_role命令即可。然而在删除该组角色之后，它与其成员角色之间的关系将被立即撤销(成员角色本身不会受影响)。不过需要注意的是，在删除之前，任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，与此同时，任何赋予该组角色的权限也都必须被撤消。