PHP小码农

Appearance

表达式索引 ​

前面讲的索引都建立在「列的原始值」上：WHERE email = '...' 走 email 列的 B-Tree，WHERE price > 100 走 price 列的 B-Tree。但真实业务里的查询条件常常是列经过计算之后的结果：lower(email) = '...'、(data ->> 'user_id')::bigint = 42、date_trunc('month', created_at) = '2026-01-01'。这类条件在原始列的索引上一点用都没有。

表达式索引（Expression Index，也叫函数索引 Functional Index）就是为这种场景准备的：索引里存的不是列值，而是一个表达式的计算结果。本节讲清它的语法、IMMUTABLE 这条硬性门槛、查询为什么必须用「同一个表达式」才能命中，以及它在唯一性、JSONB 抽取、日期分组上的典型用法与代价。

前置知识

阅读本节前，建议先了解：CREATE INDEX 基础、B-Tree 索引原理

基础概念 ​

索引里存的是「计算后的值」 ​

普通索引的键就是列值本身；表达式索引的键是表达式求值之后的结果。二者的区别可以这样理解：

text
普通索引：  CREATE INDEX ON users (email)
            索引键 ← email 的原始值
            命中条件：WHERE email = 'A@Example.com'    ✅
                      WHERE lower(email) = 'a@example.com'  ❌ 用不上

表达式索引：CREATE INDEX ON users (lower(email))
            索引键 ← lower(email) 的结果（已转小写）
            命中条件：WHERE lower(email) = 'a@example.com'  ✅
                      WHERE email = 'A@Example.com'         ❌ 用不上

一句话概括：索引键长什么样，查询条件就得长什么样。 这不是优化器的「聪明程度」问题，而是数学上的必然——索引里根本没有 email 的原始值，它没法回答原始值的比较。

两个硬性前提 ​

前提说明违反的后果
表达式必须是 IMMUTABLE相同输入永远得到相同输出，且不能依赖任何会话状态建索引直接报错
查询表达式必须等价查询里的表达式要与索引定义求值结果一致不报错，但静默退化为顺序扫描

第二条是最容易踩的坑，因为它没有任何提示——EXPLAIN 里悄悄出现 Seq Scan，你以为索引在工作，其实没有。

表达式的易变性（Volatility）分类 ​

PostgreSQL 把每个函数按其「可预测程度」分成三档，记录在 pg_proc.provolatile：

取值名称含义能否用于索引
iIMMUTABLE相同输入永远相同输出，不读任何外部状态可以
sSTABLE同一事务内相同输入结果相同，但可能受配置/时间影响不可以
vVOLATILE每次调用都可能不同不可以

典型的易变性归属：

函数 / 表达式易变性能否建表达式索引
lower(email) / upper(email)IMMUTABLE✅
md5(content) / length(name)IMMUTABLE✅
(a + b) / a * 2IMMUTABLE✅
(data ->> 'user_id')::bigintIMMUTABLE✅
date_trunc('month', ts)（ts 是 timestamp）IMMUTABLE✅
date_trunc('month', ts)（ts 是 timestamptz）STABLE❌
to_tsvector('english', body)（显式给配置）IMMUTABLE✅
to_tsvector(body)（用默认配置）STABLE❌
now() / CURRENT_TIMESTAMPSTABLE❌
random() / gen_random_uuid()VOLATILE❌

关键点：同一个函数名的不同重载，易变性可能不同

date_trunc('month', ts) 在 ts 是 timestamp 时是 IMMUTABLE，在 ts 是 timestamptz 时却是 STABLE——因为后者会受会话的 TimeZone 设置影响，同一个值在不同会话里可能落在不同月份上。建索引前必须确认自己用的是哪个重载。

语法与用法 ​

text
-- 基本形式：把表达式写在列位置，用括号包住
CREATE [ UNIQUE ] INDEX 索引名 ON 表名 (表达式);

-- 注意：表达式「通常」必须额外加一层括号
--   函数调用形式可以省略（lower(email) 直接写）
--   运算符表达式必须写双括号：((a + b))
CREATE INDEX 索引名 ON 表名 ((列名 + 列名));

