表达式索引

概念定义

对 lower(email) 的谓词无法使用 email 列上的索引——引擎必须对每行计算函数才能比较。表达式索引预先算好：树按函数输出为键，询问同一函数的查询获得索引查找。

工作机制

索引逐行存表达式结果并在写入时更新。不可变性是硬要求：规划器必须信任“索引值等于将来重算的结果”，依赖区域设置或时钟的函数都违反这一点。生成列是姊妹特性，把计算值变成真正可查询、可约束的列。

使用场景

大小写不敏感的身份查找、JSONB 字段过滤、清洗后的编码、按时间戳截断日期分组等函数范围查询。表达式需要在 SELECT 中出现或服务多个查询时，改用生成列更合适。

常见误区

查询表达式必须与索引表达式在规划器规范化后逐字一致，多包一层函数就静默弃用索引。把可变函数强行标记为 IMMUTABLE 会在区域或时区变化后腐蚀正确性。超大表达式值会膨胀索引条目；大版本升级时要复查文本表达式索引背后的排序规则假设。

代码示例

-- B-tree index for equality and range queries
CREATE INDEX idx_products_category_price
    ON products (category_id, unit_price);

-- Partial index: only index active products
CREATE INDEX idx_products_active
    ON products (product_name)
    WHERE discontinued_date IS NULL;

-- Expression index for case-insensitive searches
CREATE INDEX idx_customers_email_lower
    ON customers (LOWER(email));

-- Unique index for constraint enforcement
CREATE UNIQUE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date)
    WHERE status != 'cancelled';

-- Covering index (INCLUDE) for index-only scans
CREATE INDEX idx_order_items_covering
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_products_name
    ON products (product_name);

-- BUFFERS 选项显示磁盘与缓存命中，真实成本的核心
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 检查估算偏差：预估行数与实际行数差距大时统计信息过期
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 统计信息刷新后重测
ANALYZE orders;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 验证新索引被采用：Index Scan / Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

EXPLAIN (ANALYZE)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'paid';