Gli indici GIN JSONB di PostgreSQL possono restringere le ricerche JSONPath usando test di uguaglianza estraibili tra percorsi e costanti. I confronti di intervallo, le disuguaglianze e i test di semplice esistenza del percorso non forniscono da soli queste chiavi di ricerca. I predicati misti richiedono un esame più attento.

Questa guida aiuta uno sviluppatore che lavora nel Terminale su un Mac a scegliere un indice per filtrare righe di articoli sintetiche, passare la query tramite node-postgres e ispezionare il piano risultante. La regola fondamentale di estrazione è documentata nella documentazione di riferimento sull’indicizzazione JSONB di PostgreSQL.

Quali forme di predicato forniscono chiavi di ricerca GIN utili?

Per @? e @@, PostgreSQL estrae clausole di uguaglianza che collegano una catena di accessori a una costante. Gli accessori supportati includono membri con nome, caratteri jolly per array e indici di array. Entrambe le classi di operatori GIN JSONB supportano queste forme; jsonb_ops supporta inoltre i caratteri jolly per oggetti e la discesa ricorsiva. Tipi JSON.

Espressione JSONPath Condizione di ricerca utile estratta?
$.category == "layout" Sì, con entrambe le classi di operatori
$.tags[*] == "grid" Sì, con entrambe le classi di operatori
$.tags[0] == "grid" Sì, con entrambe le classi di operatori; occorre comunque verificare il predicato completo
$.* == "layout" Supportata da jsonb_ops, non da jsonb_path_ops
$.**.category == "layout" Supportata da jsonb_ops, non da jsonb_path_ops

I limiti seguenti derivano dall’implementazione di estrazione di PostgreSQL 18. Qui, “nessuna” significa che l’espressione da sola non produce condizioni estratte utili, e non garantisce il nome del nodo di scansione nel piano. Implementazione GIN JSONB di PostgreSQL 18.

Espressione JSONPath Condizione estratta
$.words > 1200 Nessuna
$.category != "layout" Nessuna
exists($.category) Nessuna
$.title like_regex \"Grid\" Nessuna
$.words == $.target_words Nessuna: nessuno dei due operandi è una costante scalare

Perché AND e OR si comportano diversamente?

Con $.category == "layout" && $.words > 1200, l’uguaglianza può selezionare i candidati; la condizione completa viene ricontrollata. Con $.category == "layout" || $.words > 1200, usare solo l’uguaglianza farebbe perdere le righe che soddisfano l’intervallo. Perciò l’estrazione fallisce per quella OR se uno dei rami non è supportato. Una OR che combina due uguaglianze supportate può fornire condizioni di ricerca. Queste regole riguardano l’estrazione all’interno di una singola espressione JSONPath; altre condizioni SQL possono comunque influire sul piano complessivo. Implementazione GIN JSONB di PostgreSQL 18.

Quale operatore e quale classe di operatori dovresti scegliere?

Usa @? per verificare se un percorso restituisce un elemento qualsiasi, in genere tramite un filtro. Usa @@ per verificare un predicato booleano. Passare a @? un semplice confronto booleano è una trappola: anche un valore booleano false è un elemento restituito. PostgreSQL distingue esplicitamente queste forme in Funzioni e operatori JSON.

Queste due query esprimono la stessa operazione per la struttura di esempio degli articoli introdotta di seguito:

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

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

Scegli jsonb_path_ops per il carico di lavoro dimostrato con uguaglianze su percorsi con nome. Scegli jsonb_ops, la classe predefinita, se ti servono anche operatori SQL di esistenza delle chiavi come ? o gli accessori con caratteri jolly descritti sopra. Entrambe supportano @>, @? e @@; il solo supporto degli operatori non rende selettivo ogni predicato. Indicizzazione JSONB.

Gli esempi sono destinati a PostgreSQL 18, la versione attualmente indicata dalla documentazione ufficiale consultata. Il supporto per i percorsi SQL/JSON è stato introdotto in PostgreSQL 12, quindi questi operatori non sono disponibili nei server precedenti. PostgreSQL 12 è ora indicato come non più supportato; le sue note di rilascio stabiliscono il limite di introduzione, non una versione consigliata per l’installazione. Note di rilascio di PostgreSQL 12.

Come puoi riprodurre la query su un Mac?

Inizia con un database PostgreSQL 18 in esecuzione, il comando psql disponibile nel Terminale e un database temporaneo esistente selezionato tramite le normali impostazioni di connessione. Questo esercizio crea una nuova tabella e non ne sostituisce una esistente. Se il nome della tabella esiste già, usa un altro database temporaneo oppure rinomina in modo coerente la tabella dimostrativa.

Apri psql nel Terminale, esegui SHOW server_version; e poi incolla questa configurazione completa. Crea 20.000 righe sintetiche di articoli: ogni centesimo articolo ha la categoria layout e il conteggio delle parole varia ciclicamente da 400 a 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;

