PostgreSQL JSONB-GIN-indexen kunnen JSONPath-zoekopdrachten beperken met behulp van extraheerbare gelijkheidstests van pad naar constante. Bereikvergelijkingen, ongelijkheid en eenvoudige tests op het bestaan van een pad leveren op zichzelf die zoeksleutels niet op. Gemengde predicaten vereisen nadere inspectie.

Deze handleiding helpt een ontwikkelaar die in Terminal op een Mac werkt om een index te kiezen voor het filteren van synthetische artikelrijen, de query via node-postgres uit te voeren en het resulterende plan te inspecteren. De kernregel voor extractie is beschreven in de JSONB-indexreferentie van PostgreSQL.

Welke predicaatvormen leveren bruikbare GIN-zoeksleutels op?

Voor @? en @@ extraheert PostgreSQL gelijkheidsclausules die een accessor-keten met een constante verbinden. Ondersteunde accessors zijn onder meer benoemde leden, array-wildcards en array-indexen. Beide JSONB-GIN-operator-klassen ondersteunen deze vormen; jsonb_ops ondersteunt daarnaast object-wildcards en recursieve afdaling. JSON-typen.

JSONPath-expressie Bruikbare geëxtraheerde zoekvoorwaarde?
$.category == "layout" Ja, met beide operator-klassen
$.tags[*] == "grid" Ja, met beide operator-klassen
$.tags[0] == "grid" Ja, met beide operator-klassen; het volledige predicaat moet nog worden gecontroleerd
$.* == "layout" Ondersteund door jsonb_ops, niet door jsonb_path_ops
$.**.category == "layout" Ondersteund door jsonb_ops, niet door jsonb_path_ops

De volgende grenzen volgen uit de extractie-implementatie van PostgreSQL 18. ‘Geen’ betekent hier dat die expressie op zichzelf geen bruikbare geëxtraheerde voorwaarde oplevert, niet dat de scan-node in het plan een bepaalde naam krijgt. PostgreSQL 18 JSONB-GIN-implementatie.

JSONPath-expressie Geëxtraheerde voorwaarde
$.words > 1200 Geen
$.category != "layout" Geen
exists($.category) Geen
$.title like_regex \"Grid\" Geen
$.words == $.target_words Geen: geen van beide operanden is een scalaire constante

Waarom gedragen AND en OR zich verschillend?

Met $.category == "layout" && $.words > 1200 kan de gelijkheid kandidaten selecteren; de volledige voorwaarde wordt opnieuw gecontroleerd. Met $.category == "layout" || $.words > 1200 zouden rijen die aan het bereik voldoen worden gemist als alleen de gelijkheid werd gebruikt. Daarom mislukt de extractie voor die OR als een van beide takken niet wordt ondersteund. Een OR die twee ondersteunde gelijkheden verbindt, kan zoekvoorwaarden opleveren. Deze regels betreffen extractie binnen één JSONPath-expressie; andere SQL-voorwaarden kunnen nog steeds invloed hebben op het totale plan. PostgreSQL 18 JSONB-GIN-implementatie.

Welke operator en operator-klasse kies je?

Gebruik @? om te vragen of een pad een item oplevert, meestal via een filter. Gebruik @@ voor een Booleaanse predicaattest. Een kale Booleaanse vergelijking die aan @? wordt doorgegeven, is een valkuil: zelfs een Booleaanse waarde false is een geretourneerd item. PostgreSQL maakt in JSON-functies en -operatoren expliciet onderscheid tussen deze vormen.

Deze twee queries drukken dezelfde taak uit voor de voorbeeldstructuur van artikelen die hieronder wordt geïntroduceerd:

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

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

Kies jsonb_path_ops voor de getoonde workload met gelijkheden op benoemde paden. Kies de standaardwaarde jsonb_ops als je ook SQL-operatoren voor het bestaan van sleutels nodig hebt, zoals ?, of de hierboven beschreven wildcard-accessors. Beide ondersteunen @>, @? en @@; ondersteuning van een operator betekent op zichzelf niet dat elk predicaat selectief is. JSONB-indexering.

De voorbeelden zijn gericht op PostgreSQL 18, de versie die in de geraadpleegde officiële documentatie momenteel wordt vermeld. Ondersteuning voor SQL/JSON-paden is geïntroduceerd in PostgreSQL 12; deze operatoren zijn dus niet beschikbaar op oudere servers. PostgreSQL 12 wordt inmiddels als niet meer ondersteund vermeld; de releaseopmerkingen tonen de introductiegrens aan, niet een aanbevolen installatieversie. Releaseopmerkingen PostgreSQL 12.

Hoe kun je de query reproduceren op een Mac?

Begin met een actieve PostgreSQL 18-database, de opdracht psql beschikbaar in Terminal en een bestaande tijdelijke database die via je gebruikelijke verbindingsinstellingen is geselecteerd. Deze oefening maakt een nieuwe tabel aan en vervangt geen bestaande tabel. Als die tabelnaam al bestaat, gebruik dan een andere tijdelijke database of geef de demonstratietabel overal consequent een andere naam.

Open psql in Terminal, voer SHOW server_version; uit en plak vervolgens deze volledige setup. Hiermee worden 20.000 synthetische artikelrijen aangemaakt: elk honderdste artikel heeft categorie layout en de woordenaantallen lopen cyclisch van 400 tot en met 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;

