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):
jsonb_opsir noklusējums. Tā atbalsta ietveršanu@>, jsonpath operatorus@?un@@un atslēgas esamības operatorus?,?|un?&. Tā izveido atsevišķus indeksa ierakstus katrai atslēgai un katrai vērtībai.jsonb_path_opsatbalsta tikai@>,@?un@@. Katrs ieraksts ir vērtības jaucējkods kopā ar atslēgām, kas uz to ved. Dokumentācijā teikts, ka šāds indekss parasti ir daudz mazāks un meklēšana parasti darbojas labāk, īpaši ja vaicājumā minētas atslēgas, kas ir gandrīz katrā dokumentā.
Āķ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';
- B-tree izteiksmes indekss atbalsta vienādību, diapazonus un
ORDER BY, ko GIN nekad nedara. - Vaicājumam jāizmanto tieši indeksētā izteiksme, ieskaitot tipa pārveidošanu.
doc->>'customer_id' = '4182'neizmantos indeksu, kas veidots uz(doc->>'customer_id')::bigint. - GIN indekss uz apakšdokumentu, piemēram,
doc->'tags', dokumentācijas vārdiem, visticamāk būs mazāks un ātrāk pārmeklējams nekā indekss uz visu dokumentu. - Izteiksmes indekss plānotājam dod arī statistiku par šo izteiksmi. Ja vajag tikai labākus novērtējumus, nevis indeksu,
CREATE STATISTICSuz izteiksmi dod līdzīgu statistiku bez indeksa uzturēšanas (CREATE STATISTICS).
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).
- Sarakstu iztukšo vacuum vai autoanalyze, funkcija
gin_clean_pending_list()vai tas, ka tas pārsniedzgin_pending_list_limit, pēc noklusējuma 4MB (klienta iestatījumi). - Meklēšanai jāskenē arī gaidošais saraksts, tāpēc liels saraksts palēnina lasīšanu. Un ievietošana, kas to pārspiež pāri limitam, samaksā par visu tīrīšanu, kas izpaužas kā nejauši lēnas rakstīšanas.
- Ja stabils latentums ir svarīgāks par ievietošanas ātrumu, izslēdziet
fastupdate. Izslēgšana neiztukšo to, kas jau ir rindā; pēc tam palaidiet vacuum vai izsauciet funkciju.
-- 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ā:
- Indeksa izmērs. Mūsu pieredzē
jsonb_opsindekss bagātīgiem dokumentiem var tuvoties pašas tabulas izmēram. Pirms izvēles izmēriet abas klases uz produkcijas izmēra kopijas arpg_relation_size; atšķirība bieži ir pietiekami liela, lai izšķirtu jautājumu pati. - Būvēšanas laiks un bloķēšana. Parasts
CREATE INDEXbloķē rakstīšanu, līdz tas pabeigts; lietojietCONCURRENTLYun, ja tas neizdodas, pārbaudiet, vai nav palicisINVALIDindekss. Saskaņā ar dokumentāciju GIN būvēšanas laiks ir ļoti jutīgs pretmaintenance_work_mem, tāpēc palieliniet to būvēšanas sesijai. - Gaidošais saraksts un autovacuum. Tabulā ar tūkstošiem ievietošanu sekundē gaidošais saraksts pildās nemitīgi. Ja autovacuum šai tabulai ir lēns, lasīšana pasliktinās un rakstīšanas latentums lec. Tabulas līmeņa autovacuum iestatījumi un apzināti izvēlēts
gin_pending_list_limitšeit ir svarīgāki nekā jebkuram B-tree. - Rakstīšanas pastiprinājums. HOT atjauninājumu zaudēšana bieži atjauninātai JSONB kolonnai nozīmē vairāk indeksa rakstīšanu, vairāk WAL, vairāk replikācijas datplūsmas un vairāk vacuum darba. Mūsu pieredzē tas ir slēptais iemesls, kāpēc daudzas sūdzības "JSONB ir lēns" patiesībā ir "šī tabula raksta pārāk daudz WAL".
- TOAST. Rindas, kas platākas par aptuveni 2 kB, tiek saspiestas vai pārvietotas ārpus rindas. Lieli dokumenti nozīmē, ka katram
doc->>'x'uz neindeksēta ceļa vispirms jāatspiež vērtība, un par šo CPU un I/O secīgās skenēšanas laikā maksājat par katru rindu. - Indeksu izplešanās. JSONB mudina pievienot "vēl vienu izteiksmes indeksu" katram jaunam filtram. Katrs no tiem maksā rakstīšanā; periodiski tos pārskatiet un dzēsiet tos, ko neviens neizmanto, kā aprakstīts mūsu ceļvedī par neizmantotu PostgreSQL indeksu atrašanu.
Galvenās atziņas
- GIN apkalpo
@>,@?,@@un (arjsonb_ops)?,?|,?&; tas neko nedod->>salīdzinājumiem. - Dodiet priekšroku
jsonb_path_ops, ja nekad nepārbaudāt atslēgas esamību: mazāks un parasti ātrāks. - Vienādībai, diapazoniem un kārtošanai pēc zināma lauka lietojiet B-tree izteiksmes indeksu vai pārvērtiet lauku par kolonnu.
- GIN rakstīšana ir dārga; sekojiet gaidošajam sarakstam, autovacuum un HOT atjauninājumu zudumam.
- Lielās tabulās izmēriet indeksa izmēru un būvēšanas laiku uz produkcijas izmēra datiem un būvējiet ar
CONCURRENTLY.