Los índices GIN de JSONB de PostgreSQL pueden acotar las búsquedas JSONPath mediante pruebas de igualdad extraíbles entre una ruta y una constante. Las comparaciones de rangos, la desigualdad y las pruebas básicas de existencia de rutas no proporcionan esas claves de búsqueda por sí solas. Los predicados mixtos requieren un análisis más detallado.

Esta guía ayuda a un desarrollador que trabaja en Terminal en un Mac a elegir un índice para filtrar filas sintéticas de artículos, pasar la consulta a través de node-postgres e inspeccionar el plan resultante. La regla principal de extracción está documentada en la referencia de indexación de JSONB de PostgreSQL.

¿Qué formas de predicado proporcionan claves de búsqueda GIN útiles?

Para @? y @@, PostgreSQL extrae cláusulas de igualdad que conectan una cadena de accesores con una constante. Los accesores admitidos incluyen miembros con nombre, comodines de matriz e índices de matriz. Ambas clases de operadores GIN de JSONB admiten estas formas; jsonb_ops también admite comodines de objeto y recorridos recursivos. Tipos JSON.

Expresión JSONPath ¿Proporciona una condición de búsqueda extraída útil?
$.category == "layout" Sí, con cualquiera de las clases de operadores
$.tags[*] == "grid" Sí, con cualquiera de las clases de operadores
$.tags[0] == "grid" Sí, con cualquiera de las clases de operadores; aun así, hay que comprobar el predicado completo
$.* == "layout" Admitida por jsonb_ops, pero no por jsonb_path_ops
$.**.category == "layout" Admitida por jsonb_ops, pero no por jsonb_path_ops

Los siguientes límites se desprenden de la implementación de extracción de PostgreSQL 18. Aquí, «ninguna» significa que esa expresión por sí sola no proporciona una condición extraída útil; no garantiza el nombre del nodo de análisis que aparecerá en el plan. Implementación GIN de JSONB de PostgreSQL 18.

Expresión JSONPath Condición extraída
$.words > 1200 Ninguna
$.category != "layout" Ninguna
exists($.category) Ninguna
$.title like_regex \"Grid\" Ninguna
$.words == $.target_words Ninguna: ninguno de los operandos es una constante escalar

¿Por qué AND y OR se comportan de forma distinta?

Con $.category == "layout" && $.words > 1200, la igualdad puede seleccionar candidatos; luego se vuelve a comprobar la condición completa. Con $.category == "layout" || $.words > 1200, usar solo la igualdad pasaría por alto las filas que coinciden con el rango. Por eso falla la extracción para ese OR si una de las ramas no es compatible. Un OR que combina dos igualdades compatibles puede proporcionar condiciones de búsqueda. Estas reglas se refieren a la extracción dentro de una expresión JSONPath; otras condiciones SQL aún pueden afectar el plan general. Implementación GIN de JSONB de PostgreSQL 18.

¿Qué operador y clase de operadores deberías elegir?

Usa @? para comprobar si una ruta devuelve algún elemento, normalmente mediante un filtro. Usa @@ para comprobar un predicado booleano. Pasar una comparación booleana sin más a @? es una trampa: incluso un valor booleano falso se devuelve como elemento. PostgreSQL distingue explícitamente estas formas en Funciones y operadores JSON.

Estas dos consultas expresan la misma tarea para la estructura de artículos de ejemplo que se presenta a continuación:

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

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

Elige jsonb_path_ops para la carga de trabajo de igualdad con rutas con nombre que se muestra aquí. Elige la opción predeterminada jsonb_ops si también necesitas operadores SQL de existencia de claves, como ?, o los accesores con comodines descritos arriba. Ambas admiten @>, @? y @@; que un operador sea compatible no significa que todos los predicados permitan reducir los resultados. Indexación de JSONB.

Los ejemplos están dirigidos a PostgreSQL 18, la versión que identifica actualmente la documentación oficial consultada. La compatibilidad con rutas SQL/JSON llegó en PostgreSQL 12, por lo que estos operadores no están disponibles en servidores anteriores. PostgreSQL 12 aparece ahora como una versión sin soporte; sus notas de la versión establecen cuándo se introdujo la función, pero no recomiendan instalar esa versión. Notas de la versión de PostgreSQL 12.

¿Cómo puedes reproducir la consulta en un Mac?

Empieza con una base de datos PostgreSQL 18 en ejecución, el comando psql disponible en Terminal y una base de datos temporal existente seleccionada mediante tu configuración habitual de conexión. Este ejercicio crea una tabla nueva y no reemplaza una existente. Si ese nombre de tabla ya existe, usa otra base de datos temporal o cambia el nombre de la tabla de demostración de forma coherente.

Abre psql en Terminal, ejecuta SHOW server_version; y luego pega toda esta configuración. Crea 20.000 filas sintéticas de artículos: cada artículo número cien tiene la categoría layout, y el recuento de palabras va rotando entre 400 y 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;