De afgeleide eerste vijf ID's zijn 900, 1000, 1100, 1200 en 1300; de volledige voorwaarde komt overeen met 84 gegenereerde rijen. Dit zijn illustratieve resultaten van de dataset, geen prestatiemetingen. De berekening en verwachte ID's zijn gecontroleerd met een kleine JavaScript-berekening; uitvoering van PostgreSQL was niet beschikbaar in de onderzoeksomgeving.

ANALYZE verzamelt tabelstatistieken die de planner gebruikt bij het kiezen van uitvoeringsplannen. Als je dit na het laden uitvoert, krijgt de demonstratie een bruikbaardere basis voor de planning. ANALYZE.

Hoe zie je of GIN heeft geholpen?

Voer na de setup deze twee volledige diagnostische queries uit in dezelfde database. De eerste bevat een extraheerbare gelijkheid; de tweede isoleert de bereikvoorwaarde.

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

Zoek bij de eerste query naar een Bitmap Index Scan met de naam jsonpath_article_demo_doc_gin, doorgaans onder een Bitmap Heap Scan. Beoordeel Index Cond, Recheck Cond, de werkelijke aantallen rijen, bufferactiviteit en uitvoeringstijd samen. Dat een indexvoorwaarde het volledige JSONPath toont, betekent niet dat elke vergelijking een index-zoeksleutel is geworden. In EXPLAIN gebruiken worden deze planvelden en het verschil tussen kandidaten ophalen en daarna filteren uitgelegd.

Een sequentiële scan bewijst op zichzelf niet dat de index in aanmerking kwam. De planner kiest op basis van geschatte kosten; een kleine tabel of een brede overeenkomst kan een scan gunstiger maken. Omgekeerd bewijst het zien van een indexnode niet dat er nuttig is gefilterd. Vergelijk de uitgevoerde hoeveelheid werk en de verstreken tijd onder vergelijkbare omstandigheden. EXPLAIN ANALYZE voert elke query daadwerkelijk uit. EXPLAIN gebruiken.

Voor deze oefening is de praktische vraag of de categorie-gelijkheid het aantal kandidaten genoeg verlaagt om de woordenaantalvoorwaarde goedkoop te kunnen evalueren. De verdeling van de gegenereerde categorieën biedt een helder testgeval, maar kan niet bepalen welk plan het beste is voor een artikelverzameling met een andere verdeling.

Hoe geef je het JSONPath door met node-postgres?

Geef het volledige JSONPath door als één SQL-parameter en cast het naar jsonpath. De SQL-placeholder $1 en de JSONPath-root $ horen bij verschillende talen. node-postgres accepteert querytekst plus een array met waarden; de server ontvangt het opgegeven pad als parameterwaarde. Queries.

Voer met een actuele, ondersteunde Node.js-installatie npm install pg uit in een werkmap. Sla het volgende volledige programma op als article-query.mjs. De SQL-setup hierboven moet in dezelfde database zijn uitgevoerd. De installatieopdracht komt uit de introductie van 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();
}

Voer node article-query.mjs uit terwijl de databaseverbindingsomgeving is ingesteld voor de tijdelijke database. Client leest PostgreSQL-verbindingsvariabelen zoals PGHOST, PGPORT, PGDATABASE en PGUSER. Verbindingsfouten en een ontbrekende demonstratietabel worden door het programma gemeld. Verbinden.

Hier construeert JSON.stringify(category) de JSONPath-tekenreeksliteral. Het resulterende pad bevat nog steeds de gelijkheid met de letterlijke waarde \"layout\"; door dat pad via een SQL-parameter door te geven, wordt de gelijkheid niet vervangen door een JSONPath-variabele.

Als je in plaats daarvan jsonb_path_exists met het argument vars gebruikt, roep je een functie aan met benoemde JSONPath-variabelen. Dat is een gedocumenteerde interface voor evaluatie, maar niet de vorm van de geïndexeerde @?-operator. Gebruik voor deze GIN-opzoeking de hierboven getoonde operatorquery. JSON-functies en -operatoren, JSONB-indexering.

Wat gebruik je als de hoofdvoorwaarde een numeriek bereik is?

Als artikelen selecteren op woordenaantal de hoofdtaak is, maak die scalaire waarde dan toegankelijk via een B-tree-expressie-index. PostgreSQL documenteert ondersteuning van B-trees voor gelijkheden en geordende bereikvergelijkingen; expressie-indexen maken het mogelijk een berekende waarde te indexeren. Indextypen, Indexen op expressies.

Voer deze aanvullende setup en query uit op de gegenereerde tabel:

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;

De query gebruikt bewust dezelfde expressie als de index. Hiervoor moet elk artikel één waarde van words hebben die compatibel is met een integer. Aan die voorwaarde voldoen de gegenereerde rijen; bij willekeurige tekst of waarden buiten het toegestane bereik kan de cast mislukken tijdens het aanmaken van de index of bij latere schrijfbewerkingen. Deze herschrijving is geen algemene vervanging voor JSONPath-vergelijkingen op arrays, gemengde typen of meerdere overeenkomende waarden.

Gebruik deze grens als leidraad bij je keuze: begin met GIN wanneer selectieve documentgelijkheden de opzoeking sturen, en overweeg een B-tree-expressie-index wanneer een bereikvoorwaarde op een consistent getypeerde scalaire waarde leidend is. Zorg eerst dat de bedoelde resultatenset behouden blijft en gebruik daarna het daadwerkelijke plan om te bepalen of de gekozen index de onderhoudskosten waard is.

Bronnen