表达式索引是什么
PostgreSQL 的表达式索引把表达式的结果建进索引，例如对邮箱做小写转换后再建索引，或把时间戳转成日期后建索引。只有当查询条件与索引表达式完全一致时才能命中，多包一层函数都用不上。表达式必须是不可变函数，依赖当前时间的函数不能用于索引。
基本信息
| 适用平台 | PostgreSQL |
|---|---|
| 语法 | CREATE INDEX 名 ON 表 (表达式) |
| 命中条件 | 查询里的表达式要与索引表达式一致，函数、顺序都要对上 |
| 约束 | 表达式必须是 IMMUTABLE 函数 |
| MySQL 对应 | 8.0.13+ 的功能索引，表达式需用括号包裹 |
典型使用场景
- 邮箱、用户名做大小写不敏感查询
- 把时间戳按日期建索引做按天统计
- 对 JSON 字段的抽取结果建索引
- 对拼接后的字段建索引
- 统一历史数据里大小写不一致的检索
示例
示例
-- 建表达式索引
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- 命中：查询表达式与索引一致
SELECT * FROM users WHERE lower(email) = 'a@x.com';
-- 不命中：查询端多包了一层 trim
SELECT * FROM users WHERE trim(lower(email)) = 'a@x.com';
-- 按日期统计的场景：对转换后的表达式建索引
CREATE INDEX idx_orders_day ON orders ((created_at::date));
SELECT count(*) FROM orders WHERE created_at::date = DATE '2024-06-01';两库对照
| 维度 | PostgreSQL | MySQL |
|---|---|---|
| 语法 | CREATE INDEX … (表达式) | 功能索引，表达式需括号包裹 |
| 命中要求 | 表达式完全一致 | 同样要求一致 |
| 函数要求 | 必须是 IMMUTABLE | 表达式需为确定性 |
| 版本 | 长期支持 | 8.0.13 起 |
| 典型场景 | 小写转换、日期截断、JSON 抽取 | 同 |
索引表达式与查询表达式必须逐字符一致：lower(email) 的索引不会被 trim(lower(email)) 命中，写法稍微一变就等于白建。
不可变函数的限制来自索引的持久性：索引里存的是计算结果，函数行为必须稳定。需要按当前时间过滤的查询没法直接建索引，通常改用分区或预计算列。
表达式索引也能做成唯一索引：例如对转换后的邮箱建唯一索引，用来防止大小写不同的重复注册，普通唯一索引拦不住这种情况。
改表达式就等于换索引：旧索引不会自动更新，通常先建新索引再删旧的，期间两条索引并存会额外占用空间并加重写入开销。