I primi cinque ID risultanti sono 900, 1000, 1100, 1200 e 1300; la condizione completa corrisponde a 84 righe generate. Sono risultati illustrativi del dataset, non misurazioni delle prestazioni. I calcoli e gli ID attesi sono stati verificati con un piccolo calcolo JavaScript; nell’ambiente di ricerca non era disponibile l’esecuzione di PostgreSQL.

ANALYZE raccoglie statistiche della tabella che il planner usa per scegliere i piani di esecuzione. Eseguirlo dopo il caricamento offre alla dimostrazione una base di pianificazione più utile. ANALYZE.

Come puoi capire se GIN è stato utile?

Dopo la configurazione, esegui queste due query diagnostiche complete sullo stesso database. La prima include un’uguaglianza estraibile; la seconda isola il predicato di intervallo.

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

Per la prima query, cerca un Bitmap Index Scan che indichi jsonpath_article_demo_doc_gin, in genere sotto un Bitmap Heap Scan. Esamina insieme Index Cond, Recheck Cond, i conteggi effettivi delle righe, l’attività dei buffer e il tempo di esecuzione. Una condizione di indice che mostra l’intero JSONPath non significa che ogni confronto sia diventato una chiave di ricerca dell’indice. La documentazione di PostgreSQL su Uso di EXPLAIN spiega questi campi del piano e la distinzione tra il recupero dei candidati e il successivo filtraggio.

Una scansione sequenziale non dimostra di per sé che l’indice non sia idoneo. Il planner sceglie in base ai costi stimati e una tabella piccola o una corrispondenza ampia possono favorire la scansione. Al contrario, la semplice presenza di un nodo indice non dimostra che la ricerca sia stata ristretta in modo utile. Confronta il lavoro svolto e il tempo trascorso in condizioni comparabili. EXPLAIN ANALYZE esegue effettivamente ogni query. Uso di EXPLAIN.

Per questo esercizio, la domanda pratica è se l’uguaglianza sulla categoria riduce abbastanza i candidati da rendere poco costosa la valutazione della condizione sul conteggio delle parole. La distribuzione delle categorie generate offre un caso di prova chiaro, ma non permette di stabilire il piano migliore per una raccolta di articoli distribuita diversamente.

Come dovrebbe passare node-postgres il JSONPath?

Passa l’intero JSONPath come singolo parametro SQL e convertilo esplicitamente in jsonpath. Il segnaposto SQL $1 e la radice $ di JSONPath appartengono a linguaggi diversi. node-postgres accetta il testo della query insieme a un array di valori; il server riceve il percorso fornito come valore del parametro. Query.

Con un’installazione corrente e supportata di Node.js, esegui npm install pg in una directory di lavoro. Salva il programma completo seguente come article-query.mjs. Richiede che la configurazione SQL sopra sia stata eseguita sullo stesso database. Il comando di installazione del pacchetto proviene dall’introduzione 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();
}

Esegui node article-query.mjs con l’ambiente di connessione al database configurato per il database temporaneo. Client legge variabili di connessione PostgreSQL come PGHOST, PGPORT, PGDATABASE e PGUSER. Il programma segnala gli errori di connessione e l’assenza della tabella dimostrativa. Connessione.

Qui, JSON.stringify(category) costruisce la stringa letterale JSONPath. Il percorso risultante contiene ancora l’uguaglianza con il valore letterale \"layout\"; passare quel percorso tramite un parametro SQL non sostituisce l’uguaglianza con una variabile JSONPath.

Se invece usi jsonb_path_exists con il suo argomento vars, chiami una funzione con variabili JSONPath denominate. È un’interfaccia di valutazione documentata, ma non corrisponde alla forma dell’operatore indicizzato @?. Per questa ricerca GIN, mantieni la query con operatore mostrata sopra. Funzioni e operatori JSON, Indicizzazione JSONB.

Cosa dovresti usare quando la condizione principale è un intervallo numerico?

Se l’operazione principale è selezionare articoli in base al conteggio delle parole, esponi quel valore scalare con un indice di espressione B-tree. PostgreSQL documenta il supporto dei B-tree per uguaglianze e confronti di intervallo ordinati, mentre gli indici di espressione permettono di indicizzare un valore calcolato. Tipi di indice, Indici su espressioni.

Esegui questa configurazione aggiuntiva e questa query sulla tabella generata:

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 query usa deliberatamente la stessa espressione dell’indice. Il prerequisito è che ogni articolo abbia un singolo valore words compatibile con il tipo intero. Le righe generate soddisfano questa condizione; valori di testo arbitrari o fuori intervallo possono far fallire la conversione durante la creazione dell’indice o le scritture successive. Questa riscrittura non è un sostituto generale per i confronti JSONPath su array, tipi misti o più valori corrispondenti.

Usa questo limite come guida decisionale: inizia con GIN quando la ricerca dipende da uguaglianze selettive nei documenti e valuta un indice di espressione B-tree quando la ricerca dipende da un intervallo su uno scalare di tipo coerente. Conserva prima l’insieme di risultati desiderato, poi usa il piano effettivo per decidere se l’indice scelto giustifica il costo di manutenzione.

Fonti