PostgreSQL 可能对 id < 11 使用索引，对 id > 11 使用顺序扫描，因为第一个条件匹配的行非常少，而第二个条件匹配几乎整张表。两种运算符都支持 B 树索引；规划器会比较它们的估算成本。

本指南帮助在 Mac 上工作的开发者用合成文章行复现该选择，读取执行计划，并检查差异是否需要关注。它遵循当前 PostgreSQL 18 文档和当前 node-postgres 文档。在对比计划细节前请检查你的服务器版本；示例不承诺在不同版本或机器上产生完全相同的计划。 PostgreSQL: Using EXPLAIN

为什么相同的索引会产生不同的扫描选择？

B 树索引同时支持小于和大于比较。改变比较方向不会让索引变得不可用。变化的是值的范围——因而也是规划器预期要检索的行数。 PostgreSQL: Index Types

考虑一个包含恰好 100,000 行文章的示例表，ID 为从 1 到 100,000 的连续整数：

谓词 匹配行数 占表比例
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

这些计数源于合成输入，而非对现有数据库的测量。两个范围都不包含 ID 11。索引可以高效定位第一个范围内的十个匹配 ID。对于第二个范围，它反正会识别出几乎每一行。

普通的 PostgreSQL 索引扫描从索引中检索条目，并从表的主存储（称为堆）中获取对应的行。那些堆行可能分散在许多页面上。当几乎需要每个页面时，直接读取表可能比遍历索引并获取它引用的行成本更低。 PostgreSQL: Index-Only Scans and Covering Indexes

因此数字 11 在规划器中没有特殊含义。换了数据，大于条件可能成为窄范围。PostgreSQL 利用表统计信息估算匹配行的比例，称为选择性。边界值的数值大小本身并不决定该比例。 PostgreSQL: Statistics Used by the Planner

如何在 Mac 上复现该对比？

你需要一个现有的 PostgreSQL 连接，并在 Mac 的 Terminal 中可用 psql 命令。安装不在本练习范围内。用 psql 打开你常用的数据库连接；它接受连接设置并提供交互式 SQL 提示符。在一个新会话中运行以下块。 PostgreSQL: psql

设置会创建一个仅包含合成文章文本的临时表。临时表在会话结束时消失。普通的整数主键会自动创建唯一 B 树索引，因此在 id 上再建一个索引是不必要的。 PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

PostgreSQL 的整数 generate_series 在默认步长 1 下包含两端点。因此计数查询应返回总计 100,000 行，11 以下 10 行，11 以上 99,989 行。 PostgreSQL: Set Returning Functions

加载行后运行 ANALYZE，以便规划器获得已填充表的统计信息。该命令收集统计信息；其用途不同于 EXPLAIN ANALYZE 中的执行测量选项。 PostgreSQL: ANALYZE

接下来，对比两个全行查询：

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

这些命令执行各查询并报告其计划和实际行数，而不返回所有选中的文章行。BUFFERS 增加块活动信息；TIMING OFF 禁用单个节点的计时。这适合研究执行过程，但不测量将所有匹配文章数据发送到桌面应用所需的时间。 PostgreSQL: EXPLAIN

在普通设置下，预期窄范围采用基于索引的计划，宽范围采用顺序扫描。视这些为预期：PostgreSQL 可根据其估算选择位图计划或其他适用计划。本示例的 SQL 已经过审查，但准备期间没有可用的 PostgreSQL 服务器执行它；行数算术已单独核对。

计划的哪些部分解释了决策？

从扫描节点、其估算的 rows 和实际的 rows 入手。对于第一个查询，寻找带有 Index Cond 的 Index Scan，或 Bitmap Heap Scan 下的 Bitmap Index Scan。对于第二个，寻找带有 Filter 的 Seq Scan。位图计划仍使用索引；它在获取堆页面前组织匹配行的位置。 PostgreSQL: Using EXPLAIN

然后检查以下观察：

本示例中的观察 要调查的内容
估算值大致匹配实际 10 行和 99,989 行 不同的匹配行数解释了不同的选择。
估算行数与实际行数相差很大 在更改规划器设置前先刷新统计信息。
两个谓词在小得多的样本上都使用顺序扫描 表大小可能使直接扫描更廉价。
出现位图计划 对比其行估算和堆工作，作为另一种基于索引的替代方案。

