10.4. 值存储

要插入表中的数值也根据下面的步骤转换成目标列的数据类型。

值存储数据类型解析

   1.

      查找与目标列准确的匹配。
   2.

      否则，试着将表达式直接转换成目标类型。如果已知该两种类型之间存在一个已注册的转换函数，那么这样做就是可以的。如果表达式是一个未知类型文本，该文本字串的内容将交给目标类型的输入转换过程。
   3.

      检查一下看看目标类型是否有测长转换。测长转换是一个从某类型到自身的转换。如果在 pg_cast 表里面找到这么一个，那么在存储到目标字段之前先在表达式上应用。这样的转换的实现函数总是接受一个额外的类型为 integer 的参数，它接收目标字段的声明成都（实际上是其 atttypmod 值； atttypmod 的解释印不同的数据类型而不同）。转换函数负责施加那些长度相关的语义，比如长度检查或者阶段。

Example 10-6. character 存储类型转换

对一个目标列定义为 character(20) 的语句，下面语句确保存储值的正确定长：

CREATE TABLE vv (v character(20));
INSERT INTO vv SELECT 'abc' || 'def';
SELECT v, length(v) FROM vv;

          v           | length
----------------------+--------
abcdef               |     20
(1 row)

这里真正发生的事情是两个 unknown 文本解析缺省成text，这样就允许 || 操作符解析成text连接。然后操作符的text结果转换成bpchar （"空白填充的字符型"，character类型内部名称）以匹配目标字段类型。（不过，因为知道text和bpchar是二进制兼容的，这样的转换是隐含的并且实际上不做任何函数调用。）最后，在系统表里找到测长函数 bpchar(bpchar,integer) 并且应用于该操作符的结果并存储字段长。这个类型相关的函数执行所需要的长度检查和额外的空白填充。

10.5. UNION，CASE 和 ARRAY构造

SQL 的 UNION 构造必须把那些可能不太相似的类型匹配起来成为一个结果集。解析算法分别应用于联合查询的每个输出字段。 INTERSET 和EXCEPT构造对不相似的类型使用和 UNION相同的算法进行解析。 CASE 和 ARRAY 构造也使用同样的算法匹配它的部件表达式并且选择一个结果数据类型。

UNION，CASE 和 ARRAY 类型解析

   1.

      如果所有输入都是类型 unknown，解析成类型text（字串类型的首选类型）。否则，在选择结果类型的时候忽略 unknown 输入。
   2.

      如果非 unknown 输入不是全部属于一个类型范畴，失败。
   3.

      选取第一个属于该范畴中首选类型的非未知输入类型，或者是第一个允许所有非未知输入隐含转换成它的类型。
   4.

      把所有输入转换成所选类型。

下面是一些例子。

Example 10-7. Union 中的待定类型解析

SELECT text 'a' AS "Text" UNION SELECT 'b';

Text
------
a
b
(2 rows)

这里，未知类型文本'b'将被解析成类型text。

Example 10-8. 简单 Union 中的类型解析

SELECT 1.2 AS "numeric" UNION SELECT 1;

numeric
---------
       1
     1.2
(2 rows)

文本 1.2 类型为 numeric，而且整数1可以隐含地转换为numeric，因此使用这个类型。

Example 10-9. 转置 Union 中的类型解析

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

real
------
    1
  2.2
(2 rows)

这里，因为类型 real 不能被隐含转换成 integer，但是 integer 可以隐含转换成 real，那么联合的结果类型分析成 real。

11.1. 介绍

索引使用的一个经典的例子就是有象下面这样的一个表∶

CREATE TABLE test1 (
    id integer,
    content varchar
);

并且应用需要大量的从下面查询语句进行的查询

SELECT content FROM test1 WHERE id = constant;

