PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Järjestäminen ja relevanssin säätö ts_rankilla

Painottakaa dokumenttien osioita ja säätäkää järjestämisfunktioita niin, että olennaisimmat tulokset näkyvät ensin.

Oppitunti 2/413 vaihetta

Järjestäminen ja relevanssin säätö ts_rankilla 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.

Miksi järjestyksellä on merkitystä

@@-operaatiolla tehty kokotekstikysely kertoo vain, osuko dokumentti kyselyyn, ei sitä, kuinka hyvin. Jotta osuvimmat rivit voidaan näyttää ensin, tarvitaan järjestämiseen tarkoitettu pisteytysfunktio.

PostgreSQL tarjoaa kaksi vaihtoehtoa: ts_rank (esiintymistiheyteen perustuva) ja ts_rank_cd (peittotiheys, huomioi hakutermien läheisyyden). Molemmat palauttavat real-tyyppisen pistemäärän, jonka mukaan tulokset voidaan järjestää.

Osumien etsiminen on binääristä, nopeaa ja indeksin tukemaa.
Järjestäminen on erillinen, kalliimpi laskenta, joka tehdään osumille.

SELECT title,
       ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'index & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC
LIMIT 10;

Miten ts_rank pisteyttää

ts_rank perustaa pisteensä termin esiintymistiheyteen: siihen, kuinka usein kyselyn lekseemit esiintyvät dokumentissa, sekä niille annettuihin painoihin. Kyselytermin useammat esiintymät tarkoittavat yleensä suurempaa pistemäärää.

Oleellista on, että järjestys lasketaan tsvector-arvosta, joka tallentaa lekseemien sijainnit. Dokumentti, jossa termi esiintyy viisi kertaa, sijoittuu muiden tekijöiden ollessa samat ennen dokumenttia, jossa se esiintyy kerran.

ts_rank ei huomioi termien keskinäistä läheisyyttä.
ts_rank_cd suosii dokumentteja, joissa kyselytermit esiintyvät lähekkäin.

Painotunnisteet A, B, C ja D

Jokaisella tsvector-arvon lekseemisijainnilla voi olla painotunniste: A, B, C tai D. Merkitkää eri dokumenttiosiot setweight()-funktiolla, jotta otsikkoon osuva termi vaikuttaa enemmän kuin leipätekstiin osuva.

D on oletus (pienin paino). Yleinen käytäntö on: A = otsikko, B = tiivistelmä, C = leipäteksti, D = kommentit tai metadata.

Muodostakaa painotettu vektori yhdistämällä setweight()-kutsut operaattorilla ||.

SELECT setweight(to_tsvector('english', 'PostgreSQL Indexing'), 'A') ||
       setweight(to_tsvector('english', 'A guide to fast queries'), 'B') ||
       setweight(to_tsvector('english', 'Detailed body text about GIN indexes'), 'C');

Painotetun tsvector-arvon tallentaminen

Suorituskyvyn vuoksi laskekaa painotettu tsvector-arvo etukäteen generoituun sarakkeeseen ja indeksoikaa se GIN-indeksillä. Tällöin sekä järjestäminen että osumien etsiminen käyttävät samaa painotettua vektoria, eikä tekstiä tarvitse pilkkoa uudelleen kyselyn aikana.

Generoitu sarake lasketaan automaattisesti uudelleen, kun title- tai body-sarake muuttuu, joten se pysyy yhdenmukaisena.

ALTER TABLE articles
  ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'C')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vec);

Painojen säätäminen taulukolla

ts_rank hyväksyy valinnaisena ensimmäisenä argumenttina neljän alkion float4[]-taulukon, joka sisältää tunnisteiden kertoimet järjestyksessä {D, C, B, A}. Huomioikaa järjestys: D tulee ensin ja A viimeisenä.

