PostgreSQL 教程: 多列索引

九月 11, 2023

摘要：在本教程中，您将学习如何创建多列索引，这些索引是在表的多个列上定义的索引。

目录

PostgreSQL 多列索引简介

您可以在表的多个列上创建索引。该索引称为多列索引、复合索引、组合索引或串联索引。

多列索引最多可以包含表的 32 列。该限制可以通过构建 PostgreSQL 时修改pg_config_manual.h来更改。

此外，只有 B-tree、GIST、GIN 和 BRIN 索引类型支持多列索引。

以下语法显示了如何创建多列索引：

CREATE INDEX index_name
ON table_name(a,b,c,...);

定义多列索引时，应将WHERE子句中常用的列放在列列表的开头，将不常用的列放在条件中的后面。

在上面的语法中，PostgreSQL 优化器会在以下情况下考虑使用索引：

WHERE a = v1 and b = v2 and c = v3;

或者

WHERE a = v1 and b = v2;

或者

WHERE a = v1;

但是，在以下情况下不会考虑使用索引：

WHERE c = v3;

或者

WHERE b = v2 and c = v3;

PostgreSQL 多列索引示例

为了演示多列索引，我们将创建一个新表，名为people，包含三列：ID、名字和姓氏：

CREATE TABLE people(
    id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

您可以使用以下脚本将 10,000 行加载到people表中：

加载 10000 个姓名的脚本

以下语句查找姓氏为Adams的人：

SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams';

这是输出：

postgresql multicolumn index - seq scan

正如输出中清楚显示的那样，PostgreSQL 对people表执行顺序扫描以查找相应的行，因为没有为last_name列定义索引。

让我们在last_name和first_name列上定义一个 B 树索引。假设按姓氏搜索人员比按名字搜索人员更常见，我们按以下列顺序定义索引：

CREATE INDEX idx_people_names 
ON people (last_name, first_name);

现在，如果您搜索姓氏为Adams的人，PostgreSQL 优化器将使用索引，如以下语句的输出所示：

EXPLAIN SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams';

输出是：

postgresql multicolumn index - use index

以下语句查找姓氏为Adams、名字为Lou的人。

SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams'
AND first_name = 'Lou';

下面显示了输出：

img

PostgreSQL 优化器为此语句使用了索引，因为WHERE子句中的两列都在索引中：

EXPLAIN SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams'
AND first_name = 'Lou';

但是，如果您搜索名字为Lou的人，PostgreSQL 将对表执行顺序扫描，而不是使用索引，如以下语句的输出所示：

EXPLAIN SELECT
    *
FROM
    people
WHERE
    first_name = 'Lou';

postgresql multicolumn index - querying using minor column

即使first_name列是索引的一部分，PostgreSQL 也无法利用它。

需要注意的一个关键点是，当您定义多列索引时，您应该始终考虑业务上下文来查找哪些列经常用于查找，并将这些列放置在定义索引时列列表的开头。

在本教程中，您了解了 PostgreSQL 多列索引以及多列索引中列顺序的重要性。

搜索

分类

标签