通常，数据库系统将不得不一行一行地扫描整个 test1 以寻找所有匹配的记录。 如果在 test1 里面有许多行， 但是只返回少数几行（可能是零个或者是一个）， 那么上面这个方法可就不是个有效的方法。 如果我们让数据库系统在 id 列上维护一个索引用于定位匹配的行。 这样，数据库系统就可能只需要在搜索树中走少数的几层就可以找到匹配行。

在大多数非小说的书籍里面都使用了类似这样的方法∶ 在书的背后收集着读者会经常查找的术语和概念的索引， 并按照字母顺序排列。 有兴趣的读者可以相对快速地扫描该索引并且切换到合适的页， 因此就不用阅读整本书才能查找到感兴趣的位置。 作者的任务之一就是预计哪些项是读者最需要查找的东西， 与之类似，预计哪些索引可以带来便利也使数据库程序员的任务。

下面的命令可以用于在 id 列上创建我们讨论那样的索引∶

CREATE INDEX test1_id_index ON test1 (id);

你可以自由选择索引名子 test1_id_index， 但是应该选那些稍后可以让你回忆起你索引的是干什么的名字。

要删除一个索引，使用 DROP INDEX 命令。 你可以在任何时候向表里增加索引或者从表中删除索引。

一旦你创建了索引，那么就不在需要更多的干涉了∶ 当系统认为用索引表顺序的表扫描快的时候它就会使用索引。 不过你可能必须经常性地运行ANALYZE 命令以更新统计信息，好让查询规划器能够做出有训练的判断。 又见 Chapter 13 获取关于如何获知是否使用了索引的信息， 以及何时何因下规划器会决定不使用索引。

索引对带搜索条件的 UPDATE 和 DELETE 也有好处。 索引更可以用于表连接查询。因此，如果你定义了索引的列是连接条件的一部分， 那么它可以显著提高带连接的查询的速度。

在创建索引的时候，它必须和表保持同步。这些操作增加了数据操作的负荷。 因此我们应该把那些非关键或者根本用不上的索引删除掉。 请注意一个查询或者一次数据操作命令在一个表上最多可以使用一个索引。

11.2. 索引类型

PostgreSQL 提供了好几种索引类型∶ B-tree，R-tree，Hash 和 GiST。每种索引类型都比较适合某些特定的查询类型， 因为它们用了不同的算法。 缺省时，CREATE INDEX 命令将创建一个 B-tree 索引， 它适合大多数情况。

B-tree 可以处理那些可以按照某种顺序存储的数据的等于和范围查询。 特别是在一个建立了索引的列涉及到使用下列操作符之一进行比较的时候， PostgreSQL 的查询规划器都会考虑使用 B-tree 索引∶

<
<=
=
>=
>

构造等效于这些操作符的组合，比如 BETWEEN 和 IN， 也可以用 B-tree 索引搜索视线。（但是要注意，IS NULL 不等于 =， 并且是不能建立索引的。）

优化器也会把 B-tree 所以用于涉及模式匹配操作符 LIKE， ILIKE，~，和 ~* 的查询，条件是模式锚接在字串的开头，比如， col LIKE 'foo%' 或者 col ~ '^foo'， 而不是 col LIKE '%bar'。 但是，如果你的服务器不适用 C 区域，那么你需要用一个特殊的操作符表创建索引来支持模式匹配查询上的索引。 参阅下面的 Section 11.6。

R-tree 索引特别适合于空间数据。要创建一个 R-tree 索引， 使用下面形式的命令

CREATE INDEX name ON table USING RTREE (column);

当一个索引了的列涉及到使用下列操作符之一进行比较的时候， PostgreSQL 的查询规划器都会考虑使用 R-tree 索引∶

<<
&<
&>
>>
@
~=
&&

(请参考 Section 9.10 获取这些操作符的含义。)

散列（hash）索引只能处理简单的等于比较。 当一个索引了的列涉及到使用 = 操作符进行比较的时候， 查询规划器会考虑使用散列索引。 下面的命令用于创建散列索引∶

