PostgreSQL JSONB GIN 索引可以通过可提取的路径到常量相等性测试缩小 JSONPath 搜索范围。范围比较、不等式和单独的路径存在性测试本身无法提供这些搜索键。混合谓词需要进一步检查。

本指南将帮助在 Mac 的终端中工作的开发者为合成文章行的筛选选择索引，通过 node-postgres 执行查询，并检查生成的执行计划。核心提取规则记录在 PostgreSQL 的 JSONB 索引参考中。

哪些谓词形式能提供有用的 GIN 搜索键？

对于 @? 和 @@，PostgreSQL 会提取将访问器链连接到常量的相等子句。支持的访问器包括命名成员、数组通配符和数组索引。两种 JSONB GIN 运算符类都支持这些形式；jsonb_ops 还支持对象通配符和递归下降。JSON 类型。

JSONPath 表达式 能提取出有用的搜索条件吗？
$.category == "layout" 可以，两种运算符类都支持
$.tags[*] == "grid" 可以，两种运算符类都支持
$.tags[0] == "grid" 可以，两种运算符类都支持；仍需检查完整谓词
$.* == "layout" jsonb_ops 支持，jsonb_path_ops 不支持
$.**.category == "layout" jsonb_ops 支持，jsonb_path_ops 不支持

以下边界依据 PostgreSQL 18 的提取实现得出。此处的“无”表示单独使用该表达式时不会提取出有用条件，并不意味着执行计划中的扫描节点名称一定如此。PostgreSQL 18 JSONB GIN 实现。

JSONPath 表达式 提取出的条件
$.words > 1200 无
$.category != "layout" 无
exists($.category) 无
$.title like_regex \"Grid\" 无
$.words == $.target_words 无：两个操作数都不是标量常量

为什么 AND 和 OR 的行为不同？

使用 $.category == "layout" && $.words > 1200 时，相等条件可以筛选候选项；随后会重新检查完整条件。使用 $.category == "layout" || $.words > 1200 时，如果只使用相等条件，就会漏掉符合范围条件的行。因此，只要其中任一分支不受支持，该 OR 就无法提取。连接两个受支持相等条件的 OR 可以提供搜索条件。这些规则针对单个 JSONPath 表达式内部的提取；其他 SQL 条件仍可能影响整体执行计划。PostgreSQL 18 JSONB GIN 实现。

应该选择哪个运算符和运算符类？

使用 @? 检查路径是否返回任何项，通常通过筛选器实现。使用 @@ 检查布尔谓词。将单纯的布尔比较传给 @? 容易踩坑：即使布尔值为 false，它仍是一个返回项。PostgreSQL 在JSON 函数和运算符中明确区分了这些形式。

以下两个查询针对下文介绍的示例文章结构，表达的是同一任务：

SELECT id
FROM jsonpath_article_demo
WHERE doc @? '$.tags[*] ? (@ == "grid")';

SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.tags[*] == "grid"';

对于演示中的命名路径相等性查询，选择 jsonb_path_ops。如果还需要 ? 等 SQL 键存在性运算符或上述通配符访问器，则选择默认的 jsonb_ops。两者都支持 @>、@? 和 @@；支持某个运算符本身并不意味着每个谓词都具有筛选能力。JSONB 索引。

示例面向 PostgreSQL 18，这是当前检索到的官方文档所标示的版本。SQL/JSON 路径支持始于 PostgreSQL 12，因此更早的服务器版本无法使用这些运算符。PostgreSQL 12 目前已被列为不受支持；其发行说明界定了功能引入的版本，不代表推荐安装该版本。PostgreSQL 12 发行说明。

如何在 Mac 上复现该查询？

准备一个正在运行的 PostgreSQL 18 数据库、可在终端中使用的 psql 命令，以及通过常用连接设置选定的现有临时数据库。本练习会创建一张新表，不会替换已有表。如果该表名已存在，请使用另一个临时数据库，或在整个示例中统一更改演示表名。

在终端中打开 psql，运行 SHOW server_version;，然后粘贴以下完整的设置脚本。它会创建 20,000 条合成文章行：每第 100 篇文章的类别为 layout，字数则在 400 到 1,999 之间循环。

