位图索引

概念定义

位图索引不把值映射到行位置，而是每个值对每行占一个比特：置位表示该行取此值。过滤组合退化为位向量的按位与或，CPU 一条指令处理上千行——这正是多标志过滤几乎免费组合的原因。

工作机制

每个谓词求值为位图，交集并集先把过滤条件折叠，再一次性取回幸存行。Roaring 等压缩格式把稠密与稀疏分段，保持向量小巧。但写入会作废成段比特，引擎要么锁范围要么重建片段，更新因此昂贵。

使用场景

读多写少、且状态/分层/布尔列总是组合过滤的分析模式。PostgreSQL 提供的是临时版：由 B 树结果构建的 bitmap AND/OR 扫描节点。

常见误区

基数是预算：取值数接近行数时每个值一条向量，比全表扫描还糟。频繁更新的表是反例，会遭遇锁风暴。未压缩位图稀疏而巨大，实用形态必须是压缩编码；刚更新过的行在可见性映射稳定前可能被临时位图扫描遗漏。

代码示例

-- 按账户分区、按时间排序的累计余额
WITH ledger AS (
  SELECT
    account_id,
    occurred_at,
    amount_cents,
    SUM(amount_cents) OVER (
      PARTITION BY account_id
      ORDER BY occurred_at, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
  FROM transactions
  WHERE status = 'settled'
)
SELECT
  account_id,
  occurred_at,
  amount_cents,
  running_balance,
  LAG(running_balance, 1, 0) OVER (
    PARTITION BY account_id ORDER BY occurred_at, id
  ) AS previous_balance
FROM ledger
WHERE running_balance < 0  -- 首次透支的时刻一目了然
ORDER BY account_id, occurred_at;

-- 配套索引：窗口的 PARTITION + ORDER 键
-- CREATE INDEX idx_tx_account_time
--   ON transactions (account_id, occurred_at, id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM users
WHERE tenant_id = 42
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- A covering index such as (tenant_id, created_at DESC) lets the planner
-- use an index scan and avoid a sort when the ORDER BY matches the index.