PostgreSQL中CREATE INDEX ON t ((lower(name)))是表达式索引，非独立“函数索引”；其命中要求查询中表达式字面完全一致（含大小写、括号），且ILIKE等无法自动利用，需改写为lower(name) = lower('Alice')。
PostgreSQL 里 CREATE INDEX ON t ((lower(name))) 是表达式索引，不是函数索引
很多人看到 lower() 就以为是“函数索引”，其实 PostgreSQL 没有独立的“函数索引”类型——所有基于函数调用的索引都属于表达式索引。关键区别在于：表达式索引要求查询条件中出现**完全一致的表达式**才能命中，大小写、括号、空格都不能差。
常见错误现象：WHERE lower(name) = 'alice' 能走索引，但 WHERE name ILIKE 'alice' 或 WHERE LOWER(name) = 'alice'（大写函数名）在某些旧版本可能不匹配（取决于规划器对函数大小写的敏感度）。
- 必须确保查询中表达式与索引定义**字面完全一致**，包括函数名大小写、嵌套层级、是否加括号
-
ILIKE不会自动利用lower()表达式索引，得改写成lower(name) = lower('Alice') - 如果字段常做模糊前缀查（如
name LIKE 'ali%'），表达式索引没用，该上text_pattern_ops或pg_trgm
部分索引 WHERE status = 'active' 的生效前提是 WHERE 条件能静态推导出谓词
部分索引只在查询条件能被规划器**确定性地等价推出**索引谓词时才启用。它不是“只要 WHERE 里写了 status = 'active' 就一定用”，而是看整个查询逻辑能否被证明满足索引约束。
使用场景：用户表里 95% 是 inactive，但活跃用户查询极频繁；订单表中只有 state IN ('paid', 'shipped') 的记录需要高频检索。
- 带参数的查询（如
WHERE status = $1）在 prepare 阶段无法推导，可能跳过部分索引，除非绑定具体值后重计划 - 复合条件如
WHERE status = 'active' AND created_at > '2024-01-01'仍可用，但若写成WHERE status IN ('active', 'pending')，哪怕只查 active，索引也不会用 - 部分索引不降低写入开销——插入时仍要检查是否满足
WHERE条件，不满足就跳过索引更新，但判断本身有微量 CPU 成本
三者共存时，PostgreSQL 选哪个索引？看执行计划，别猜
没有“优先级排序”。优化器根据统计信息估算代价，从所有可用索引（包括表达式、部分、普通 B-tree）里挑它认为最便宜的那个。同一个查询，在不同数据分布下可能选完全不同索引。
容易踩的坑：本地小数据集上 EXPLAIN 看着走了部分索引，上线后因数据倾斜或统计信息过期，实际跑的是全表扫描。
- 务必在生产数据量级 +
VACUUM ANALYZE后再看EXPLAIN (ANALYZE, BUFFERS) - 不要同时建
INDEX ON t (status)和INDEX ON t (status) WHERE status = 'active'——后者更精准，前者几乎没存在价值，还拖慢 INSERT/UPDATE - 表达式索引和部分索引可以叠加：
CREATE INDEX ON t ((lower(email))) WHERE verified = true，但维护成本和命中门槛同步升高
MySQL 和 SQLite 的“函数索引”根本不是一回事
MySQL 8.0+ 的函数索引是语法糖，底层仍是表达式索引，但允许你写 INDEX (lower(name)) 这种形式；SQLite 3.30+ 的表达式索引也类似。但二者都不支持 PostgreSQL 那种灵活的 WHERE 子句过滤（即没有部分索引能力）。
兼容性影响明显：想把 PostgreSQL 的 CREATE INDEX ON t ((lower(name))) WHERE deleted_at IS NULL 迁到 MySQL，得拆成两步——先建函数索引，再靠应用层或触发器保证 deleted_at IS NULL 的数据隔离。
- MySQL 函数索引要求表达式必须是“确定性”的（比如不能含
NOW()、RAND()），否则建索引直接报错ERROR 3719 - SQLite 表达式索引不支持
COLLATE指定，大小写处理不如 PostgreSQL 灵活 - 跨数据库迁移时，最容易被忽略的是部分索引的语义缺失——它不是性能选项，而是数据子集的物理划分方式