CREATE TABLE jsonpath_article_demo (
    id integer PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO jsonpath_article_demo (id, doc)
SELECT
    n,
    jsonb_build_object(
        'title', 'Article ' || n,
        'category', CASE
            WHEN n % 100 = 0 THEN 'layout'
            ELSE 'notes'
        END,
        'words', 400 + n % 1600,
        'tags', jsonb_build_array(
            CASE WHEN n % 100 = 0 THEN 'grid' ELSE 'text' END
        )
    )
FROM generate_series(1, 20000) AS sample(n);

CREATE INDEX jsonpath_article_demo_doc_gin
ON jsonpath_article_demo USING gin (doc jsonb_path_ops);

ANALYZE jsonpath_article_demo;

SELECT id, doc->>'title' AS title
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200'
ORDER BY id
LIMIT 5;

推导出的前五个 ID 是 900、1000、1100、1200 和 1300；完整条件会匹配生成的 84 行。这些是示例数据集的结果，并非性能测量值。我们用一个小型 JavaScript 计算检查了算术结果和预期 ID；研究环境无法执行 PostgreSQL。

ANALYZE 会收集规划器在选择执行计划时使用的表统计信息。数据载入后运行该命令，可以为演示提供更有用的规划基准。ANALYZE。

如何判断 GIN 是否起了作用？

完成设置后，在同一数据库中运行以下两个完整的诊断查询。第一个包含可提取的相等条件；第二个单独检验范围谓词。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.words > 1200';

对于第一个查询，查找名为 jsonpath_article_demo_doc_gin 的 Bitmap Index Scan，它通常位于 Bitmap Heap Scan 之下。结合查看 Index Cond、Recheck Cond、实际行数、缓冲区活动和执行时间。即使索引条件中显示了完整的 JSONPath，也不代表每个比较都变成了索引搜索键。PostgreSQL 的使用 EXPLAIN介绍了这些执行计划字段，以及候选项检索和后续筛选之间的区别。

顺序扫描本身并不能证明索引不适用。规划器会根据估算成本作出选择；表较小或匹配范围较广时，扫描可能更划算。反过来，仅仅看到索引节点也不能证明索引有效地缩小了范围。在相似条件下比较所执行的工作量和耗时。EXPLAIN ANALYZE 会实际执行每个查询。使用 EXPLAIN。

在本练习中，实际要判断的是：类别相等条件能否充分减少候选项，使计算字数条件的成本足够低。生成数据中的类别分布提供了一个明确的测试案例，但不能据此确定分布不同的文章集合应采用的最佳计划。

node-postgres 应该如何传入 JSONPath？

将完整的 JSONPath 作为一个 SQL 参数传入，并将其转换为 jsonpath。SQL 占位符 $1 和 JSONPath 根符号 $ 属于不同的语言。node-postgres 接收查询文本和一个值数组；服务器会将提供的路径作为参数值接收。查询。

在当前受支持的 Node.js 安装中，于工作目录运行 npm install pg。将以下完整程序保存为 article-query.mjs。同一数据库中必须已执行上面的 SQL 设置。安装命令来自node-postgres 入门指南。

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const category = 'layout';
const path = `$.category == ${JSON.stringify(category)} && $.words > 1200`;

try {
  await client.connect();

  const result = await client.query({
    text: `
      SELECT id, doc->>'title' AS title
      FROM jsonpath_article_demo
      WHERE doc @@ $1::jsonpath
      ORDER BY id
      LIMIT 5
    `,
    values: [path],
  });

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

配置好临时数据库的连接环境后，运行 node article-query.mjs。Client 会读取 PostgreSQL 连接变量，例如 PGHOST、PGPORT、PGDATABASE 和 PGUSER。程序会报告连接失败或缺少演示表的情况。连接。

这里，JSON.stringify(category) 会构造 JSONPath 字符串字面量。生成的路径仍包含针对字面量 \"layout\" 的相等条件；将该路径作为 SQL 参数传入，不会把相等条件替换成 JSONPath 变量。

如果改用带 vars 参数的 jsonb_path_exists，调用的是一个使用命名 JSONPath 变量的函数。这是文档所述的求值接口，但它不是可使用索引的 @? 运算符形式。对于这次 GIN 查找，请使用上面展示的运算符查询。JSON 函数和运算符、JSONB 索引。

如果主要条件是数值范围，应该使用什么？

如果主要任务是按字数筛选文章，请通过 B-tree 表达式索引公开这个标量值。PostgreSQL 文档说明，B-tree 支持相等和有序范围比较，而表达式索引可以为计算出的值建立索引。索引类型、表达式索引。

针对生成的表运行以下附加设置和查询：

CREATE INDEX jsonpath_article_demo_words_btree
ON jsonpath_article_demo (((doc->>'words')::integer));

ANALYZE jsonpath_article_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE (doc->>'words')::integer > 1200;

该查询会刻意使用与索引相同的表达式。前提是每篇文章都只有一个兼容整数类型的 words 值。生成的行满足这一条件；任意文本或超出范围的值可能导致创建索引或后续写入时转换失败。对于数组、混合类型或包含多个匹配值的 JSONPath 比较，这种改写不能作为通用替代方案。

可以依据这一界限辅助决策：当文档中的选择性相等条件主导查询时，从 GIN 开始；当类型始终一致的标量范围主导查询时，考虑使用 B-tree 表达式索引。先确保结果集符合预期，再根据实际执行计划判断所选索引是否值得承担维护成本。

来源