Oletustaulukko on {0.1, 0.2, 0.4, 1.0}. Suurentakaa A-kerrointa, jos haluatte otsikko-osumien vaikuttavan vielä enemmän, tai tasoittakaa taulukkoa osioiden painotuksen vaikutuksen pienentämiseksi.

SELECT title,
       ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vec, query) AS rank
FROM articles, to_tsquery('english', 'gin & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 10;

Pituuden normalisointi

Oletusarvoisesti ts_rank ei normalisoi dokumentin pituuden perusteella, joten pitkät dokumentit voivat saada suurempia pisteitä vain pituutensa vuoksi. Valinnainen viimeinen kokonaislukuargumentti ohjaa normalisointia yhteenlaskettavilla bittilipuilla.

0 — ohita pituus (oletus)
1 — jaa järjestysluvulla 1 + log(pituus)
2 — jaa pituudella
4 — jaa harmonisella keskimääräisellä etäisyydellä (vain cd)
8 — jaa yksilöllisten sanojen määrällä
16 — jaa luvulla 1 + log(yksilölliset sanat)
32 — jaa itsellään + 1:llä (muuntaa järjestysluvun välille [0,1))

Normalisoinnin käyttäminen

Lippu 1 on yleisin valinta: se heikentää pitkien dokumenttien pistemäärää lievästi logaritmin avulla, joten 2 000 sanan artikkeli ei jyrää kohdennettua 200 sanan artikkelia. Yhdistäkää liput laskemalla ne yhteen, esimerkiksi 1|32 = 33, jolloin pistemäärä muunnetaan lisäksi välille [0,1).

Välille [0,1) normalisoitu pistemäärä on kätevä, kun kokotekstin osuvuus halutaan yhdistää muihin tekijöihin, kuten tuoreuteen tai suosioon.

SELECT title,
       ts_rank(search_vec, query, 1) AS rank_lognorm,
       ts_rank(search_vec, query, 33) AS rank_0_to_1
FROM articles, to_tsquery('english', 'query & optimization') query
WHERE search_vec @@ query
ORDER BY rank_lognorm DESC
LIMIT 10;

ts_rank_cd lausekkeiden läheisyyden arviointiin

ts_rank_cd toteuttaa peittotiheyteen perustuvan järjestämisen: se suosii dokumentteja, joissa kyselyn lekseemit esiintyvät lähekkäin. Tämä edellyttää sijaintitietoja, joten se toimii vain tsvector-arvolla, jossa sijainnit ovat edelleen mukana (niitä ei ole poistettu).

Kyselyissä, kuten "query planner", joissa vierekkäisyys kertoo osuvuudesta, ts_rank_cd on yleensä tavallista ts_rank-funktiota parempi. Se hyväksyy samat painotaulukko- ja normalisointiargumentit.

SELECT title,
       ts_rank_cd(search_vec, query, 1) AS cd_rank
FROM articles,
     phraseto_tsquery('english', 'query planner') query
WHERE search_vec @@ query
ORDER BY cd_rank DESC
LIMIT 10;

Kaksivaiheinen suorituskykymalli

Järjestäminen on suoritinta kuormittavaa ja suoritetaan jokaiselle osumariville, joten sitä ei pidä koskaan tehdä miljoonille riveille. Tehokas malli on kaksivaiheinen: suodattakaa ensin edullisesti GIN-indeksillä ja järjestäkää sitten vain jäljelle jääneet rivit.

Siirtäkää @@-osuma (indeksin tukemana) alikyselyyn tai CTE:hen, tarvittaessa karkean LIMIT-rajoituksen kanssa, ja laskekaa ts_rank vasta pienelle ehdokasjoukolle.

Indeksi supistaa miljoonat rivit tuhansiksi.
ts_rank järjestää sen jälkeen vain tuhansia rivejä.

WITH candidates AS (
  SELECT id, title, search_vec
  FROM articles
  WHERE search_vec @@ to_tsquery('english', 'index & tuning')
  LIMIT 500
)
SELECT id, title,
       ts_rank(search_vec, to_tsquery('english', 'index & tuning')) AS rank
FROM candidates
ORDER BY rank DESC
LIMIT 10;

Järjestystä ei voi indeksoida

Yleinen väärinkäsitys on, että GIN-indeksi voisi toteuttaa ORDER BY ts_rank(...) -lausekkeen. Se ei voi. GIN-indeksit nopeuttavat @@-jäsenyystestiä, mutta ts_rank on black box -funktio, jonka arvoa ei tallenneta indeksiin. Siksi PostgreSQL:n on laskettava arvo ja järjestettävä tulokset sen jälkeen.

Jos järjestäminen muodostaa pullonkaulan, vaihtoehtoja ovat staattisen laatupisteen esilaskeminen omaan sarakkeeseensa, RUM-indeksien käyttäminen (laajennus, joka voi palauttaa rivit järjestyspisteiden mukaisessa järjestyksessä) tai ehdokasjoukon rajaaminen ensin.

Järjestyksen yhdistäminen liiketoimintasignaaleihin

Pelkän tekstin järjestys vastaa harvoin tuotteen käyttäjien intuitiota. Yhdistäkää normalisoitu tekstipisteytys esimerkiksi tuoreus- ja suosiomittareihin lopullisen järjestyksen laskemiseksi. Koska lippu 32 muuntaa tekstipisteytyksen välille [0,1), se on helppo yhdistää muihin normalisoituihin tekijöihin.

Pitäkää @@-suodatus indeksin tukemana. Yhdistelmän laskenta tehdään vain osuville ehdokasriveille.

SELECT id, title,
       ts_rank(search_vec, query, 32) AS text_score,
       ts_rank(search_vec, query, 32) * 0.7
         + (1.0 / (1 + extract(epoch FROM now() - created_at) / 86400)) * 0.3
         AS final_score
FROM articles, to_tsquery('english', 'postgres & performance') query
WHERE search_vec @@ query
ORDER BY final_score DESC
LIMIT 10;

Pikatarkistus

Järjestätte hakutuloksia viiden miljoonan rivin taulusta, ja kysely on hidas. EXPLAIN näyttää GIN-indeksin Bitmap Index Scan -operaation, jota seuraa ts_rank(...) -lausekkeen mukainen Sort. Mikä on tehokkain korjaus?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Opitte säätämään kokotekstin osuvuutta PostgreSQL:ssä:

ts_rank pisteyttää termin esiintymistiheyden perusteella; ts_rank_cd suosii lähekkäisiä termejä (edellyttää sijaintitietoja).
Merkitkää osiot setweight()-funktiolla tunnisteilla A/B/C/D ja tallentakaa painotettu vektori GIN-indeksoituun generoituun sarakkeeseen.
Painotaulukolla {D, C, B, A} (oletus {0.1,0.2,0.4,1.0}) säädetään osioiden vaikutusta.
Normalisointilippu ohjaa pituuden aiheuttamaa rangaistusta; 1 käyttää logaritmista rangaistusta ja 32 muuntaa arvon välille [0,1) yhdistämistä varten.
Järjestystä ei voi indeksoida: suodattakaa aina ensin @@-operaatiolla ja järjestäkää vasta pieni ehdokasjoukko.

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 ”Järjestäminen ja relevanssin säätö ts_rankilla” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Järjestäminen ja relevanssin säätö ts_rankilla”. 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 ”Järjestäminen ja relevanssin säätö ts_rankilla”?

Painottakaa dokumenttien osioita ja säätäkää järjestämisfunktioita niin, että olennaisimmat tulokset näkyvät ensin. 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 ”Järjestäminen ja relevanssin säätö ts_rankilla”-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

tsvector-sarakkeiden ja GIN-indeksien suunnittelu
Järjestäminen ja relevanssin säätö ts_rankilla
Sumea haku pg_trgm-similaarisuudella
Suodattimien yhdistäminen hakuehtoihin

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi