JSONB:n GIN- ja lausekeindeksit
Valitkaa jsonb_path_ops-GIN-indeksien ja kohdennettujen lausekeindeksien välillä kyselyrakenteidenne perusteella.
JSONB:n GIN- ja lausekeindeksit on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 2/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.
Kaksi tapaa indeksoida JSONB
Kun tallennat dataa jsonb-sarakkeeseen, indeksoimaton kysely pakottaa PostgreSQL:n lukemaan ja jäsentämään jokaisen rivin. Tämän korjaamiseen on kaksi hyvin erilaista työkalua:
- GIN-indeksi — koko dokumentin kattava yleiskäyttöinen käänteisindeksi, joka sopii joustavaan sisältämis- ja avain-arvohakuun.
- Lausekeindeksi (B-puu) — yhteen poimittuun skalaarikenttään kohdistettu indeksi, joka sopii tiettyyn ennalta tunnettuun kyselymuotoon.
Tässä oppitunnissa opit valitsemaan oikean vaihtoehdon omien kyselymalliesi perusteella.
Esimerkkitaulu
Kuvittele events-taulu, jonka jokainen rivi sisältää joustavan JSON-sisällön. Indeksoimme sen data-sarakkeen.
Huomaa, että sisältö yhdistää muutamia yleisiä avaimia (type, user_id) mielivaltaisiin lisäkenttiin.
CREATE TABLE events (
id bigserial PRIMARY KEY,
data jsonb NOT NULL
);
INSERT INTO events (data) VALUES
('{"type": "login", "user_id": 42, "ip": "10.0.0.1"}'),
('{"type": "logout", "user_id": 42}'),
('{"type": "login", "user_id": 99, "mfa": true}');Oletusarvoinen GIN: jsonb_ops
Tavallinen GIN-indeksi käyttää oletusarvoista jsonb_ops-operaattoriluokkaa. Se indeksoi jokaisen avaimen JA jokaisen arvon erillisenä merkintänä.
Tämä tukee laajinta operaattorijoukkoa: sisältämistä @> sekä avaimen olemassaoloa ?, ?| ja ?&.
Haittapuoli on se, että indeksi vie enemmän levytilaa ja sen rakentaminen ja päivittäminen on hitaampaa, koska se tallentaa riviä kohden paljon enemmän merkintöjä.
CREATE INDEX idx_events_data_gin
ON events USING gin (data);
-- Supports key existence AND containment:
-- WHERE data ? 'mfa'
-- WHERE data @> '{"type":"login"}'Keveämpi GIN: jsonb_path_ops
Jos käytät aina vain sisältämisoperaattoria @> (sekä JSONPath-operaattoreita @? / @@), suosi jsonb_path_ops-operaattoriluokkaa.
- Se hajauttaa kokonaiset avain→arvo-polut yksittäisiksi merkinnöiksi.
- Tuloksena on huomattavasti pienempi indeksi ja nopeammat sisältämishaut.
- Haittapuoli: se EI tue avaimen olemassaolo -operaattoreita
?,?|ja?&.
CREATE INDEX idx_events_data_pathops
ON events USING gin (data jsonb_path_ops);
-- Great for:
SELECT id FROM events
WHERE data @> '{"type": "login"}';Miten sisältäminen käyttää GIN-indeksiä
@>-operaattori kysyy, "sisältääkö vasemmanpuoleinen dokumentti oikeanpuoleisen?" Molemmat GIN-operaattoriluokat nopeuttavat sitä.
Sisältäminen on rakenteellista: se täsmäyttää sisäkkäisiä avaimia ja arvoja, ei vain ylimmän tason arvoja. Siksi yksi GIN-indeksi voi palvella monia erilaisia suodatinyhdistelmiä.
-- Match by one key:
SELECT * FROM events WHERE data @> '{"user_id": 42}';
-- Match by two keys at once (same index):
SELECT * FROM events
WHERE data @> '{"type": "login", "user_id": 42}';
-- Match a nested shape:
SELECT * FROM events WHERE data @> '{"flags": {"beta": true}}';Milloin GIN ei riitä: alueet ja järjestäminen
GIN on suunniteltu yhtäsuuruustyyppiseen sisältämiseen. Se EI auta seuraavissa tilanteissa:
- Aluevertailut poimitulle arvolle (
>,<,BETWEEN). - Järjestäminen JSON-kentän perusteella (
ORDER BY ... LIMIT). - Etuliite- tai merkkijonokuviohaku tekstiarvolle.
Näihin kyselymuotoihin tarvitset B-puun, mikä JSONB:n yhteydessä tarkoittaa lausekeindeksiä.
-- GIN can't accelerate this range filter on an inner number:
SELECT * FROM events
WHERE (data ->> 'user_id')::int > 50
ORDER BY (data ->> 'user_id')::int
LIMIT 10;Lausekeindeksin luominen
Lausekeindeksi tallentaa lausekkeen tuloksen, ei raakaa saraketta. Poimit JSON:stä yhden skalaarin ja indeksoit sen tavallisena B-puuna.
Tässä on tärkeää kaksi operaattoria:
->palauttaa arvon muodossajsonb.->>palauttaa arvon muodossatext— yleensä juuri tämän tyypin muunnat ja indeksoit.
-- B-tree on user_id extracted as an integer:
CREATE INDEX idx_events_user_id
ON events (((data ->> 'user_id')::int));
-- Now ranges, sorts and equality all use it:
SELECT * FROM events
WHERE (data ->> 'user_id')::int BETWEEN 40 AND 99
ORDER BY (data ->> 'user_id')::int;Indeksilausekkeen on vastattava täsmälleen
Suunnittelija käyttää lausekeindeksiä vain, kun kyselyn lauseke vastaa indeksoitua lauseketta merkki merkiltä, myös tyyppimuunnos mukaan lukien.
Jos indeksoit (data ->> 'user_id')::int, mutta kyselyssä käytät lauseketta (data ->> 'user_id') tavallisena tekstinä, indeksiä ei käytetä.
Pidä poiminta ja tyyppimuunnos kaikkialla täsmälleen samoina.
-- Indexed expression:
-- ((data ->> 'user_id')::int)
-- USES the index:
WHERE (data ->> 'user_id')::int = 42
-- IGNORES the index (text vs int mismatch):
WHERE (data ->> 'user_id') = '42'EXPLAIN-tuloksen lukeminen käytön varmistamiseksi
Älä koskaan arvaa, mikä indeksi valitaan — kysy suunnittelijalta. Käytä komentoa EXPLAIN (ANALYZE, BUFFERS) ja tarkista solmun tyyppi:
- Bitmap Heap Scan +
Bitmap Index Scan on ...gin→ GIN-indeksisi käsittelee sisältöhakua. - Index Scan / Index Only Scan lausekeindeksissä → B-tree-indeksisi käsittelee aluehaun tai lajittelun.
- Seq Scan → mikään ei täsmännyt; tarkista lauseke tai operaattori uudelleen.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE data @> '{"type": "login"}';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE (data ->> 'user_id')::int = 42;Osittaiset lausekeindeksit
Jos kyselyt kohdistuvat aina vain tiettyyn rivien osajoukkoon, lisää indeksiin WHERE-ehto. Osittainen lausekeindeksi on pienempi ja edullisempi ylläpitää, koska se tallentaa vain rivit, joita todella haet.
Tässä indeksoimme user_id-kentän vain kirjautumistapahtumille — tämä sopii täydellisesti, kun sitä tarvitaan vain tämänkaltaisissa kyselyissä.
CREATE INDEX idx_events_login_user
ON events (((data ->> 'user_id')::int))
WHERE data @> '{"type": "login"}';Valinta: nopea päätösopas
Valitse indeksi kyselyjesi rakenteen, ei tottumuksen perusteella:
- Joustavat suodatukset useilla eri avaimilla tai avaimen olemassaolon tarkistus (
?) → GIN jsonb_ops. - Vain sisältöhaku
@>/ JSONPath, ja haluat kevyen ja nopean indeksin → GIN jsonb_path_ops. - Yksi tunnettu kenttä, jolle tehdään aluehakuja, lajittelua tai skalaarista yhtäsuuruusvertailua → B-tree-lausekeindeksi.
- Kyseistä kenttää haetaan vain pienestä rivien osajoukosta → osittainen lausekeindeksi.
On tavallista ja oikein säilyttää samassa sarakkeessa sekä GIN-indeksi että yksi tai kaksi lausekeindeksiä.
Pikatarkistus
Testaa, ymmärrätkö GIN-indeksin ja lausekeindeksin välisen valinnan.
Kertaus
Opit valitsemaan JSONB-indeksit kyselyn rakenteen perusteella:
- GIN jsonb_ops — laajin operaattorituki, mukaan lukien avaimen olemassaolon tarkistus
?; suurin. - GIN jsonb_path_ops — kevyempi ja nopeampi, tukee vain sisältöhakua
@>ja JSONPathia. - B-tree-lausekeindeksi — yhdelle poimitulle ja tyyppimuunnetulle skalaarille aluehakuja, lajittelua ja yhtäsuuruusvertailuja varten; kyselyn lausekkeen on vastattava indeksin lauseketta täsmälleen.
- Osittainen lausekeindeksi — sama periaate, mutta rajattuna rivien osajoukkoon pienemmän koon saavuttamiseksi.
Varmista valinta aina komennolla EXPLAIN (ANALYZE, BUFFERS), äläkä epäröi käyttää rinnakkain GIN-indeksiä ja yhtä tai kahta lausekeindeksiä.
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
Usein kysytyt kysymykset
Onko oppitunti ”JSONB:n GIN- ja lausekeindeksit” ilmainen?
Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “JSONB:n GIN- ja lausekeindeksit”. 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:n GIN- ja lausekeindeksit”?
Valitkaa jsonb_path_ops-GIN-indeksien ja kohdennettujen lausekeindeksien välillä kyselyrakenteidenne perusteella. 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 2/4.
Kuinka kauan ”JSONB:n GIN- ja lausekeindeksit”-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
- JSONB-operaattorit ja sisältökyselyt
- JSONB:n GIN- ja lausekeindeksit
- JSONB:n kyselyt JSONPathilla
- Milloin JSONB kannattaa normalisoida