-- 多列索引中混用普通列与表达式
CREATE INDEX 索引名 ON 表名 (tenant_id, lower(email));

-- 指定操作符类 / 排序方向 / NULLS 位置
CREATE INDEX 索引名 ON 表名 (lower(email) text_pattern_ops);
CREATE INDEX 索引名 ON 表名 (lower(email) DESC NULLS LAST);

-- 覆盖列（PostgreSQL 11+）
CREATE INDEX 索引名 ON 表名 (lower(email)) INCLUDE (id, name);

-- 部分索引 + 表达式索引
CREATE INDEX 索引名 ON 表名 (lower(email)) WHERE deleted_at IS NULL;

-- 在线创建
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (lower(email));

-- 唯一表达式索引（实现大小写不敏感的唯一性）
CREATE UNIQUE INDEX 索引名 ON 表名 (lower(email));

最小可运行示例：

sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text   NOT NULL,
  name  text   NOT NULL
);

CREATE INDEX idx_users_lower_email ON users (lower(email));
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

表达式索引的本质：先算，再排序 ​

PostgreSQL 在处理表达式索引时做的事非常直白：写入时算出表达式结果，放进 B-Tree 当键；查询时把查询里的表达式也算一遍，再拿结果去 B-Tree 里比。

sql
INSERT INTO users (email, name) VALUES
  ('Alice@Example.com', 'Alice'),
  ('BOB@example.com',   'Bob'),
  ('carol@example.com', 'Carol');

ANALYZE users;
text
INSERT 0 3
ANALYZE

此时索引里的键是三个已经转成小写的字符串：

text
lower(email) 键 →  行指针（TID）
alice@example.com  → (0, 1)
bob@example.com    → (0, 2)
carol@example.com  → (0, 3)

用相同表达式查询，就能走 Index Cond：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name FROM users WHERE lower(email) = 'alice@example.com';
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using idx_users_lower_email on users (actual rows=1 loops=1)
   Index Cond: (lower(email) = 'alice@example.com'::text)
   Buffers: shared hit=3
(3 rows)

而用原始列查询 WHERE email = 'Alice@Example.com'，索引一点忙也帮不上——计划会退化成 Seq Scan，原因见下文「查询必须用同一个表达式」。

IMMUTABLE：表达式索引的准入门槛 ​

这是建表达式索引时最先撞上的一堵墙。试图用非 IMMUTABLE 表达式建索引，PostgreSQL 会直接拒绝：

sql
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  amount     numeric(12,2) NOT NULL
);

-- 错误！timestamptz 转 date 依赖会话时区，是 STABLE
CREATE INDEX idx_bad_1 ON orders (created_at::date);

-- 错误！timestamptz 版 date_trunc 同样依赖会话的 TimeZone
CREATE INDEX idx_bad_2 ON orders (date_trunc('month', created_at));
text
ERROR:  functions in index expression must be marked IMMUTABLE

第二例比第一例更容易踩，因为它「看起来」完全没问题。原因前面说过：timestamptz 版的 date_trunc 依赖会话的 TimeZone，不是一个纯函数。要想索引它，必须把时区显式固定下来：

sql
-- 正确：先把 timestamptz 转成 UTC 的 timestamp，再 date_trunc
CREATE INDEX idx_orders_month ON orders (date_trunc('month', created_at AT TIME ZONE 'UTC'));

用 pg_get_indexdef() 核对索引定义的形态——AT TIME ZONE 'UTC' 在索引定义里被还原成了它的函数形式 timezone('UTC', created_at)：

sql
SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_month';
text
                                        indexdef
-----------------------------------------------------------------------------------------
 CREATE INDEX idx_orders_month ON public.orders USING btree (date_trunc('month', timezone('UTC'::text, created_at)))
(1 row)

要判断某个函数到底能不能用于索引，直接查系统表：

sql
SELECT proname, provolatile, progargtypes::regtype[] AS argtypes
FROM pg_proc
WHERE proname IN ('lower', 'now', 'date_trunc', 'to_tsvector', 'random')
ORDER BY proname, provolatile;
text
   proname   | provolatile |              argtypes