规划器成本是以任意单位表示的估算，而非毫秒。将它们作为 PostgreSQL 模型的一部分进行比较，而非把成本 100 解释为持续时间。因为示例使用临时表，缓冲区活动出现在本地块下；常规表和索引缓冲区出现在共享块下。 PostgreSQL: EXPLAIN

如果估算看起来令人惊讶，在同一会话中继续运行此诊断查询：

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

直方图描述用于范围估算的值分布。相关性描述列的逻辑顺序与物理行顺序的吻合程度；接近任一极端的值会让索引访问堆看起来开销更小。这两个字段都不确立 PostgreSQL 必须切换扫描的通用百分比。 PostgreSQL: pg_stats

如何通过 node-postgres 对比查询？

node-postgres 将查询文本和参数值发送给 PostgreSQL，由后者进行规划。其查询 API 接受 SQL 后跟一个值数组，因此两个对比都可以使用相同的边界值 11。 node-postgres: Queries

对于独立的 Node.js 复现，使用可用的 Node.js/npm 安装和你现有的 PostgreSQL 连接设置。在一个新项目目录中运行 npm init -y，然后 npm install pg。将下面的完整程序保存为 compare-scans.mjs 并运行 node compare-scans.mjs。客户端使用 PostgreSQL 环境设置，如 PGHOST、PGPORT 和 PGDATABASE；使用你现有本地数据库的设置。 node-postgres: Connecting

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

该程序创建自己的样本表，并在单个连接上完成所有操作。它打印两个计划并在之后关闭连接。其 JavaScript 语法已用 Node.js 检查；准备期间未执行数据库操作。如果连接设置失败，请先解决该问题，再解读扫描行为。

命名预备语句能改变对比吗？

在 node-postgres 查询配置中添加 name 会在每个连接上创建一个命名预备语句。这引入了一个进一步的区别：PostgreSQL 可以基于参数值使用自定义计划，或跨执行共享通用计划。 node-postgres: Queries, PostgreSQL: PREPARE

在 PostgreSQL 18 的默认自动策略下，前五次执行使用自定义计划。随后 PostgreSQL 将通用计划的估算成本与平均自定义计划成本比较；它不会在第六次执行时自动切换到通用计划。 PostgreSQL: PREPARE

如果应用的命名语句与字面量 SQL 测试表现不同，在同一连接上用 EXPLAIN EXECUTE 检查该语句。通用计划显示参数符号，而自定义计划替换为提供的值。因为两个比较运算符的 SQL 文本不同，请将它们放在单独的命名语句中。 PostgreSQL: PREPARE

何时应更改查询或规划器设置？

首先保留查询的实际任务。将 SELECT * 替换为 SELECT id 会改变 PostgreSQL 必须检索的数据。当所有必需列都可从合适的索引获得时，索引扫描成为可能。避免堆获取还取决于可见性映射，因此索引扫描计划不保证零堆访问。 PostgreSQL: Index-Only Scans and Covering Indexes

如果任务需要每个匹配的文章，在对比计划时保留该要求。如果只需要一个小的有序子集，改为测试实际的 ORDER BY 和 LIMIT 查询：排序和早期终止可以改变首选计划。 PostgreSQL: Using EXPLAIN

检查统计信息后，你可以在现有的 SQL 会话中对比替代计划：

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

该设置不鼓励顺序扫描；但不能完全禁止它们。将结果作为诊断对比使用。PostgreSQL 还通过 seq_page_cost 和 random_page_cost 对顺序和非顺序页面访问建模。改变它们的相对值可以改变计划选择，但文档建议在整个查询负载上评估成本设置，而非仅凭几个实验。 PostgreSQL: Query Planning

对于这个合成表，决定性的对比是十个匹配行对 99,989 个。检查确切查询，刷新统计信息，对比估算与实际行数，并在相关时检查预备计划行为。这些步骤确立顺序扫描是否符合请求的工作，并为任何进一步更改提供证据。

来源