SQL 为热点查询建立部分索引

功能说明

示例假定 orders、subscriptions 与 users 三张表的字段结构，并创建临时副本与少量样例数据。第一个部分索引只包含 status 为 pending 的订单，并按创建时间倒序排列，适合“最近一天待处理订单”这类热点查询。第二个部分唯一索引保证每个用户最多一条未取消订阅。第三个表达式索引对 lower(email) 建唯一约束，实现大小写不敏感的邮箱唯一性。

关键代码

部分索引的 WHERE 条件必须与查询谓词匹配，规划器才可能使用它；查询中保留 status = ‘pending’ 是关键。索引列顺序 (created_at DESC) 同时服务过滤与排序，避免额外排序步骤。唯一部分索引把已取消的历史记录排除在外，因此同一用户可以保留多条取消记录。表达式索引要求查询使用同样的 lower(email) 形式，直接比较原始 email 无法命中。

使用注意

临时表数据量很小，EXPLAIN 很可能选择顺序扫描，这不代表索引无效；应在接近生产的数据量和统计信息下评估。参数化查询中的 status 若来自绑定变量，规划器在通用计划下可能无法证明谓词匹配，需要观察实际执行计划。部分索引会增加写入维护成本，并让批量导入变慢。唯一约束应作为最后防线，应用层仍需给出友好错误。

验证方式

执行脚本后，检查两个 EXPLAIN 输出：带 status 条件的查询在数据量足够时应出现 idx_orders_pending_created_at，不带该条件的查询则不应使用它。插入第二条未取消的同用户订阅应触发唯一冲突，而插入已取消记录应成功。插入仅大小写不同的邮箱应被 uq_users_email_lower 拒绝。

代码示例

-- Schema assumptions (PostgreSQL 15+):
--   orders(id bigint PRIMARY KEY, customer_id bigint NOT NULL,
--          status text NOT NULL CHECK (status IN ('pending','paid','cancelled')),
--          created_at timestamptz NOT NULL DEFAULT now(), total_cents integer NOT NULL)
--   subscriptions(id bigint PRIMARY KEY, user_id bigint NOT NULL, plan text NOT NULL,
--                 canceled_at timestamptz)
--   users(id bigint PRIMARY KEY, email text NOT NULL)
-- TEMP copies of those tables keep this example self-contained.

CREATE TEMP TABLE users (
    id    bigint PRIMARY KEY,
    email text NOT NULL
);

CREATE TEMP TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status      text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    total_cents integer NOT NULL
);

CREATE TEMP TABLE subscriptions (
    id          bigint PRIMARY KEY,
    user_id     bigint NOT NULL,
    plan        text NOT NULL,
    canceled_at timestamptz
);

INSERT INTO users (id, email) VALUES
    (1, '[email protected]'),
    (2, '[email protected]');

INSERT INTO orders (id, customer_id, status, created_at, total_cents) VALUES
    (1, 1, 'pending', now() - interval '2 hours', 4900),
    (2, 1, 'paid', now() - interval '3 days', 2500),
    (3, 2, 'pending', now() - interval '30 days', 8900);

INSERT INTO subscriptions (id, user_id, plan, canceled_at) VALUES
    (1, 1, 'pro', NULL),
    (2, 1, 'pro', now() - interval '90 days'),
    (3, 2, 'team', NULL);

-- Partial index: only pending rows are stored, so it stays small even when
-- the orders table is dominated by paid and cancelled history.
CREATE INDEX idx_orders_pending_created_at
    ON orders (created_at DESC)
    WHERE status = 'pending';

ANALYZE orders;

-- The predicate matches the index condition exactly, so this is indexable.
EXPLAIN (COSTS OFF)
SELECT id, customer_id, total_cents
FROM orders
WHERE status = 'pending'
  AND created_at > now() - interval '1 day'
ORDER BY created_at DESC
LIMIT 50;

-- Omitting `status = 'pending'` makes the predicate non-matching; on a
-- production-sized table this query should not use the partial index.
EXPLAIN (COSTS OFF)
SELECT id, total_cents
FROM orders
WHERE created_at > now() - interval '1 day'
ORDER BY created_at DESC;

-- At most one active subscription per user; cancelled history is exempt.
CREATE UNIQUE INDEX uq_subscriptions_active_per_user
    ON subscriptions (user_id)
    WHERE canceled_at IS NULL;

-- Case-insensitive uniqueness without changing stored email casing.
CREATE UNIQUE INDEX uq_users_email_lower
    ON users (lower(email));