PostgreSQL 表达式索引和生成列有什么区别？什么时候用？

简化版

表达式索引直接对表达式结果建索引，比如 lower(email)；生成列把表达式结果作为表字段保存，再对字段查询或建索引。表达式索引轻量，适合单一查询优化；生成列更显式，适合复用、展示、约束或多处查询。

详细版

如果查询经常写 WHERE lower(email) = lower(?)，普通 email 索引可能用不上，因为查询条件作用在表达式上。可以建表达式索引：

CREATE INDEX idx_user_lower_email ON users (lower(email));

生成列则把计算结果定义成列：

ALTER TABLE users
ADD COLUMN email_lower text GENERATED ALWAYS AS (lower(email)) STORED;

选择时看表达式是否稳定、是否多处复用、是否需要约束和可读性。两者都会增加写入维护成本。

完整版教学

一、为什么函数条件会影响索引

普通索引按原始列值组织。如果查询对列套函数，例如 lower(email)、date(created_at)，数据库不能总是直接用原列索引。

SELECT *
FROM users
WHERE lower(email) = 'tom@example.com';

这时索引如果只建在 email 上，排序的是原始大小写字符串，不是 lower(email) 的结果。

二、表达式索引怎么用

表达式索引把计算结果放进索引：

CREATE INDEX idx_user_lower_email
ON users (lower(email));

查询条件写法要和表达式匹配，优化器才容易使用。它适合“某个表达式就是高频过滤条件”的场景。

数字例子：1000 万用户里按邮箱忽略大小写查 1 个用户，没有表达式索引可能全表扫描；有表达式索引可以定位到少量匹配项。

三、生成列怎么用

生成列把表达式定义成表的一列。PostgreSQL 的 stored generated column 会把计算结果存储下来。

ALTER TABLE users
ADD COLUMN email_lower text
GENERATED ALWAYS AS (lower(email)) STORED;

CREATE INDEX idx_user_email_lower ON users(email_lower);

这种方式更显式，应用、报表、约束都能看到 email_lower 这个字段。

四、两者如何选择

维度	表达式索引	生成列
侵入性	低	表结构新增列
可读性	隐式	显式
复用	偏单查询	多处复用方便
约束	可建唯一表达式索引	字段约束更直观

如果只是优化一个查询，表达式索引通常更轻；如果计算结果是业务概念，生成列更适合。

五、维护成本和限制

表达式必须是可用于索引的稳定计算，不能依赖随时间变化的不确定结果。否则索引内容无法可靠维护。

每次插入或更新相关列时，数据库都要重新计算表达式并维护索引或生成列。频繁更新字段上不要随便堆很多表达式索引。

这类优化也要用 EXPLAIN ANALYZE 验证，避免建了索引但 SQL 写法不匹配。

六、常见误区与追问

• 误区：列上有索引，套函数查询也一定能用。 函数改变了索引匹配对象，可能需要表达式索引。
• 误区：表达式索引没有写入成本。 插入更新时仍要计算表达式和维护索引。
• 误区：生成列只是语法糖。 stored 生成列会存储结果，能被查询、索引和约束使用。
• 追问：忽略大小写邮箱唯一怎么做？ 可用 UNIQUE INDEX ON users(lower(email)) 或生成列加唯一约束。
• 追问：表达式写法不同会影响命中吗？ 会，实际要看优化器能否识别等价表达式。

七、加强记忆

记忆钩子：表达式索引像给计算结果偷偷做目录，生成列像把计算结果正式写进表格；一个轻，一个明。

回答这题要从“函数条件为什么破坏普通索引匹配”讲起，再比较表达式索引和生成列。这样能把查询优化和表设计一起讲透。