CREATE INDEX name ON table USING HASH (column);

    注意: 测试表明 PostgreSQL 的散列索引和 B-tree 索引类似或者慢一些， 而且散列索引的尺寸和制作时间要差很多。而且在高度并发的条件下，散列 索引的性能也很差。因此，目前我们不建议使用散列索引。

GiST 索引不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。 因此，可以使用 GiST 索引的特定操作符类型根据索引策略的不同而不同（操作符表）。 祥见 Chapter 48。

B-tree 索引是一个 Lehman-Yao 高并发 B-tree 的实 现。R-tree 索引用 Guttman 的二次分割算法实现了标准的 R-tree。 hash（散列）索引是 Litwin 的线性散列的一个实现。 我们单独的列出这些所用的算法是要表明所有这些索引方法都是完全动态的并且不必进行周期性的优化 （例如，象静态散列算法常见的那样）。

11.3. 多字段索引

一个索引可以定义在多于一个字段上。比如，如果年有象下面这样的表∶

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

(比如，把你的 /dev 目录保存在一个数据库里。。。)并且你经常做下面这样的查询

SELECT name FROM test2 WHERE major = constant AND minor = constant;

那么也许我们在字段 major 和 minor 上一起定义一个索引是比较合适的做法，也就是∶

CREATE INDEX test2_mm_idx ON test2 (major, minor);

目前，只有 B-tree 和 GiST 实现支持多字段索引。 缺省最多可以声明 32 个字段（这个限制可以在制作 PostgreSQL时改变， 见文件 pg_config_manual.h）。

查询规划器可以将多字段索引用于那些涉及索引定义中最左边的字段， 以及在其右边列出任意数目的无空隙字段的查询(在使用了合适的操作符的情况下)， 涉及的字段最多可以到索引定义的字段数目。比如，一个定义在 (a, b, c) 上的索引可以用于所有涉及 a，b，和 c 全部的查询，或者那些同时包含 a 和 b 的查询，或者那些只包括 a，不包括其它的查询。 （在一个只涉及 a 和 c 的查询里，规划器可能会只使用用于 a 的索引， 而把 c 当做一个普通的没有索引的字段看待。） 当然，每个字段必须是在那些适用于该索引类型的操作符里使用； 那些涉及其它操作符的子句将不会被考虑。

只有在涉及索引字段的子句是用 AND 连接的时候才会使用多字段索引。比如，

SELECT name FROM test2 WHERE major = constant OR minor = constant;

无法利用我们上面定义的 test2_mm_idx 来查找两个字段。(不过，你可以用它查找 major 字段。)

我们应该节俭地使用多字段索引。在大多数时候，一个在单一的字段上 的索引就足够用了，并且它还可以节约时间和空间。 除非以非常特定的方式使用，否则包含多于三个字段的索引几乎没什么用。

11.4. 唯一索引

索引还可以用于强迫字段数值的唯一性，或者是多个字段组合值的唯一性。

CREATE UNIQUE INDEX name ON table (column [, ...]);

目前，只有 B-tree 索引可以声明为唯一的。

如果索引声明为唯一的，那么就不允许出现多个索引值相同的行。 我们认为 NULL 值相互间不相等。 一个多字段唯一索引认为只有两行数据里所有被索引字段都相同的时候才是相同的，这种数据才被拒绝。

如果一个表声明了一个唯一约束或者一个主键， 那么 PostgreSQL 自动在那些组成主键或者唯一字段的列上创建唯一索引(可能地话是一个多字段索引)， 以强迫这些约束。

    注意: 给一个表增加唯一约束的比较好的办法是 ALTER TABLE ... ADD CONSTRAINT。 用索引强制唯一约束应该认为是一个实现细节，而不应该直接访问。 不过，我们应该知道，我们没有必要在唯一字段上建立索引，那样做只会重复建立自动创建的索引。

11.5. 表达式上的索引

