PostgreSQL / 系统课时

创建表达式索引

创建表达式索引

本课解决“查询对列做了函数处理,普通索引为什么失效”的问题。你将为表达式结果建立索引,并让查询条件与索引表达式保持一致。

学习目标

  • 理解普通列索引与表达式索引的区别
  • 为常用函数结果创建表达式索引
  • 验证查询表达式是否精确匹配索引定义

为什么普通索引可能无法使用

假设已经为 email 创建普通索引,但查询写成 lower(email) = 'alice@example.com'。数据库需要比较的是函数计算结果,而普通索引保存的是原始邮箱值,两者不是同一个索引键。

表达式索引会在写入或更新时计算表达式,并把结果保存到索引中。查询再次使用相同表达式时,就能直接搜索这些预先计算的键值。

为 lower(email) 建立索引

sql
-- 重建演示表,保证脚本可以重复运行
DROP TABLE IF EXISTS demo_users;

CREATE TABLE demo_users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email varchar(200) NOT NULL,
    display_name varchar(100) NOT NULL
);

-- 邮箱故意混合大小写,模拟规范化查询
INSERT INTO demo_users (email, display_name)
SELECT
    'User' || g || '@Example.com',
    'user-' || g
FROM generate_series(1, 10000) AS g;

-- 索引键保存 lower(email) 的计算结果
CREATE INDEX idx_demo_users_lower_email
ON demo_users (lower(email));

ANALYZE demo_users;
SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT id, email, display_name
FROM demo_users
WHERE lower(email) = 'user5000@example.com';

预期结果

text
执行计划的核心节点包含:
Index Scan using idx_demo_users_lower_email on demo_users
Index Cond: lower(email) = 'user5000@example.com'

查询中的 lower(email) 与索引定义一致,因此能够使用表达式索引。

查询写法必须匹配表达式

表达式索引不是“模糊匹配”。如果索引定义为 lower(email),查询改成 upper(email)、trim(lower(email)) 或其他不同表达式,通常无法使用该索引。

常量一侧可以正常变化,例如不同的小写邮箱值仍能复用同一个索引。关键是被索引列上的表达式结构要一致。

表达式也可以组合

可以把表达式与普通列放进同一个联合索引,例如限制同一租户内邮箱忽略大小写后唯一。

例如,可使用片段 CREATE UNIQUE INDEX uq_users_tenant_lower_email ON users (tenant_id, lower(email)); 限制同一租户内的邮箱忽略大小写后唯一。该片段依赖现有 users 表以及 tenant_id、email 两列。生效后,同一租户内的 Alice@example.com 与 alice@example.com 会被视为重复值。

成本与适用边界

表达式索引能减少查询时的重复计算,但会增加写入成本,因为每次插入或相关列更新时都要重新计算表达式并维护索引。

应只为稳定、高频、可重复使用的表达式建立索引。对于偶发查询或高频变化字段,先通过执行计划确认收益。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建表达式索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通