Arhitektūra

PostgreSQL JSONB indeksēšana: GIN, jsonb_path_ops un izteiksmju indeksi

GIN indekss jsonb kolonnai izskatās pēc acīmredzama risinājuma lēniem JSON vaicājumiem, un bieži tas ir nepareizais. Lūk, kuru indeksu katrs vaicājuma veids patiešām var izmantot, ko GIN maksā rakstīšanā un kā JSONB indeksēšana uzvedas tabulās ar simtiem miljonu rindu.

· 13 min lasīšanai · AG Data komanda

Pareizais PostgreSQL JSONB indekss pilnībā atkarīgs no tā, kā jūs vaicājat kolonnu. GIN indekss palīdz ietveršanas un atslēgas esamības meklēšanai, neko nedod vaicājumam doc->>'status' = 'paid' un padara katru rakstīšanu dārgāku. Izvēlieties pēc vaicājuma formas, nevis pēc datu tipa, un lielākā daļa JSONB veiktspējas problēmu pazūd.

Piemēra tabula

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  created_at timestamptz NOT NULL,
  doc        jsonb NOT NULL
);

-- a typical document
-- {"customer_id": 4182, "status": "paid", "tags": ["b2b", "eu"],
--  "shipping": {"country": "LV", "method": "courier"}}

Īss atgādinājums, kāpēc šeit ir jsonb, nevis json: jsonb tiek glabāts sadalītā binārā formātā, nesaglabā atstarpes, atslēgu secību un dublētas atslēgas (paliek pēdējā vērtība), un tikai tam no abiem ir ietveršanas operatori un GIN atbalsts. Dokumentācija iesaka jsonb lielākajai daļai lietojumprogrammu (JSON tipi).

GIN: jsonb_ops pret jsonb_path_ops

-- default operator class: jsonb_ops
CREATE INDEX CONCURRENTLY orders_doc_gin ON orders USING GIN (doc);

-- smaller, more selective, no key-exists operators
CREATE INDEX CONCURRENTLY orders_doc_gin_path ON orders USING GIN (doc jsonb_path_ops);

Abas ir GIN operatoru klases, un atšķirība ir svarīgāka, nekā vairums komandu apzinās (GIN iebūvētās operatoru klases):

Āķis ar jsonb_path_ops: tas nevar atbildēt uz jautājumu "vai atslēga X eksistē", un tas neveido ierakstus struktūrām bez vērtībām, piemēram, {"a": {}}. To meklēšanai vajag pilnu indeksa skenēšanu, ko dokumentācija sauc par diezgan lēnu. Ja jūsu lietojumprogramma nekad neizmanto ? un līdzīgos, jsonb_path_ops parasti ir labāka noklusējuma izvēle.

Kuri vaicājumi var izmantot kuru indeksu

-- can use either GIN index
SELECT id FROM orders WHERE doc @> '{"status": "paid", "shipping": {"country": "LV"}}';
SELECT id FROM orders WHERE doc @? '$.tags[*] ? (@ == "b2b")';

-- key exists: only jsonb_ops
SELECT id FROM orders WHERE doc ? 'refund_id';

-- cannot use a GIN index on doc at all
SELECT id FROM orders WHERE doc->>'status' = 'paid';

Pēdējais vaicājums ir tas, ko lēno vaicājumu žurnālos redzam visbiežāk. ->> atgriež tekstu, un salīdzināšana nav neviens no GIN operatoriem, tāpēc GIN indekss uz doc vienkārši tiek ignorēts. Pārrakstot to kā ietveršanu, doc @> '{"status": "paid"}', GIN indekss kļūst izmantojams, bet tikai ja vērtība glabāta kā tas pats JSON tips: "4182" kā virkne neatbilst 4182 kā skaitlim.

Jsonpath gadījumā GIN palīdz tikai ar nosacījumiem formā accessor_chain == constant. Diapazona nosacījumus jsonpath iekšienē, piemēram, @.amount > 100, indekss neapkalpo. Vienmēr pārbaudiet ar EXPLAIN ANALYZE, nevis pieņemiet.

Izteiksmju indeksi: parasti īstā atbilde

Ja lielākā daļa vaicājumu filtrē, savieno vai kārto pēc viena vai diviem labi zināmiem laukiem, indeksējiet tieši šos laukus:

-- B-tree on one extracted field: equality, ranges, ORDER BY
CREATE INDEX CONCURRENTLY orders_customer_idx
  ON orders (((doc->>'customer_id')::bigint));

SELECT id FROM orders
WHERE (doc->>'customer_id')::bigint = 4182
ORDER BY created_at DESC LIMIT 20;

-- GIN on one sub-document instead of the whole thing
CREATE INDEX CONCURRENTLY orders_tags_gin ON orders USING GIN ((doc->'tags'));
SELECT id FROM orders WHERE doc->'tags' ? 'b2b';
Ja lauks ir katrā vaicājumā, tas, visticamāk, grib būt kolonna. Mūsu pieredzē tīrākais risinājums karstai JSONB atslēgai, piemēram, klienta vai īrnieka ID, ir pārvērst to par īstu kolonnu ar parastu indeksu un pārējo dokumentu paturēt kā JSONB.

Ko GIN maksā rakstīšanā

Dokumentācija ir tieša: GIN indeksa atjaunināšana mēdz būt lēna, jo viena rinda var radīt daudzus indeksa ierakstus, pa vienam katrai izvilktajai atslēgai vai vērtībai. Lai to mīkstinātu, GIN ir fast update režīms, pēc noklusējuma ieslēgts: jaunie ieraksti vispirms nonāk nesakārtotā gaidošo ierakstu sarakstā un vēlāk tiek apvienoti masveidā (CREATE INDEX glabāšanas parametri).

-- pending list size per GIN index (default 4MB, set globally or per index)
ALTER INDEX orders_doc_gin SET (gin_pending_list_limit = 16384);  -- kB

-- predictable read latency instead of faster inserts
ALTER INDEX orders_doc_gin SET (fastupdate = off);
SELECT gin_clean_pending_list('orders_doc_gin');  -- flush what is already queued

Ir arī otra, klusāka cena. HOT atjauninājums, kas izvairās no jauniem indeksa ierakstiem, iespējams tikai tad, ja nemainās neviena indeksēta kolonna (HOT atjauninājumi). Ar GIN indeksu uz visu doc katrs atjauninājums, kas skar dokumentu, nav HOT un raksta visos tabulas indeksos, ne tikai GIN. Izteiksmes indeksam uz vienu lauku ir tāds pats efekts, jo indeksētā kolonna ir doc.

JSONB indeksi lielās tabulās

JSONB indekss, kas testēts uz 50 000 rindām, maz ko pasaka par 500 miljoniem. Kas mainās mērogā:

Galvenās atziņas

PostgreSQL JSONB GIN indekss Veiktspēja
Sadarbība ar mums

JSONB vaicājumi kļūst lēnāki ar katru mēnesi?

Mēs pārskatām shēmas, vaicājumu plānus un indeksu dizainu PostgreSQL datubāzēm, kur JSONB kolonnās glabājas simtiem miljonu dokumentu, un salabojam tos bez dīkstāves.

Sazināties Vairāk no emuāra