部分索引

概念定义

全量索引对每行付维护成本。当只有一小片行会被查询——待处理任务、活跃会话、单一市场——部分索引只盖住这片。更小的树意味着更浅的查找、更快的扫描和好得多的缓存驻留。

工作机制

索引只存通过谓词的行；当查询约束在逻辑上蕴含该谓词时规划器才会使用它：查 status=pending 可以用按 status=pending 建的索引。条件唯一性同理——唯一部分索引只在谓词范围内强制唯一。

使用场景

已完成行占大头但查询只碰 pending 的队列表；活跃行为热集的软删除方案；只须按状态或按租户而非全局成立的唯一性约束。

常见误区

蕴含证明是严格的：查询 status IN (pending, retrying) 用不上只为 pending 建的索引，除非补第二个覆盖 retrying 的。行进出索引集的谓词抖动会在堆更新之外叠加索引增删流量；队列排空后忘了删除部分索引，维护成本会一直付下去。

代码示例

-- 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';