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_namefirst_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 多列索引以及多列索引中列顺序的重要性。