-------------+-------------+--------------------------------------
 date_trunc  | i           | {text,timestamp without time zone}
 date_trunc  | s           | {text,timestamp with time zone}
 lower       | i           | {text}
 now         | s           | {}
 random      | v           | {}
 to_tsvector | i           | {regconfig,text}
 to_tsvector | s           | {text}
(6 rows)

注意重载

date_trunc 和 to_tsvector 各出现两次，provolatile 分别是 i 和 s。to_tsvector('english', body) 因为配置是显式传入的字面量所以是 IMMUTABLE；而 to_tsvector(body) 用的是会话级的 default_text_search_config，只能算 STABLE。

((a + b)) 双括号语法 ​

语法上有一处容易混淆的地方：表达式索引的表达式「通常」需要额外加一层括号。这是为了避免 PostgreSQL 把括号内容误解析成「列名 + 操作符类 + 排序方向」这种列选项。

函数调用形式是唯一可以省略外层括号的情况：

sql
CREATE TABLE t_math (a integer, b integer);

-- 可以：函数调用形式，括号天然存在
CREATE INDEX idx_t_lower ON t_math (lower(a::text));

-- 错误！PostgreSQL 会试图把 `a + b` 解析成列选项，直接报语法错误
CREATE INDEX idx_bad_3 ON t_math (a + b);
text
ERROR:  syntax error at or near "+"
LINE 1: CREATE INDEX idx_bad_3 ON t_math (a + b);
                                            ^
sql
-- 正确：运算符表达式必须再包一层括号
CREATE INDEX idx_t_sum ON t_math ((a + b));

为什么需要两层？看最外层的括号：( (a + b) ) ——外层是「索引列列表」的括号，内层才是「这是一个表达式」的标记。规则可以总结为：

表达式形态写法例子
纯函数调用一层括号即可lower(email)、md5(body)
含运算符必须双层((a + b))、((amount * 1.13))
含 :: 转换必须双层(((data ->> 'n')::int))
纯列引用一层（这是普通索引）(email)

写对了以后，pg_get_indexdef() 反解析出来的定义会是 ... USING btree (((a + b)))——最外层那对括号属于列列表，里面才是表达式。

查询必须用「同一个表达式」才能命中（附 EXPLAIN 验证法） ​

这是表达式索引最需要牢记的一条纪律。优化器判断能否使用索引的方式是比较表达式树（pg_node_tree），而不是「理解语义」。

能命中的情况：

sql
-- 与索引定义逐字一致的表达式
SELECT id FROM users WHERE lower(email) = 'alice@example.com';

-- 大小写不同但语义完全一致（解析后是同一个表达式树），也可以命中
SELECT id FROM users WHERE LOWER(email) = 'alice@example.com';

-- 常量侧可以自由计算，优化器会先做常量折叠
SELECT id FROM users WHERE lower(email) = lower('Alice@Example.com');

-- ORDER BY 也能利用表达式索引免排序
SELECT id FROM users ORDER BY lower(email);

不能命中的情况：

sql
-- 错误！表达式树不同：多了个 ::text 转换（虽然结果一样）
SELECT id FROM users WHERE lower(email::text) = 'alice@example.com';

-- 错误！索引键是 lower(email)，这里用的是 ILIKE
SELECT id FROM users WHERE email ILIKE 'alice@example.com';

-- 错误！B-Tree 的 lower() 键无法回答前缀匹配
SELECT id FROM users WHERE lower(email) LIKE 'alice%';

不要靠猜，用 EXPLAIN 验证

表达式索引失效不报错，只是默默变成 Seq Scan。任何依赖表达式索引的查询，上线前都应当用 EXPLAIN 看一眼 Index Cond 是否出现。

验证方法统一为：看计划里有没有 Index Scan / Bitmap Index Scan，并在其下出现 Index Cond（而不是 Filter）。

sql
-- 未命中：条件落在 Filter 而不是 Index Cond
EXPLAIN (COSTS OFF)
SELECT id FROM users WHERE email = 'bob@example.com';
text
             QUERY PLAN
------------------------------------
 Seq Scan on users
   Filter: (email = 'bob@example.com'::text)
(2 rows)

小表上优化器会觉得顺序扫描更便宜，这属于正常的代价判断，不代表索引写错了。要验证索引本身有效，可以临时关掉顺序扫描：

