PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

JSONB-operaattorit ja sisältökyselyt

Käyttäkää sisältö- ja polkuoperaattoreita, joita GIN-indeksit voivat todella nopeuttaa.

Oppitunti 1/413 vaihetta

JSONB-operaattorit ja sisältökyselyt on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 1/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Miksi operaattorin valinta ratkaisee indeksin käytön

PostgreSQL:ssä jsonb-tyyppistä saraketta voidaan hakea monella eri tavalla, mutta kaikki operaattorit eivät voi käyttää indeksiä. Suorituskyky riippuu tässä lähes kokonaan sellaisten operaattorien valinnasta, joita GIN-indeksi voi nopeuttaa.

GIN-indeksi (Generalized Inverted Index) tallentaa JSON-dokumenttien sisällä olevat avaimet ja arvot, joten haut voivat ohittaa koko taulun läpikäynnin.
Kaksi tärkeintä operaattoria ovat sisältäminen (@>) ja avaimen olemassaolo (?, ?|, ?&).

Tässä oppitunnissa opetellaan tarkalleen, mitkä operaattorit nämä ovat ja miten kyselyitä kirjoitetaan niin, että ne pysyvät indeksiystävällisinä.

Sisältämisoperaattori @>

Sisältämisoperaattori @> kysyy: sisältääkö vasemmanpuoleinen JSONB oikeanpuoleisen JSONB:n? Oikea puoli on katkelma, ja Postgres tarkistaa, että sen jokainen avain ja arvo esiintyy vasemmanpuoleisessa dokumentissa.

'{"a":1,"b":2}' @> '{"a":1}' on tosi.
'{"a":1}' @> '{"a":1,"b":2}' on epätosi (oikea puoli sisältää enemmän).

Tämä on rivien suodattamisen perustyökalu: WHERE data @> '{"status":"active"}' löytää jokaisen rivin, jonka JSON sisältää kyseisen avain-arvoparin.

SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS contains_a,
       '{"a":1}'::jsonb @> '{"a":1,"b":2}'::jsonb AS contains_both;

GIN-indeksin luominen sisältämistä varten

Tavallinen GIN-indeksi jsonb-sarakkeessa tukee sekä sisältämis- että avaimen olemassaolo -operaattoreita. Tämä on ensimmäinen indeksi, jota kannattaa kokeilla.

Oletusarvoinen jsonb_ops-operaattoriluokka indeksoi jokaisen avaimen ja arvon.
Se nopeuttaa operaattoreita @>, ?, ?| ja ?&.

Kun indeksi on luotu kerran, suodattimet, jotka aiemmin kävivät läpi koko taulun, muuttuvat bittikarttaindeksihauiksi.

CREATE INDEX idx_events_data
  ON events
  USING GIN (data);

Sisältämissuodattimet WHERE-lauseessa

Kun GIN-indeksi on olemassa, kirjoita suodatin sisältämistarkistuksena, jotta suunnittelija voi käyttää sitä. Myös sisäkkäisen katkelman täsmäytys toimii, koska sisältäminen on rekursiivista.

Ylimmän tason täsmäytys: data @> '{"status":"active"}'.
Sisäkkäinen täsmäytys: data @> '{"user":{"plan":"pro"}}'.

Huomaa, että oikealla puolella välitetään JSON-olioliteraali, ei sarakeviitettä tai funktiokutsua. Juuri tämä literaalin muoto tekee kyselystä indeksikelpoisen.

SELECT id, created_at
FROM events
WHERE data @> '{"user":{"plan":"pro"}}'
ORDER BY created_at DESC
LIMIT 50;

Avaimen olemassaolo -operaattorit ? ?| ?&

Joskus haluat tietää vain, onko avain olemassa, sen arvosta riippumatta. Olemassaolo-operaattorit hoitavat tämän, ja myös GIN-indeksi nopeuttaa niitä.

data ? 'email' — tosi, jos ylimmän tason avain email on olemassa.
data ?| array['phone','email'] — tosi, jos vähintään yksi näistä avaimista on olemassa.
data ?& array['phone','email'] — tosi, jos kaikki nämä avaimet ovat olemassa.

Tärkeää: ? tarkistaa vain ylimmän tason avaimet, ja taulukoissa se tarkistaa, onko merkkijono alkiona.

SELECT '{"email":"x@y.z","phone":"123"}'::jsonb ? 'email'        AS has_email,
       '{"email":"x@y.z"}'::jsonb ?| array['phone','email']      AS has_any,
       '{"email":"x@y.z"}'::jsonb ?& array['phone','email']      AS has_all;

Ansa: polun poimintaoperaattorit -> ja ->>

Poimintaoperaattorit vaikuttavat käteviltä, mutta tavallinen GIN-indeksi ei nopeuta niitä:

data -> 'status' palauttaa arvon muodossa jsonb.
data ->> 'status' palauttaa arvon muodossa text.

Kysely kuten WHERE data ->> 'status' = 'active' pakottaa tekemään järjestyksessä etenevän haun tavallisella GIN-indeksillä, koska indeksi ei indeksoi poimittujen skalaarien vertailuja. Käytä sen sijaan sisältämismuotoa data @> '{"status":"active"}'.

-- Slow on a plain GIN index (seq scan):
SELECT * FROM events WHERE data ->> 'status' = 'active';

-- Fast equivalent (uses GIN):
SELECT * FROM events WHERE data @> '{"status":"active"}';

->>-operaattorin pelastaminen lausekeindeksillä

Jos tarvitset todella yhden kentän alue- tai merkkijonokuviovertailuja, oikea työkalu on poimitun tekstin B-puun lausekeindeksi — ei GIN.