Los primeros cinco ID resultantes son 900, 1000, 1100, 1200 y 1300; la condición completa coincide con 84 filas generadas. Son resultados ilustrativos del conjunto de datos, no mediciones de rendimiento. Los cálculos y los ID previstos se comprobaron con un pequeño cálculo en JavaScript; no fue posible ejecutar PostgreSQL en el entorno de investigación.

ANALYZE recopila estadísticas de la tabla que el planificador usa al elegir planes de ejecución. Ejecutarlo después de cargar los datos proporciona una base de planificación más útil para la demostración. ANALYZE.

¿Cómo puedes saber si GIN ayudó?

Después de la configuración, ejecuta estas dos consultas de diagnóstico completas en la misma base de datos. La primera incluye una igualdad extraíble; la segunda aísla el predicado de rango.

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

En la primera consulta, busca un Bitmap Index Scan que mencione jsonpath_article_demo_doc_gin, normalmente debajo de un Bitmap Heap Scan. Examina conjuntamente Index Cond, Recheck Cond, el recuento real de filas, la actividad de búferes y el tiempo de ejecución. Que una condición de índice muestre la expresión JSONPath completa no significa que todas las comparaciones se hayan convertido en claves de búsqueda del índice. Uso de EXPLAIN de PostgreSQL explica estos campos del plan y la diferencia entre recuperar candidatos y filtrarlos después.

Un análisis secuencial no demuestra por sí solo que el índice no sea elegible. El planificador elige según los costos estimados, y una tabla pequeña o una coincidencia amplia puede favorecer el análisis secuencial. A la inversa, la mera presencia de un nodo de índice no demuestra que se haya reducido el conjunto de resultados de manera útil. Compara el trabajo realizado y el tiempo transcurrido en condiciones similares. EXPLAIN ANALYZE ejecuta realmente cada consulta. Uso de EXPLAIN.

En este ejercicio, la pregunta práctica es si la igualdad de categoría reduce lo suficiente los candidatos como para que resulte barato evaluar la condición del recuento de palabras. La distribución de categorías generada ofrece un caso de prueba claro, pero no permite determinar cuál es el mejor plan para una colección de artículos con otra distribución.

¿Cómo debe node-postgres pasar la ruta JSONPath?

Pasa la ruta JSONPath completa como un único parámetro SQL y conviértela a jsonpath. El marcador SQL $1 y la raíz $ de JSONPath pertenecen a lenguajes distintos. node-postgres acepta el texto de la consulta junto con una matriz de valores; el servidor recibe la ruta proporcionada como valor del parámetro. Consultas.

Con una instalación actual y compatible de Node.js, ejecuta npm install pg en un directorio de trabajo. Guarda el siguiente programa completo como article-query.mjs. Requiere que la configuración SQL anterior se haya ejecutado en la misma base de datos. El comando de instalación del paquete proviene de la introducción a 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();
}

Ejecuta node article-query.mjs con el entorno de conexión de la base de datos configurado para la base de datos temporal. Client lee variables de conexión de PostgreSQL como PGHOST, PGPORT, PGDATABASE y PGUSER. El programa informa de los errores de conexión y de la ausencia de la tabla de demostración. Conexión.

Aquí, JSON.stringify(category) construye la cadena literal de JSONPath. La ruta resultante sigue conteniendo la igualdad con el literal \"layout\"; pasar esa ruta mediante un parámetro SQL no reemplaza la igualdad por una variable JSONPath.

Si en su lugar usas jsonb_path_exists con su argumento vars, estás llamando a una función con variables JSONPath con nombre. Esa es una interfaz de evaluación documentada, pero no la forma del operador @? que usa el índice. Para esta búsqueda GIN, conserva la consulta con el operador que se muestra arriba. Funciones y operadores JSON, Indexación de JSONB.

¿Qué deberías usar cuando la condición principal es un rango numérico?

Si la tarea principal es seleccionar artículos por recuento de palabras, expón ese valor escalar mediante un índice de expresión B-tree. PostgreSQL documenta la compatibilidad de B-tree con la igualdad y las comparaciones de rangos ordenados, y los índices de expresión permiten indexar un valor calculado. Tipos de índice, Índices sobre expresiones.

Ejecuta esta configuración y consulta adicionales sobre la tabla generada:

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;

La consulta usa deliberadamente la misma expresión que el índice. Requiere que haya un único valor de words compatible con enteros por artículo. Las filas generadas cumplen esa condición; el texto arbitrario o los valores fuera de rango pueden hacer que la conversión falle durante la creación del índice o en escrituras posteriores. Esta reescritura no sustituye de forma general las comparaciones JSONPath sobre matrices, tipos mixtos o varios valores coincidentes.

Usa este límite como guía para decidir: empieza con GIN cuando las igualdades selectivas de documentos determinan la búsqueda y considera un índice de expresión B-tree cuando el factor principal sea un rango sobre un valor escalar con tipo constante. Primero conserva el conjunto de resultados deseado y luego usa el plan real para decidir si el índice elegido justifica su costo de mantenimiento.

Fuentes