sql
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id FROM users WHERE lower(email) = 'bob@example.com';
RESET enable_seqscan;
text
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_users_lower_email on users
   Index Cond: (lower(email) = 'bob@example.com'::text)
(2 rows)

Index Cond 出现即证明索引在语法与语义上是匹配的。生产上排查时这是最省事的一个判定手段。

用途一：大小写不敏感的唯一性 ​

这是表达式索引最经典的生产用途。业务要求「邮箱不区分大小写唯一」，但用户输入的大小写是随机的。三种方案对比：

方案做法问题
查询时转小写应用层统一 lower() 后存储需要应用层纪律，历史数据难治理
citext 扩展列类型改成 citext需要装扩展，且 citext 有自己的比较开销
唯一表达式索引存原值，索引 lower(email)数据结构不变，数据库层强约束
sql
DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email    text        NOT NULL,
  nickname text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uq_accounts_lower_email ON accounts (lower(email));

唯一性立刻生效，且大小写不敏感：

sql
INSERT INTO accounts (email, nickname) VALUES ('Alice@Example.com', 'alice');
INSERT INTO accounts (email, nickname) VALUES ('alice@example.com', 'alice2');
text
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "uq_accounts_lower_email"
DETAIL:  Key (lower(email))=(alice@example.com) already exists.

唯一表达式索引还能当 ON CONFLICT 的仲裁目标吗

可以，但必须把表达式写进 ON CONFLICT 的推断子句里，且外层括号别丢：

sql
INSERT INTO accounts (email, nickname) VALUES ('Bob@Example.com', 'bob')
ON CONFLICT (lower(email)) DO NOTHING;

用途二：从 JSONB 里抽取标量再索引 ​

jsonb 里的字段是「无模式」的，但业务查询往往只关心其中一两个键。直接给整个 jsonb 建 GIN 只能回答包含关系，回答不了范围比较与排序；表达式索引可以把某个键「拉」成标量，从而获得 B-Tree 的全部能力。

sql
DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload    jsonb       NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (payload)
SELECT jsonb_build_object(
         'user_id', (g % 500) + 1,
         'level',   (ARRAY['info','warn','error'])[1 + (g % 3)],
         'cost_ms', (g * 7) % 4000
       )
FROM generate_series(1, 20000) AS g;

-- 把 user_id 抽成 bigint 并建索引
CREATE INDEX idx_events_user_id ON events (((payload ->> 'user_id')::bigint));

-- 把 cost_ms 抽成 int 并建索引，用于范围查询
CREATE INDEX idx_events_cost ON events (((payload ->> 'cost_ms')::int));

ANALYZE events;

注意索引定义里是双括号：外层是列列表，内层是表达式；::bigint 的转换让索引键从 text 变成了 8 字节整数，索引体积更小、比较更快。

查询必须原样照抄这个表达式：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM events WHERE (payload ->> 'user_id')::bigint = 42;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_events_user_id on events (actual rows=40 loops=1)
   Index Cond: (((payload ->> 'user_id'::text))::bigint = 42)
   Buffers: shared hit=35
(3 rows)

范围查询同样走索引，这是 GIN 做不到的：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM events WHERE (payload ->> 'cost_ms')::int BETWEEN 1000 AND 1500;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Scan using idx_events_cost on events (actual rows=2502 loops=1)
         Index Cond: ((((payload ->> 'cost_ms'::text))::int) >= 1000)
         Index Cond: ((((payload ->> 'cost_ms'::text))::int) <= 1500)
(4 rows)

类型必须一致

WHERE payload ->> 'user_id' = '42'（字符串比较）用不上 ::bigint 的索引；反过来 WHERE (payload ->> 'user_id')::int = 42（换成 int）也会因为表达式树不同而失效。抽取值的目标类型一旦选定，索引与查询必须完全对齐。

用途三：日期取月 / 时间截断 ​

报表类业务大量存在「按月统计」的查询。直接在 created_at 上建索引对 date_trunc 无能为力，而给月份建表达式索引可以让「按月分组」直接走索引有序扫描。

sql
INSERT INTO orders (created_at, amount)
SELECT
  '2026-01-01'::timestamptz + (g || ' hours')::interval,
  (random() * 500)::numeric(12,2)