Indeksoi täsmälleen se lauseke, jota kyselyssä käytät.
Tämän jälkeen vertailut kuten =, <, > ja BETWEEN voivat käyttää sitä.

Kyselyn lausekkeen on vastattava indeksoitua lauseketta merkki merkiltä, muuten suunnittelija jättää indeksin huomiotta.

CREATE INDEX idx_events_status
  ON events ((data ->> 'status'));

-- Now this can use the B-tree index:
SELECT * FROM events WHERE (data ->> 'status') = 'active';

jsonb_path_ops: pienempi, nopeampi ja vain sisältämiseen

Vaihtoehtoinen operaattoriluokka jsonb_path_ops indeksoi hajautetut juuresta lehteen kulkevat polut kaikkien avainten sijaan.

Se tuottaa pienemmän indeksin ja on yleensä nopeampi @>-kyselyissä.
Haittapuoli: se tukee vain sisältämistä (@>), ei olemassaolo-operaattoreita ?, ?| ja ?&.

Valitse jsonb_path_ops, kun työkuormasi koostuu pääasiassa sisältämissuodatuksesta etkä koskaan tarvitse avainten olemassaolohakuja.

CREATE INDEX idx_events_data_path
  ON events
  USING GIN (data jsonb_path_ops);

Sisältäminen taulukoita vasten

Sisältämisen tarkistus toimii myös JSON-taulukoiden sisällä, joten se sopii erinomaisesti tunnisteita sisältävään dataan. Kun haluat kysyä, "sisältääkö tämä taulukko arvon", ympäröi arvo taulukolla oikealla puolella.

'["a","b","c"]' @> '["b"]' on tosi.
Tunnisteita sisältävässä dokumentissa data @> '{"tags":["urgent"]}' löytää rivit, joiden tags-taulukossa on alkio urgent.

Tämä pysyy täysin indeksikelpoisena GIN-indeksillä, joten tunnisteiden suodatus skaalautuu hyvin.

SELECT '["a","b","c"]'::jsonb @> '["b"]'::jsonb   AS has_b,
       '{"tags":["urgent","billing"]}'::jsonb
         @> '{"tags":["urgent"]}'::jsonb            AS is_urgent;

Varmista EXPLAIN-komennolla

Älä oleta, että indeksiä käytetään — varmista se. Suorita EXPLAIN ja etsi GIN-indeksistä Bitmap Index Scan -operaatio. Seq Scan tarkoittaa, että operaattori tai lauseke esti indeksin käytön.

Hyvä merkki: Bitmap Index Scan on idx_events_data.
Huono merkki: Seq Scan on events JSON-suodattimen yhteydessä.

Käytä komentoa EXPLAIN (ANALYZE, BUFFERS), jos haluat nähdä myös todellisen ajoitusdatan ja luettujen sivujen määrän.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE data @> '{"status":"active"}';

Kaiken yhdistäminen: päätössääntö

Käytä tätä pikasääntöä JSONB-suodatinta kirjoittaessasi:

Täsmäytätkö avain-arvoparin tai sisäkkäisen katkelman? Käytä @>-operaattoria ja GIN-indeksiä.
Tarkistatko vain avaimen olemassaolon? Käytä operaattoreita ?/?|/?& oletusarvoisen jsonb_ops-GIN-indeksin kanssa.
Onko työkuorma pelkkää sisältämistä ja haluat pienimmän indeksin? Käytä jsonb_path_ops-GIN-indeksiä.
Alue- tai merkkijonokuviovrtailu yhdelle skalaarikentälle? Käytä B-puun lausekeindeksiä operaattorille ->>.

Vältä ->>-operaattorin yhtäsuuruussuodattimia ilman vastaavaa lausekeindeksiä — ne käynnistävät järjestyksessä etenevän haun.

Pikatarkistus

Sinulla on oletusarvoinen jsonb_ops-GIN-indeksi sarakkeessa events.data. Mikä WHERE-lauseke voi käyttää tätä indeksiä?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Opit, mitkä JSONB-operaattorit todella hyötyvät indeksoinnista:

@> (sisältäminen) on ensisijainen GIN-indeksillä nopeutettu suodatin, ja se toimii myös sisäkkäisille objekteille ja taulukoille.
?, ?| ja ?& (avaimen olemassaolo) nopeutuvat GIN-indeksillä, mutta vain oletusarvoisen jsonb_ops-luokan kanssa, ja ne tarkistavat ylimmän tason avaimet.
jsonb_path_ops tarjoaa pienemmän ja nopeamman, vain sisältämiseen tarkoitetun indeksin.
->- ja ->>-poimintasuodattimet EIVÄT käytä tavallista GIN-indeksiä. Muotoile ne uudelleen @>-operaattorilla tai lisää B-puun lausekeindeksi.
Varmista aina EXPLAIN-komennolla, että saat Bitmap Index Scan -operaation etkä Seq Scan -operaatiota.

Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 22
Oppitunnit: 88

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”JSONB-operaattorit ja sisältökyselyt” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “JSONB-operaattorit ja sisältökyselyt”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”JSONB-operaattorit ja sisältökyselyt”?

Käyttäkää sisältö- ja polkuoperaattoreita, joita GIN-indeksit voivat todella nopeuttaa. Harjoittelet PostgreSQL:n suorituskyky ja kyselyjen optimointi-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni PostgreSQL:n suorituskyky ja kyselyjen optimointi-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 1/4.

Kuinka kauan ”JSONB-operaattorit ja sisältökyselyt”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunnilla?

Kyllä. Jokainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi