Postgresql 中文操作指南

11.1. Introduction #

假设我们有一张类似以下内容的表：

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 命令来更新统计信息，以便查询计划程序可以做出明智的决策。有关如何确定是否使用索引以及计划程序何时何原因决定 not 使用索引的信息，请参阅 Chapter 14。

在具有搜索条件的 UPDATE 和 DELETE 命令中，索引也可以受益。此外，可以在联接搜索中使用索引。因此，在联接条件中以部分形式定义的索引也可以显著加速具有联接的查询。

通常，PostgreSQL 索引可用于优化包含一个或多个以下形式的 WHERE 或 JOIN 子句的查询

indexed-column indexable-operator comparison-value

此处，indexed-column 是索引所定义所在的列或表达式。indexable-operator 是运算符，是索引列的索引 operator class 的成员。（有关该运算符的详细信息如下所示。）而 comparison-value 可以是不稳定的任何表达式，且不引用索引表。

在某些情况下，查询计划器可以从此类 SQL 结构中提取可索引子句。一个简单的示例是，如果原始子句为

comparison-value operator indexed-column

那么，如果原始 operator 带有 commutator 运算符且它为索引运算符类成员，则可以将其翻转为可索引表单。

在大型表上创建索引可能需要很长时间。默认情况下，PostgreSQL 允许在与索引创建并行的情况下对表进行读写（SELECT 语句），但在索引构建完成之前，写操作（INSERT、UPDATE、DELETE）被阻塞。在生产环境中，这种情况通常是不可接受的。可以让写入与索引创建并行进行，但需要注意几个注意事项 — 有关详细信息，请参阅 Building Indexes Concurrently。

在创建索引后，系统必须使其与表保持同步。这会给数据处理操作增加开销。索引还可以防止创建 heap-only tuples。因此，在查询中很少或从未使用的索引应被删除。