FROM generate_series(1, 50000) AS g;

ANALYZE orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT date_trunc('month', created_at AT TIME ZONE 'UTC') AS mon, count(*)
FROM orders
GROUP BY 1
ORDER BY 1;
text
                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=2 loops=1)
   Group Key: (date_trunc('month'::text, (created_at AT TIME ZONE 'UTC'::text)))
   ->  Index Only Scan using idx_orders_month on orders (actual rows=50000 loops=1)
         Heap Fetches: 0
(4 rows)

计划里没有 Sort，也没有 Seq Scan——GroupAggregate 直接吃索引的有序输出。同一条查询如果不建这个索引，计划里会多出一次 5 万行的显式 Sort。行数越大，这个排序的代价越突出。

WHERE 上按「日」过滤同理，只要索引与查询用同一个表达式：建立 ((created_at AT TIME ZONE 'UTC')::date) 后，WHERE (created_at AT TIME ZONE 'UTC')::date = DATE '2026-01-05' 会以 Index Cond 形式直接定位到当天。

表达式索引与约束 ​

表达式索引与唯一性是天然搭配——唯一约束背后就是一个唯一索引，唯一表达式索引则提供「按表达式唯一」。但有一处必须记牢：表达式索引可以「当唯一索引用」，却不能提升为具名约束。

与约束相关的重要限制：

操作表达式索引是否可用
ALTER TABLE ... ADD CONSTRAINT ... USING INDEX（提升为主键/唯一约束）不可用，报错
ALTER TABLE ... ADD CONSTRAINT ... USING INDEX（部分索引）不可用
ON CONFLICT (表达式)可用（需与索引表达式一致）
CREATE UNIQUE INDEX ... WHERE ...（部分唯一）可用，二者可组合
外键引用无法引用表达式索引
sql
-- 错误！表达式索引不能被提升为约束
CREATE UNIQUE INDEX uq_tmp ON accounts (lower(email));
ALTER TABLE accounts ADD CONSTRAINT uq_accounts_lower UNIQUE USING INDEX uq_tmp;
text
ERROR:  index "uq_tmp" contains expressions
DETAIL:  Cannot create a primary key or unique constraint using such an index.

因此「唯一表达式索引」只能是索引，不能变成具名的 UNIQUE 约束。这对使用 information_schema.table_constraints 做 Schema 比对的工具有一定影响——比对工具看不到它。

用系统表把表达式索引与普通索引区分开，靠的是 pg_index.indexprs 与 indkey：

sql
SELECT
  c.relname                                   AS index_name,
  i.indisunique                               AS is_unique,
  i.indexprs IS NOT NULL                      AS has_expression,
  pg_get_indexdef(i.indexrelid)               AS index_def
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'accounts'::regclass;
text
        index_name         | is_unique | has_expression |                 index_def
---------------------------+-----------+----------------+--------------------------------------------
 accounts_pkey             | t         | f              | CREATE UNIQUE INDEX accounts_pkey ON public.accounts USING btree (id)
 uq_accounts_lower_email   | t         | t              | CREATE UNIQUE INDEX uq_accounts_lower_email ON public.accounts USING btree (lower(email))
(2 rows)

indexprs IS NOT NULL 就是「这个索引里含有表达式」。

表达式索引的维护与磁盘代价 ​

表达式索引不是免费的，代价体现在三个地方。

第一，写入时要多算一次表达式。 每 INSERT / UPDATE 一行，PostgreSQL 都要对每个表达式索引求值一次。注意一个容易误解的点：即使 UPDATE 没有改动表达式依赖的列，表达式也会被重新求值——PostgreSQL 不做这种「依赖分析」的优化。

第二，索引体积取决于表达式结果的宽度。 同样一份数据，索引键是 text 还是 bigint，体积差别明显：

sql
CREATE TABLE ev2 (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb  NOT NULL
);

INSERT INTO ev2 (payload)
SELECT jsonb_build_object('uid', (g % 100000)::text, 'note', repeat('x', 200))
FROM generate_series(1, 100000) AS g;

-- 方案 A：索引键是 text
CREATE INDEX idx_ev2_uid_text ON ev2 ((payload ->> 'uid'));

-- 方案 B：索引键是 bigint
CREATE INDEX idx_ev2_uid_int  ON ev2 (((payload ->> 'uid')::bigint));

SELECT indexrelid::regclass                         AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'ev2' AND indexrelid <> 'ev2_pkey'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
text
     index_name     | index_size
--------------------+------------
 idx_ev2_uid_text   | 2528 kB
 idx_ev2_uid_int    | 1560 kB
(2 rows)

第三，表达式索引不能「顺手」被别的索引复用。 普通的多列索引 (a, b) 可以回答所有以 a 开头的查询；而每个表达式索引只服务于它那一个表达式。

成本项说明
写入 CPU每次写入重算表达式（不可跳过）
写入 I/O索引键的 B-Tree 维护
磁盘索引键宽度 × 行数 × B-Tree 开销
维护需 ANALYZE 才能让优化器准确估行
可复用性低，一个表达式一个索引

表达式索引同样会膨胀，也同样是 REINDEX 的对象；检查使用率与体积是日常运维动作：

sql
SELECT indexrelid::regclass                        AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'ev2';
text
     index_name     |   size   | idx_scan
--------------------+----------+----------
 idx_ev2_uid_int    | 1560 kB  |        0
 idx_ev2_uid_text   | 2528 kB  |        0
 ev2_pkey           | 3480 kB  |        0
(3 rows)

与生成列（Generated Column）的取舍 ​

「让查询直接用上计算结果」还有另一条路：生成列。PostgreSQL 12 起支持 GENERATED ALWAYS AS (...) STORED，把表达式的值物化成表的一列。

sql
-- 生成列方案
ALTER TABLE users ADD COLUMN email_lower text
  GENERATED ALWAYS AS (lower(email)) STORED;

-- 然后建一个普通索引
CREATE INDEX idx_users_email_lower ON users (email_lower);

-- 查询直接引用列名，不再需要照抄表达式，计划里出现 Index Cond: (email_lower = ...)
EXPLAIN (COSTS OFF)
SELECT id FROM users WHERE email_lower = 'alice@example.com';

两者的取舍如下：

维度表达式索引生成列（STORED）
是否占用堆表空间不占（只在索引里存一份）占用（堆表 + 索引各一份）
SELECT 能否直接读到不能（除非写成表达式）能，像普通列一样
查询写法要求必须逐字照抄表达式直接写列名，不易出错
能否加 NOT NULL / CHECK不能能
能否被多个索引共用不能（一索引一表达式）能（可建多个索引）
能否作为分区键 / 外键引用不能能
写入成本只算一次、只写索引算一次、堆表与索引各写一次
表达式限制必须 IMMUTABLE必须 IMMUTABLE，且不能引用其他生成列

一句话决策：

  • 只为了让某个查询走索引 → 用表达式索引，省空间、改动最小。
  • 计算结果本身是业务数据的一部分（要 SELECT 出来、要参与约束、要做分区键） → 用生成列。

生成列不能改表达式

生成列的表达式一旦定义就不能通过 ALTER 修改。要改只能「加新列 → 回填 → 删旧列 → 改名」，代价不小。表达式索引则只需 DROP INDEX + CREATE INDEX，灵活得多。

实战示例 ​

场景：一张多租户的 members 表，同时满足三类需求——邮箱大小写不敏感唯一、按租户 + 邮箱前缀模糊搜索、按注册月份统计。全部用表达式索引实现，并用 EXPLAIN 逐项验证。

第 1 步：建表并灌入 10 万行

sql
DROP TABLE IF EXISTS members CASCADE;

CREATE TABLE members (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id  integer     NOT NULL,
  email      text        NOT NULL,
  profile    jsonb       NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT pk_members PRIMARY KEY (id)
);

INSERT INTO members (tenant_id, email, profile, created_at)
SELECT
  (g % 50) + 1,
  'user' || g || '@' || (ARRAY['example.com','demo.org','test.cn'])[1 + (g % 3)],
  jsonb_build_object('level', (g % 10) + 1, 'src', (ARRAY['web','app','api'])[1 + (g % 3)]),
  '2025-06-01'::timestamptz + (g || ' minutes')::interval
