[Olug-list] Tips: fritekstsøking i PostgreSQL

[Denis Braekhus]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hei og god sommer !

Ser MySQL får litt publisitet rundt sine FULLTEXT indekseringsløsninger.

Tenkte dette er et fint sted å spre det glade budskap om en rimelig super 
løsning for mye av det samme (og litt til) i PostgreSQL. 

ABC Startsiden har i litt over ett år brukt PostgreSQL med den russiske 
søkeløsningen OpenFTS [1] til søk i våre kategorier og pekere.
Nå har den samme duoen bak OpenFTS kommet noen skritt videre på sin nye 
versjon av Tsearch (Tsearch V2), en modul som vil bli med i /contrib delen av 
PostgreSQL 7.4. For oss som bruker PostgreSQL 7.3.x idag er det fint mulig å 
installere og bruke Tsearch V2 allerede nå.

Siden jeg liker å teste slike ting gikk jeg igang for å gjøre en ny database 
jeg hadde fritekst-søkbar, via Tsearch V2 [2].

Kort step by step beskrivelse (egentlig bare en oppsummering av 
introdokumentasjonen på Tsearch sidene [3]) :

1. Hent ned tarball : 
wget [4]

2. Pakk opp tarballen : 
tar xfz tsearch-v2.tar.gz

3. Hvis ikke du har kompilert PostgreSQL fra kildekode selv, hent ned og pakk 
ut relevante pakker. (For meg ble det Debian sin postgresql unstable 
kildepakke.) :
apt-get source postgresql 

3. Flytt katalogen til /contrib treet i PostgreSQL kildetreet :
mv tsearch-2.1.6 /..../postgresql-7.3.3/contrib

4. Gå til tsearch katalogen : 
cd /..../postgresql-7.3.3/contrib/tsearch-2.1.6/

5. Bygg og installer tsearch.so :
make
make install


I instruksene under tar jeg utgangspunkt i følgende oppsett :
En database som heter : mindatabase
Bruker : minbruker
Tabell : mintabell
Tekstfelt vi skal fritekstindeksere : mittekstfelt

7A. Legg til Tsearch funksjoner og datatyper i relevant database (obs: endel 
postgresql reduserer, med god grunn, tilgang til å opprette slikt. Bli derfor 
superbruker midlertidig hvis du må det. Se 7B) :
psql -U minbruker mindatabase
\i /..../postgresql-7.3.3/contrib/tsearch-2.1.6/tsearch.sql

7B. Alternativt ved begrenset tilgang :
psql -U postgres mindatabase
\i /..../postgresql-7.3.3/contrib/tsearch-2.1.6/tsearch.sql
ALTER TABLE pg_ts_cfg OWNER TO minbruker;
ALTER TABLE pg_ts_cfgmap OWNER TO minbruker;
ALTER TABLE pg_ts_dict OWNER TO minbruker;
ALTER TABLE pg_ts_parser OWNER TO minbruker;
\c mindatabase minbruker

8. Legg til et FTIindex felt i den aktuelle tabellen :
ALTER TABLE mintabell ADD idxFTI tsvector;

9. Legg inn fritekstsøkeordene i det nye feltet :
UPDATE tblMessages SET idxFTI=to_tsvector('default', mittekstfelt);

10. Støvsug databasen :
VACUUM FULL ANALYZE;

11. Nå må vi lage en index på fritekstfeltet :
CREATE INDEX idxFTI_idx ON mintabell USING gist(idxFTI);

12. Støvsug igjen :
VACUUM FULL ANALYZE;

13. Legg til en Trigger så oppdateringer og nye poster blir fritekstindeksert:
CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON mintabell
	FOR EACH ROW EXECUTE PROCEDURE tsearch(idxFTI, mittekstfelt);

14. Nå har du en fritekstsøkbar database (riktignok bare på ett felt, se den 
originale guiden for mer info om hvordan å indeksere flere felter av 
gangen!), så nå kan vi søke :
SELECT id, mittekstfelt FROM mintabell 
	WHERE idxfti @@ to_tsquery('default', 'Test');

(Eksempelet over søker på ordet "test".)

Eller søke på flere ord (AND) :
SELECT id, mittekstfelt FROM mintabell 
	WHERE idxfti @@ to_tsquery('default', 'Test & Tast');

Eller søke på flere ord (OR) :
SELECT id, mittekstfelt FROM mintabell 
	WHERE idxfti @@ to_tsquery('default', 'Test | Tast');


Hvis noen i det hele tatt hadde noe glede av dette så kan jeg snart fortelle 
hvordan man kobler Tsearch V2 opp mot en norsk ordliste, og hvordan man 
manipulerer Tsearch konfigurasjonen. Det er på disse områdene Tsearch er 
overlegen MySQLs FREETEXT.. 

:-)



[1] http://openfts.sourceforge.net
[2] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
[3] 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intr=
o.html
[4] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch-v2.tar.gz

- -- 
Denis Brækhus - ABC Startsiden AS
http://www.startsiden.no

"`The best way to get a drink out of a Vogon is to stick 
your finger down his throat...'" 
(Hitchhikers Guide To The Galaxy)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/AWKXvsCA6eRGOOARAv2FAJ94dhBbIf95Fue9CF6qyrTzgJLXDQCgs/Em
APhcGXm5lUzJSMMZ4bGK2G4=
=LbuF
-----END PGP SIGNATURE-----