索引字段并非一定要是一个下层表的字段，而可以是一个函数或者从表中一个或多个字段计算出来的标量表达式。 这个特性对于快速访问那些基于计算结果的表非常有用。

比如，做大小写无关比较的常用方法是使用函数 lower∶

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果我们在 lower(col1) 操作的结果上定义索引， 那么这个查询就可以使用索引∶

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

如果我们要把这个索引声明为 UNIQUE，那么它会禁止创建那种 col1 数值只是大小写有区别的数据行， 以及 col1 数值完全相同的数据行。因此，在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。

另外一个例子是，如果我们经常使用下面这样的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那么我们就值得创建下面这样的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX 命令的语法通常要求在索引表达式周围书写圆括弧， 就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略， 就像我们在第一个例子里显示的那样。

从维护角度来看，索引表达式相对费劲一些，因为在插入数据行或者更新数据行的时候， 都必须为每一行计算生成的表达式。因此，只有在查询使用该索引非常频繁的时候才使用表达式索引。

11.6. 操作符表

一次索引定义可以为索引的每个字段声明一个 操作符表(operator class)。

CREATE INDEX name ON table (column opclass [, ...]);

这个操作符表标识该索引用于该字段要使用的操作符。 例如，一个在int4上的 B-tree 索引将使用 int4_ops 表； 这个操作符表包括用于int4的比较函数。 实际上，该字段类型的缺省操作符通常就足够了。 拥有操作符表的原因是：对于某些数据类型，可能存在多于一个有意义的索引行为。 例如，我们可能想排序两个复数，既可能通过绝对值， 也可能通过实部。我们可以通过为该数据类型定义两个操作符表，然后在建立索引时选择合适的一个来实现这个目的。

除了缺省的以外，还有一些有内置的操作符表：

    *

      操作符表 text_pattern_ops，varchar_pattern_ops， bpchar_pattern_ops，和 name_pattern_ops 分别支持在类型 text，varchar，char，和 name 上的 B-tree 索引。他们与初始的操作符表的区别是数值是严格地一个一个字符比较的， 而不是根据区域相关的集合规则进行比较。 这样，如果服务器不使用标准的 "C" 区域设置， 那么这些操作符表适合用于那些涉及模式匹配（LIKE 或者 POSIX 正则表达式）表达式的查询。 举一个例子，你可以像下面这样对一个 varchar 字段进行索引：

      CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

      如果你 确实使用了 C 区域，那么你可以创建一个使用缺省操作符表的索引， 而即使这样，它也会对模式匹配查询有用。 还要注意的是，如果你希望那些涉及到普通比较的查询也使用索引， 那么你应该创建用缺省操作符表的索引。这样的查询不能使用 xxx_pattern_ops 操作符表。系统允许在同一个字段上创建多个使用不同操作符表的索引。

下面的查询显示所有已定义的操作符表：

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcamid = am.oid
    ORDER BY index_method, opclass_name;

我们可以把它扩展为显示每个表中的所有操作符：

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opr.oprname AS opclass_operator
    FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
    WHERE opc.opcamid = am.oid AND
          amop.amopclaid = opc.oid AND
          amop.amopopr = opr.oid
    ORDER BY index_method, opclass_name, opclass_operator;

11.7. 部分索引

部分索引（partial index） 是建立在一个表的子集上的索引； 该子集是由一个条件表达式定义的（叫做部分索引的谓词）。 该索引只包含表中那些满足这个谓词的行。

部分索引的主要动机是为了避免对普通数值建立索引， 因为如果一个在一个普通数值（那种占表中所有行超过几个百分点的数值）上的查询不会使用索引， 那么就没有在索引中保存这些（普通）行的必要。 这样就可以减小索引的尺寸，这样就可以提高那些真正使用索引的查询的速度。 同时它也能提高许多表更新操作的速度，因为不是所有情况都需要更新索引。 Example 11-1 显示了一个潜在的这方面应用的例子。

Example 11-1. 设置一个部分索引以排除普通数值

