查询规划

工作原理

解析器把 SQL 变成语法树，重写器应用视图、规则和安全策略。规划器随后探索各种替代方案：顺序扫描、索引扫描或位图扫描；嵌套循环、哈希连接或归并连接；以及不同的连接顺序。每个方案根据行数、页数和 CPU 因子得到估算成本，最便宜的计划被编译执行。

计划是一棵算子树。EXPLAIN 显示估算行数和成本；EXPLAIN ANALYZE 额外给出实际时间和行数。估算行数与实际行数差距很大，通常说明统计信息或建模有问题，而不是缺少索引。

阅读执行计划

先找消耗时间最多的算子，再比较它的估算行数和实际行数。一个执行数百万次的嵌套循环，往往意味着规划器预期外层只有少量行。选择性谓词上的顺序扫描，可能说明索引缺失、不可用或选择性不足。排序和哈希溢出说明内存不足。估算会向上传播，因此一次糟糕的扫描会扭曲其上所有连接。

何时运用

当查询变慢、部署后计划改变，或设计索引时使用这些知识。执行计划也适合审查 ORM 生成的 SQL，它常包含不必要的连接或重复子查询。简单的主键查找很少涉及规划问题；包含大量连接的分析查询则经常涉及。

权衡与示例

计划稳定性与自适应性相互竞争。预编译语句可能复用对倾斜参数值很差的通用计划。统计信息有维护成本，刷新之间可能过时。更多索引给规划器更多选择，却拖慢写入。

示例：订单搜索按 status = 'open' 和 created_at > now() - interval '1 day' 过滤。过时统计说 open 很少，规划器选择索引上的嵌套循环；现实中大多数订单都是 open，顺序扫描或不同连接顺序会更快。运行 ANALYZE 并查看实际行数，可以暴露这种不匹配。

代码示例

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

export class SegmentTree {
  private readonly size: number;
  private readonly tree: number[];

  constructor(values: readonly number[]) {
    if (!Array.isArray(values)) throw new TypeError("values must be an array");
    for (const value of values) {
      if (typeof value !== "number" || !Number.isFinite(value)) {
        throw new TypeError("values must contain finite numbers");
      }
    }
    this.size = values.length;
    this.tree = new Array<number>(Math.max(4, this.size * 4)).fill(0);
    if (this.size > 0) this.build(values, 1, 0, this.size - 1);
  }

  sum(left: number, right: number): number {
    if (!Number.isInteger(left) || !Number.isInteger(right)) {
      throw new TypeError("range bounds must be integers");
    }
    if (left < 0 || right >= this.size || left > right) throw new RangeError("invalid range");
    return this.query(1, 0, this.size - 1, left, right);
  }

  update(index: number, value: number): void {
    if (!Number.isInteger(index) || index < 0 || index >= this.size) {
      throw new RangeError("index out of range");
    }
    if (typeof value !== "number" || !Number.isFinite(value)) {
      throw new TypeError("value must be a finite number");
    }
    this.assign(1, 0, this.size - 1, index, value);
  }

  private build(values: readonly number[], node: number, start: number, end: number): void {
    if (start === end) {
      this.tree[node] = values[start] ?? 0;
      return;
    }
    const middle = (start + end) >> 1;
    this.build(values, node * 2, start, middle);
    this.build(values, node * 2 + 1, middle + 1, end);
    this.tree[node] = this.tree[node * 2] + this.tree[node * 2 + 1];
  }

  private query(node: number, start: number, end: number, left: number, right: number): number {
    if (left <= start && end <= right) return this.tree[node];
    const middle = (start + end) >> 1;
    let total = 0;
    if (left <= middle) total += this.query(node * 2, start, middle, left, right);
    if (right > middle) total += this.query(node * 2 + 1, middle + 1, end, left, right);
    return total;
  }

  private assign(node: number, start: number, end: number, index: number, value: number): void {
    if (start === end) {
      this.tree[node] = value;
      return;
    }
    const middle = (start + end) >> 1;
    if (index <= middle) this.assign(node * 2, start, middle, index, value);
    else this.assign(node * 2 + 1, middle + 1, end, index, value);
    this.tree[node] = this.tree[node * 2] + this.tree[node * 2 + 1];
  }
}

const tree = new SegmentTree([2, 4, 6, 8, 10]);
console.log(tree.sum(1, 3));
tree.update(2, 0);
console.log(tree.sum(0, 4));
console.log(tree.sum(2, 2));