Kuinka kokonaishaun (full-text search) toiminta PostgreSQL:ssä?

Question

Accepted Answer

PostgreSQL:llä on **sisäänrakennettu kokonaishaun** — dokumenttien/tekstin haku sanoille stemmauksella, rankingilla ja kielienhavainnoinnilla, paljon tehokkaampi kuin `LIKE`. Se käyttää **`tsvector`** (käsitelty dokumentti) ja **`tsquery`** (hakukysely) -tyyppejä, GIN-indekseillä nopeuden takaamiseksi.

## Ongelma, jota LIKE ei ratkaise

```text
LIKE '%word%' → no stemming (won't match "running" for "run"), no ranking, no language
  awareness, and can't use an index well (leading wildcard) → slow and limited.
Full-text search → stems words, ignores stop words, ranks by relevance, indexable.
```

## tsvector ja tsquery

```sql
-- to_tsvector — process text into searchable lexemes (stemmed, stop-words removed)
SELECT to_tsvector('english', 'The cats are running fast');
-- → 'cat':2 'fast':5 'run':4  (stemmed: cats→cat, running→run; "the"/"are" removed)

-- to_tsquery / plainto_tsquery — parse a search query
SELECT to_tsvector('english', body) @@ to_tsquery('english', 'running')
FROM articles;   -- @@ = the match operator; matches "run" stem
```

`to_tsvector` muuntaa tekstin normalisoiduiksi **lekseemeiksi** (stemmauksella ja stop-sanojen poistolla), ja `to_tsquery` jäsentää hakutermit. `@@`-operaattori testaa vastaavuuden — joten "running" vastaa dokumentteja, jotka sisältävät "run", "runs" jne.

## Hakusarake + GIN-indeksi (käytännöllinen asetus)

```sql
-- add a generated tsvector column and index it with GIN
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- fast searches using the index
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'machine learning');
```

## Tulosten järjestäminen relevanssin mukaan

```sql
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;          -- most relevant first
```

`ts_rank` pisteyttää, kuinka hyvin kukin dokumentti vastaa hakua, mikä mahdollistaa relevanssin mukaan järjestetyt tulokset.

## Miksi sillä on merkitystä

PostgreSQL:n sisäänrakennettu kokonaishaun on arvokas ominaisuus, jonka avulla voit **toteuttaa hakutoiminnallisuuden suoraan tietokannassasi** — ilman erillistä hakumoottoria (kuten Elasticsearch) monissa käyttötapauksissa — joten sen ymmärtäminen on hyödyllistä sovelluksille, jotka tarvitsevat tekstihaun.

Pääarvo on se, että se on paljon tehokkaampi kuin `LIKE` (joka ei voi stemmaita sanoja, rankita tuloksia tai olla indeksoitu tehokkaasti): kokonaishaulla on **sanojen stemmausta** ("running" vastaa "run"-hakua), **stop-sanojen poistoa**, **kielienhavainnointi**, **relevanssin mukaan järjestämistä** ja ratkaisevasti se on **indeksoitavissa** (GIN-indekseillä nopeille hauille) — toimittaen oikeat hakuominaisuudet (sellaisia, joita käyttäjät odottavat: sanojen muunnelmien vastaaminen, järjestäminen relevanssin mukaan) suoraan Postgresissa.

Ydinalueiden ymmärtäminen — **`tsvector`** (käsitelty, stemmattu dokumenttiteksti), **`tsquery`** (jäsennetyt hakutermit), **`@@`** -vastaavuusoperaattori ja **`ts_rank`** relevanssin rankingille — sekä käytännöllinen asetus (luotu `tsvector`-sarake indeksoitu GIN-indeksillä suorituskyvyn takaamiseksi) kattaa, kuinka toteuttaa tehokas haku.

Vaikka erilliset hakumoottorit tarjoavat enemmän monimutkaisiin tarpeisiin, Postgres-kokonaishaun riittää ja on kätevä monissa sovelluksissa (välttäen erillisen hakujärjestelmän operatiivisen monimutkaisuuden ja pitäen hakutiedot yhdenmukaisia relatiivisten tietojen kanssa).

Koska tekstihaku on yleinen sovellusvaatimus ja koska Postgres tarjoaa kyvykkään sisäänrakennetun kokonaishaun (tehokkaan, indeksoitavan, stemmauksen ja rankingilla varustetun), joka usein riittää ilman erillistä hakumoottoria, sen ymmärtäminen — tsvector/tsquery-malli, indeksoidtu asetus ja ranking — on arvokas, käytännöllisesti olennainen tieto, jonka avulla voit lisätä oikean haun PostgreSQL:n sisäänrakennettujen ominaisuuksien avulla, hyödyllinen ominaisuus, joka usein jätetään huomiotta monimutkaisempien ulkoisten ratkaisujen hyväksi silloin, kun Postgres riittäisi.