PostgreSQL 教程: 表达式索引

九月 10, 2023

摘要:在本教程中,您将学习如何利用表达式上的索引来提高涉及表达式的查询的性能。

PostgreSQL 表达式索引简介

通常,在您创建一个索引时,会引用表的一列或多列。但您也可以基于涉及表列的表达式创建索引,该索引称为表达式索引。

表达式上的索引也称为基于函数的索引。

在表达式上创建索引的语法如下:

CREATE INDEX index_name 
ON table_name (expression);

在这个语句中:

  • 首先,在CREATE INDEX子句后指定索引的名称。
  • 然后,形成一个涉及表table_name的列的表达式。

一旦定义了索引表达式,当定义索引的表达式出现在 SQL 语句的WHERE子句或ORDER BY子句中时,PostgreSQL 将考虑使用该索引。

请注意,表达式上的索引维护起来非常昂贵,因为 PostgreSQL 必须在插入更新每一行时计算该表达式,并使用结果值进行索引。因此,当检索速度比插入和更新速度更重要时,您才应该在表达式上使用索引。

PostgreSQL 表达式索引示例

让我们看看示例数据库中的customer表。

customer table

customer表具有为first_name列定义的 B 树索引。以下查询查找姓氏为Purdy的客户:

SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    last_name = 'Purdy';

执行此查询时,PostgreSQL 使用了idx_last_name索引,如下面的EXPLAIN语句所示:

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    last_name = 'Purdy';

PostgreSQL Index On Expression Example

以下语句查找姓氏小写为purdy的客户。然而,PostgreSQL 无法利用索引进行查找:

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    LOWER(last_name) = 'purdy';

PostgreSQL Index On Expression LOWER function seq scan

为了改进这个查询,您可以定义一个索引表达式,如下所示:

CREATE INDEX idx_ic_last_name
ON customer(LOWER(last_name));

现在,以不区分大小写的方式,根据姓氏查找客户的查询将使用表达式上的索引,如下所示:

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    LOWER(last_name) = 'purdy';

PostgreSQL Index On Expression - example of index with LOWER function

在本教程中,您学习了如何使用表达式上的 PostgreSQL 索引,来改进具有涉及表列的表达式的查询。