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 索引，来改进具有涉及表列的表达式的查询。

搜索

分类

标签