Индексы PostgreSQL JSONB GIN могут сужать поиск по JSONPath с помощью извлекаемых проверок равенства пути константе. Сравнения диапазонов, неравенство и простые проверки существования пути сами по себе не предоставляют таких ключей поиска. Смешанные предикаты требуют более тщательного анализа.

Это руководство поможет разработчику, работающему в Терминале на Mac, выбрать индекс для фильтрации синтетических строк статей, передать запрос через node-postgres и проверить полученный план. Основное правило извлечения описано в справочнике по индексированию JSONB PostgreSQL.

Какие формы предикатов дают полезные ключи поиска GIN?

Для @? и @@ PostgreSQL извлекает условия равенства, связывающие цепочку доступа с константой. Поддерживаются доступ к именованным элементам, подстановочные знаки массивов и индексы массивов. Эти формы поддерживаются обоими классами операторов GIN для JSONB; 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. Здесь «нет» означает, что само это выражение не даёт полезного извлечённого условия, а не обещание относительно имени узла сканирования в плане. Реализация JSONB GIN в PostgreSQL 18.

Выражение 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 по-прежнему могут влиять на общий план. Реализация JSONB GIN в PostgreSQL 18.

Какой оператор и класс операторов выбрать?

Используйте @?, чтобы проверить, возвращает ли путь хотя бы один элемент, обычно с помощью фильтра. Используйте @@ для проверки логического предиката. Передавать простое логическое сравнение оператору @? — ловушка: даже значение 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. Используйте класс jsonb_ops по умолчанию, если вам также нужны SQL-операторы проверки существования ключа, такие как ?, или описанные выше подстановочные доступы. Оба класса поддерживают @>, @? и @@; сама по себе поддержка оператора не делает каждый предикат селективным. Индексирование JSONB.

Примеры рассчитаны на PostgreSQL 18 — версию, указанную в полученной официальной документации как текущая. Поддержка путей SQL/JSON появилась в PostgreSQL 12, поэтому в более ранних версиях сервера эти операторы недоступны. PostgreSQL 12 теперь числится неподдерживаемой версией; примечания к выпуску устанавливают границу появления функции, но не рекомендуемую версию для установки. Примечания к выпуску PostgreSQL 12.

Как воспроизвести запрос на Mac?

Подготовьте работающую базу данных PostgreSQL 18, команду psql, доступную в Терминале, и существующую тестовую базу, выбранную с помощью обычных настроек подключения. В этом упражнении создаётся новая таблица, существующая таблица не заменяется. Если таблица с таким именем уже есть, используйте другую тестовую базу или последовательно переименуйте демонстрационную таблицу.

Откройте psql в Терминале, выполните SHOW server_version;, а затем вставьте этот полный скрипт настройки. Он создаёт 20 000 синтетических строк статей: у каждой сотой статьи категория 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;

Первые пять вычисленных идентификаторов: 900, 1000, 1100, 1200 и 1300; полному условию соответствуют 84 созданные строки. Это примеры результатов набора данных, а не показатели производительности. Арифметика и ожидаемые идентификаторы проверены с помощью небольшого расчёта на JavaScript; в исследовательской среде выполнить запросы 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';

В первом запросе ищите Bitmap Index Scan с именем jsonpath_article_demo_doc_gin, обычно расположенный под Bitmap Heap Scan. Рассматривайте вместе Index Cond, Recheck Cond, фактическое число строк, активность буферов и время выполнения. Указание полного JSONPath в условии индекса не означает, что каждое сравнение стало ключом поиска по индексу. В разделе PostgreSQL Использование EXPLAIN объясняются поля плана и различие между получением кандидатов и последующей фильтрацией.

Последовательное сканирование само по себе не опровергает возможность использования индекса. Планировщик выбирает вариант на основе расчётной стоимости, и для небольшой таблицы или широкого совпадения сканирование может оказаться предпочтительнее. И наоборот, само наличие узла индекса ещё не доказывает, что он эффективно сузил выборку. Сравнивайте выполненную работу и затраченное время в сопоставимых условиях. EXPLAIN ANALYZE действительно выполняет каждый запрос. Использование EXPLAIN.

Для этого упражнения практический вопрос заключается в том, достаточно ли равенство категории сокращает число кандидатов, чтобы проверка условия по количеству слов стала недорогой. Распределение категорий в сгенерированных данных даёт наглядный тестовый пример, но не позволяет определить лучший план для коллекции статей с другим распределением.

Как передать JSONPath через node-postgres?

Передайте полный 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.

Если вместо этого использовать jsonb_path_exists с аргументом vars, вы вызываете функцию с именованными переменными 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, совместимое с типом integer, для каждой статьи. Созданные строки этому условию соответствуют; произвольный текст или значения вне допустимого диапазона могут привести к ошибке приведения типа при создании индекса или последующих изменениях данных. Это преобразование не является универсальной заменой сравнений JSONPath для массивов, смешанных типов или нескольких совпадающих значений.

Используйте это ограничение как ориентир при выборе: начинайте с GIN, если поиском управляют селективные равенства по документу, и рассмотрите индекс выражения B-tree, если основным критерием служит диапазон скалярного значения с устойчивым типом. Сначала сохраните нужный набор результатов, а затем по фактическому плану определите, оправдывает ли выбранный индекс затраты на его обслуживание.

Источники