假设你在一个数据库中存储 web 服务器的访问日志。 大多数访问是从你的组织内部的 IP 地址范围发起的， 但也有一小部分来自其它地方（比如那些通过拨号进行联接的雇员）。 如果你搜索的主要是来自外部访问的 IP，那么你可能就不需要对那些对应你的组织的子网的 IP 范围进行索引。

假设表象下面这样∶

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

要创建符合我们的例子的索引，使用象下面这样的命令∶

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';

我们通过观察可以看出，这种类型的部分索引要求普通数值是可以预计的。 如果数值的分布是固有的（来自应用自身的性质）并且是静态的（不随时间而改变）， 那做到这一点（跟踪普通数值）并不困难，但是如果普通数值只是因为一致的数据装载， 那么它可能就要花很多维护性工作。

另外一个可能是把那些典型的查询工作不感兴趣的数值排除在 索引之外；这个可能在 Example 11-2 里显示。 这个结果有与上面列出的同样的优点，但是它完全拒绝了通过索引 访问"不感兴趣"的数值，即使索引扫描可能对那些数据 也有利。显然，为这种情况设置部分索引需要非常仔细以及需要大量试验。

Example 11-2. 设置一个部分索引以排除不感兴趣的数值

如果你有一个表，包含已付款和未付款的定单，而未付款的定单只占总表的一小部分并且它是经常使用的部分， 那么你可以通过只在未付款定单上创建一个索引来改善性能。创建索引的命令看起来会象这样∶

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;

不过，该索引也可以用于那些完全不涉及 order_nr 的查询，比如，

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这个查询不象在 amount 字段上的部分索引那么有效， 因为系统必须扫描整个索引。但是，如果未付款的定单相对较少， 那么用这个部分索引找出未付款的定单将会更快些。

请注意下面这个查询无法使用这个索引∶

SELECT * FROM orders WHERE order_nr = 3501;

定单 3501 可能是已付款也可能是未付款。

Example 11-2 还说明了建了索引的字段和在谓词中使用的字段不必相配。 PostgreSQL 支持带任意谓词的部分索引，只要是只涉及被索引表的字段就行。 不过，我们要记住的是谓词必须和那些希望从该索引中获益的查询中的条件相匹配。 准确说，只有在系统能够识别出该查询的WHERE条件在数学上蕴涵了该索引的谓词时， 这个部分索引才能用于该查询。 PostgreSQL 还没有复杂的理论校定用于识别那些形式不同但数学上相等的谓词。 （做这样的理论校定不仅非常困难，而且在实际使用中也可能非常慢。） 系统可以识别简单的不相等蕴涵，比如 "x < 1" 蕴涵 "x < 2"； 否则，谓词条件必须准确匹配查询的WHERE条件，要不然系统将无法识别该索引是可用的。

部分索引的第三种可能用途完全不要求索引在查询中得到使用。 这里的概念是在一个表的一个子集里创建一个唯一索引， 如 Example 11-3 里描述。 这样就强制在满足谓词的行中的唯一性，而不用约束那些不需要唯一的行。

Example 11-3. 设置一个部分唯一索引

假设我们有一个描述测试输出的表。我们希望确保在一定的目标和课题的组合中只有一个"成功"记录， 但是可以有任意数量的"不成功"记录。下面是实现方法∶

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

如果只有少数成功测试而有很多不成功测试，那么这是一种非常有效的实现方法。

最后，部分索引也可以用于覆盖系统选择的查询规划。 可能会出现这样的情况∶如果数据集的分布是比较特定的形状， 那么会导致系统在不该使用索引的时候使用它。在这种情况下， 我们可以把索引设置为在违反(规律)的查询中不可用。 通常 PostgreSQL 对索引的使用是会做合理的选择的（比如，它在检索普通数值的时候避免使用它， 因此前面的例子实际上只是节约了索引的尺寸，它并不要求避免索引的使用）， 而如果出现了错误的规划选择那么请提交一个臭虫报告。