FROM generate_series(1, 100000) AS g;

ANALYZE members;

第 2 步：建立三个表达式索引

sql
-- 需求 1：邮箱大小写不敏感唯一（单列表达式索引即全局唯一）
CREATE UNIQUE INDEX uq_members_lower_email ON members (lower(email));

-- 若租户之间允许邮箱重复，改成租户内唯一（多列中混用普通列与表达式）
DROP INDEX uq_members_lower_email;
CREATE UNIQUE INDEX uq_members_tenant_email ON members (tenant_id, lower(email));

-- 需求 2：配合 text_pattern_ops，让 lower(email) 支持前缀匹配
CREATE INDEX idx_members_lower_email_prefix
  ON members (lower(email) text_pattern_ops);

-- 需求 3：按 UTC 注册月份统计
CREATE INDEX idx_members_month
  ON members (date_trunc('month', created_at AT TIME ZONE 'UTC'));

第 3 步：验证唯一性与 ON CONFLICT 目标

sql
INSERT INTO members (tenant_id, email) VALUES (1, 'NewUser@Example.com');
INSERT INTO members (tenant_id, email) VALUES (1, 'newuser@example.com');

-- 唯一表达式索引也可以作为 ON CONFLICT 的推断目标
INSERT INTO members (tenant_id, email) VALUES (1, 'newuser@example.com')
ON CONFLICT (tenant_id, lower(email)) DO NOTHING;
text
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "uq_members_tenant_email"
DETAIL:  Key (tenant_id, lower(email))=(1, newuser@example.com) already exists.
INSERT 0 0

第 4 步：验证前缀模糊搜索走了表达式索引

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, email FROM members WHERE lower(email) LIKE 'user12345%';
text
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using idx_members_lower_email_prefix on members (actual rows=111 loops=1)
   Index Cond: ((lower(email) ~>=~ 'user12345'::text) AND (lower(email) ~<~ 'user12346'::text))
   Filter: (lower(email) ~~ 'user12345%'::text)
   Buffers: shared hit=117
(4 rows)

text_pattern_ops 让 LIKE '前缀%' 被翻译成了一次范围扫描（~>=~ / ~<~），而不是全表比对。这是表达式索引 + 操作符类组合起来才有的能力。

第 5 步：验证按月统计免排序

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT date_trunc('month', created_at AT TIME ZONE 'UTC') AS mon, count(*)
FROM members
GROUP BY 1
ORDER BY 1;
text
                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=2 loops=1)
   Group Key: (date_trunc('month'::text, (created_at AT TIME ZONE 'UTC'::text)))
   ->  Index Only Scan using idx_members_month on members (actual rows=100000 loops=1)
         Heap Fetches: 0
(4 rows)

Index Only Scan + GroupAggregate + 无 Sort，这是报表查询最理想的一种形态。

第 6 步：对比体积，评估代价

sql
SELECT
  indexrelid::regclass                          AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid))  AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'members'
ORDER BY pg_relation_size(indexrelid) DESC;
text
          index_name           | index_size
-------------------------------+------------
 uq_members_tenant_email       | 4880 kB
 idx_members_lower_email_prefix| 3872 kB
 idx_members_month             | 2608 kB
 members_pkey                  | 2216 kB
(4 rows)

三个表达式索引合计约 11 MB，而表本身（含 jsonb）大约是它的数倍。这就是表达式索引的实际价格——在评估「要不要建」时，把这张表列出来比凭感觉判断靠谱得多。

第 7 步：清理

sql
DROP TABLE members, ev2, events, orders, accounts, users, t_math;

注意事项 ​

  • 表达式必须是 IMMUTABLE，否则直接报错。 now()、CURRENT_DATE、random()、gen_random_uuid()、to_tsvector(text)、timestamptz 版 date_trunc 都不能用。

    sql
    -- 错误！functions in index expression must be marked IMMUTABLE
CREATE INDEX idx_bad ON orders (date_trunc('month', created_at));

  • 运算符表达式必须写双括号，只写一层是语法错误。

    sql
    -- 错误！syntax error at or near "+"
CREATE INDEX idx_bad ON t (a + b);
-- 正确
CREATE INDEX idx_ok ON t ((a + b));

  • 查询表达式与索引表达式不等价时不会报错，只是静默退化为顺序扫描。 这是最难排查的一类「索引失效」。也不要在查询里对索引键再套一层函数。要改就改索引定义，两边一起改。

    sql
    -- 索引建在 lower(email) 上，这两条都用不上
SELECT id FROM users WHERE lower(email::text) = 'a@example.com';
SELECT id FROM users WHERE lower(lower(email)) = 'a@example.com';

  • 表达式索引不能被提升为主键/唯一约束，也不能被外键引用。

    sql
    -- 错误！index "uq_tmp" contains expressions
ALTER TABLE accounts ADD CONSTRAINT uq_x UNIQUE USING INDEX uq_tmp;

  • UPDATE 会重算所有表达式索引，即使依赖列没变。 表达式越复杂（尤其含 jsonb 解析）写入代价越高，写密集表要慎重。

  • 别把 GIN 能干的活儿交给表达式索引。 WHERE payload @> '{"a":1}' 应该用 GIN；表达式索引解决的是「抽取标量后做等值/范围/排序」，写成 CREATE INDEX ... ((payload @> '{"a":1}')) 没有意义。

  • 表达式里不要写 || 拼接大文本。 索引键会变得极宽，索引体积可能超过表本身；ORDER BY 想免排序时，排序表达式也必须与索引表达式一致。不推荐 ((lower(email) || '|' || lower(name) || '|' || lower(phone))) 这类超宽索引键。

  • 建完表达式索引别忘了 ANALYZE，否则优化器缺少表达式列的统计信息，可能不敢用索引。大表建表达式索引必须用 CONCURRENTLY；它失败会留下 indisvalid = false 的索引，需要删除重建。

    sql
    CREATE INDEX CONCURRENTLY idx_big ON big_table (lower(email));
SELECT indexrelid::regclass, indisvalid FROM pg_index
WHERE indrelid = 'big_table'::regclass AND NOT indisvalid;

最佳实践 ​

  • 从慢查询的 WHERE / ORDER BY 里找表达式，而不是凭想象建索引。 先用 pg_stat_statements 定位高频慢查询，再决定表达式形态。

    sql
    SELECT calls, mean_exec_time, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

  • 优先把表达式的结果转成最窄的合适类型。 (data ->> 'n')::bigint 比 data ->> 'n' 的索引小得多，也让范围比较更快。

    sql
    CREATE INDEX idx_events_uid ON events (((payload ->> 'user_id')::bigint));

  • 表达式索引与查询表达式要「同源」维护。 把表达式写成应用层共享的常量 / ORM 片段，避免一边改了另一边没改。

  • timestamptz 上做时间聚合索引，一律显式固定时区。

    sql
    CREATE INDEX idx_orders_month ON orders (date_trunc('month', created_at AT TIME ZONE 'UTC'));

  • 大小写不敏感唯一优先用唯一表达式索引，而不是 citext；前缀模糊搜索用「表达式 + text_pattern_ops」组合，让 LIKE 'x%' 变成范围扫描。

    sql
    CREATE UNIQUE INDEX uq_accounts_lower_email ON accounts (lower(email));
CREATE INDEX idx_users_prefix ON users (lower(email) text_pattern_ops);

  • 给表达式索引起能自解释的名字，把表达式意图写进名字（如 idx_orders_month、uq_accounts_lower_email），因为 \d 里的索引名是运维第一眼看到的东西。表达式索引尽量配 INCLUDE 做覆盖，避免为了取几个小列而回表。

    sql
    CREATE INDEX idx_members_lower_email_inc
  ON members (lower(email)) INCLUDE (id, tenant_id);

  • 定期复核表达式索引的使用率与体积，idx_scan = 0 且体积大的要提上清理日程。

    sql
    SELECT indexrelid::regclass AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'members'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

  • 建索引与改索引都走变更流程：先 CONCURRENTLY 建好、ANALYZE、用 EXPLAIN 验证命中，再观察一段时间使用率，最后才考虑清理旧索引。

下一节

继续学习：部分索引（Partial Index）

参考链接 ​