请记住一件事∶设置一个部分索引表示你至少和查询规划器知道的一样多， 特别是你知道什么场合里索引是有效的。要形成这些只是要求富有经验并且理解 PostgreSQL 里的索引是如何运做的。 在大多数情况下，部分索引对普通索引的优势并不太明显。

更多有关部分索引的信息可以在 部分索引的实例， Partial indexing in POSTGRES: research project，和 Generalized Partial Indexes 获得。

11.8. 检查索引的使用

尽管在 PostgreSQL 里的索引并不需要维护和调节， 但是检查一下哪些索引是在实际查询工作中得到使用的仍然是非常重要的。 检查索引的使用是通过 EXPLAIN 命令进行的； 为此目的做的应用在 Section 13.1 里演示。 我们也可以在一个运行的服务器上收集有关索引使用的全部可能性， 就想在 Section 23.2 里描述的那样。

归纳一个判断需要设置哪些索引的通用过程是很难的。 在前面的章节中已经列出了许多典型的例子。 在大多数情况下我们都需要许多试验。本节的剩余部分就是给出一些这方面的窍门。

    *

      总是先运行 ANALYZE。 这条命令收集关于表中数值分布的统计。猜测一个查询返回的行数需要这个信息， 而规划器需要这个行数以便给每个可能的查询规划赋予真实开销值。 如果缺乏任何真实的统计信息，那么就会假设一些缺省数值， 那肯定是不准确的。因此，如果还没有运行 ANALYZE 就检查一个应用的索引使用状况，那实际上就是一次失败的检查。
    *

      使用真实的数据做实验。用测试数据设置索引将告诉你在测试数据中需要什么索引，而不是在所有数据中。

      最要命的是用很小的数据集。如果从 100000 行中选 1000 行是使用索引的好时机， 那么从 100 行中选 1 行很难说也需要索引， 因为 100 行很可能是装在一个磁盘页里面的， 因此没有任何查询规划能比通过顺序访问抓取一个磁盘页面更有效。

      做测试数据的时候也要小心 -- 如果应用还不能在生产环境中使用， 那么这也是不可避免的。那些非常相似的数据，完全随机的数据， 或者按照排序顺序插入的数据会令统计信息偏离实际数据应该具有的特征。
    *

      如果索引没有得到使用，那么在测试中强制它的使用也许有些价值。 有一些运行时参数可以关闭各种各样的查询规划（在 Section 16.4 中描述）。 比如，关闭顺序扫描（enable_seqscan）和嵌套循环连接（enable_nestloop）将强迫系统使用不同的规划。 （这些规划是最基本的规划。）如果系统仍然选择顺序扫描或者嵌套循环联接， 那么在为何索引没有得到使用的问题中可能有更基本的问题， 比如，查询条件和索引不匹配等。（我们在前面的章节中介绍了什么样的查询可以使用什么样的索引。）
    *

      如果强制索引用法的确使用了索引，那么就有两种可能∶ 要么是系统选择是正确的∶使用索引实际上并不合适， 要么是查询计划的开销计算并不反映现实情况。 这样你就应该对使用和不使用索引的查询进行计时。这个时候 EXPLAIN ANALYZE 命令就很有用了。
    *

      如果实际情况说明开销计算是错误的，那么仍然有两种可能。 总开销是从每行的每个规划节点乘以每个规划节点的选择性估计的开销计算出来的。 规划节点的开销可以用一些运行时参数进行调节（在 Section 16.4 中描述）。 不准确的选择性估计是因为统计信息不够充分。 我们可以通过调节统计收集参数（参阅 ALTER TABLE）提高选择性估计的精确度。

      如果你没能通过将开销调整得更准确实现索引的使用，那么你可能不得不 求助于明确地强制索引使用。而且也许与 PostgreSQL 开发人员联系并且讨论你的情况也是值得考虑的。