ugrás a tartalomhoz

PostgreSQL + Full Text Search

dave · 2012. Júl. 10. (K), 14.53
Sziasztok!

Egy ideje már játszadozok a PostgreSQL-lel, most jutottam el odáig, hogy full text keresést is használnék. Egyelőre csak tapogatózom, az alábbi kérdéseim merültek fel:

1. Melyik a legjobb megoldás a megvalósításra (sebesség, egyszerűség tekintetében)? A beépített FTS, vagy a "külső" megoldások? Olvasgattam a Solr-t és a Sphinxről, nekem jobban tetszik, hogy "házon belül" marad a megoldás, nem kell külön szoftvert telepítenem a szerverre. Milyen előnyei lehetnek az utóbbiaknak?
2. Magyar nyelv esetén mennyire működőképes a dolog? Értem ez alatt a stop szavakat és a szótövezést. Kereséskor a szótövezés automatikusan működik vagy úgy kell szerkeszteni az SQL lekérdezést? Mi a helyzet az elválasztással?
3. A sorba rendezés része nem teljesen tiszta, arról valamit tudnátok mondani pár szóban? Ha valami precedenciát szeretnék, azt ki lehet kényszeríteni. Itt ilyenre gondolok, hogy van egy topic táblám title és body mezővel, akkor a title-ben lévő találatok előbb helyezkedjenek el, mint a body.

Köszi,
dave
‹ Változó mennyiségű és típusú adatok (oszlopok?!) db tervezés: sok feltétel, sok adat, lapozás, szűrés és a helyes módszerek ›
 ■
1

"use-case"

bbalint2 · 2012. Júl. 16. (H), 02.16
Szervusz!
  1. én csak a Postgres saját rendszerét alkalmaztam - a többiről sejtelmem sincs, de ha azokat kiterjesztésként (extension) kell telepíteni, akkor annyira rosszak nem lehetnek...
  2. a szótövezést megoldja magától az FTS; szótagolás alatt nem tudom mit értesz, de próbaképpen a "képvisel", illetve "képviselt" lekérdezésekre is kiadott olyan találatokat, melyekben szerepel a képviselet szó; ha a "hotelek"-re keresek, akkor pedig az összes "hotel" is szerepel az eredmények között
  3. SQL-ben rendezni az ORDER BY záradékkal lehet; a MySQL-lel ellentétben a Postgres nem rendez automatikusan a FULLTEXT-es keresés relevanciájára - arra külön "kérni" kell;
    ilyen relevancia adatot a ts_rank_cd() függvény szolgáltat
Alább egy kis tapasztalat/összefoglaló, hogy én mire-hogyan használtam fel...

Előszó: ezt az egészet még régebben csináltam, 8.3, 8.4 verzióval; azóta remélhetőleg fejlődött a technika/technológia. Továbbá bizonyos miértek a feledés homályába vesznek... :-/

A rendszerben előtte sima toldozott-foltozott LIKE kifejezésekkel volt megoldva a keresés, de a félreírt adatok, ronda helyesírás s (előbbiek által is) a rossz keresési eredmények miatt szükségessé vált egy okosabb megoldás.
Nem utolsó sorban ahogy' növekedett az adatbázis, úgy vált egyre használhatalanabbá a keresés funkció.

Részemről csak a hivatalos dokumentáció alapján dolgoztam, átvettem annak ajánlásait:

FULLTEXT kereséshez elég csak a to_tsvector() és to_tsquery() függvényeket kombinálni a @@ operátorral.
A függvények első paramétere a FULLTEXT keresés nyelve, második a "kezelni" kívánt szöveg (TEXT):

SELECT * FROM cegek
WHERE to_tsvector ('hungarian', nev) @@ to_tsquery ('hungarian', 'hotelek');
Érdemes létrehozni egy külön mezőt a tsvector tárolására, illetve azt indexelni.

 ALTER TABLE cegek ADD COLUMN nev_ft tsvector;
 CREATE INDEX cegek_nev_ft_index ON cegek USING GIN (nev_ft);
(szerver újraindítás, pg_dump esetén így nem kell újraszámolni az indexet)

Ezen mező automatikus frissítését érdemes TRIGGER-rel megoldani:

CREATE FUNCTION nev_ft_trigger_func() RETURNS trigger
LANGUAGE plpgsql
AS $$
 BEGIN
  new.nev_ft := to_tsvector ('hungarian', new.nev);
 RETURN new;
 END
$$;

CREATE TRIGGER nev_ft_trigger BEFORE INSERT OR UPDATE ON cegek
FOR EACH ROW EXECUTE PROCEDURE nev_ft_trigger_func();
És ha már indexelve van a dolog, akkor változik a SELECT lekérdezés is:

SELECT * FROM cegek WHERE nev_ft @@ to_tsquery ('hungarian', 'hotelek');
Relevancia szerinti rendezéshez a ts_rank(), illetve ts_rank_cd() pontozóbírókat hívhatjuk segítségül:

SELECT * FROM cegek WHERE nev_ft @@ to_tsquery ('hungarian', 'hotelek')
ORDER BY ts_rank_cd (nev_ft, to_tsquery ('hungarian', 'hotelek'));
No, ésakkor egy-két tapasztalat, avagy hogyan lett az egy-egy szem függvényhívásokból kulimász:
  • a magyar szótövezés nem ismeri sem az angol, sem más nyelvek többesszámát:
    ez úgy jött elő, hogy a "hotels" kifejezésre tényleg csak a "hotels" szót tartalmazó találatok jelentek meg. megoldás: a to_tsquery() és to_tsvector() függvényhívásokhoz hozzáfűzni (||) az angol szabályok szerinti eredményt, lekérdezést is:
    
SELECT to_tsvector('hungarian', 'hotels');  -- 'hotels':1
SELECT to_tsvector('english', 'hotels');    -- 'hotel':1
SELECT to_tsvector('hungarian', 'hotels')
    || to_tsvector('english', 'hotels'); -- 'hotel':2 'hotels':1
    ennek hozadéka, hogy a magyar szabályok alapján megmarad a hotels szó, míg az angol leszótövezi s így mindkét szó letárolásra kerül,
    és ha a "hotels"-re keresésnél a hotels-t tartalmazó találatok jobb pontszámot kapnak
  • az előbbi pont "kártékony" hatása, hogy a ts_rank()/ts_rank_cd()-nél is meg kell ismételni az összefűzést - illetve nem szabad elfelejteni: egy ideig így hibás volt a program és az eredmények között szerepelt a keresett dolog, csak a ts_rank_cd() pontozás mássága miatt az eredménylista végére került :-/
  • rendezéshez én továbbra is használom, illetve ajánlom a
    LOWER (nev) BETWEEN 'keresett' AND 'keresettz' DESC
    kitételt, mivel nem mindig kap(ott) maximális pontszámot a keresett név. ennek gyorsítására kapott egy saját indexet is a mező:
    
CREATE INDEX cegek_nev_index ON cegek (LOWER (nev));
  • mivel nálunk probléma [volt], hogy az adatok felvitelekor nem mindeki ír helyesen és/vagy elrontja az ékezetes betűket, így a FULLTEXT mező generálásához még hozzátettem a nev mező ékezetek nélküli változatát is:
    
 ... || to_tsvector (TRANSLATE (new.nev, 'áäéëíóöőúüűÁÄÉËÍÓÖŐÚÜŰ', 'aaeeiooouuuAAEEIOOOUUU'))
  • aztán volt-lett-jött egy olyan probléma, hogy a nem alfanumerikus karaktereket (kötőjel, vessző stb.!) tartalmazó rekordok nem jöttek elő keresésre, illetve "aszem" néha félreértette azokat a to_tsvector(). erre a válaszom az alábbi reguláris kifejezés lett:
    
REGEXP_REPLACE (new.nev, '[^[:alnum:]áéíóöőúüűÁÉÍÓÖŐÚÜŰ]+', ' ', 'gi')
  • alapértelmezésben a @@ keresés nem keres a paraméterben megadott kezdetű szavakra:
    a WHERE nev_ft @@ to_tsquery('hungarian', 'hotelek') lekérdezés nem fogja kiadni a 'hoteljofajta' nevűt. megoldás:
    a keresési lekérdezés szavainak végére "csak" oda kell biggyeszteni a :* szöveget és már működik is:
    
SELECT * FROM cegek WHERE
nev_ft @@ to_tsquery ('hungarian', 'hotelek:*');
így, a fenti pontok tapasztalatai után a következőképp' torzult az eredeti TRIGGER függvény:

CREATE FUNCTION nev_ft_trigger_func() RETURNS trigger
LANGUAGE plpgsql
AS $$
 BEGIN
  new.nev_ft     :=
  to_tsvector( 'hungarian',
                REGEXP_REPLACE (new.nev, '[^[:alnum:]áéíóöőúüűÁÉÍÓÖŐÚÜŰ]+', ' ', 'gi'))
||to_tsvector( 'english',
                REGEXP_REPLACE (new.nev, '[^[:alnum:]áéíóöőúüűÁÉÍÓÖŐÚÜŰ]+', ' ', 'gi'))
  
||to_tsvector( 'hungarian',
                REGEXP_REPLACE (TRANSLATE (new.nev, 'áäéëíóöőúüűÁÄÉËÍÓÖŐÚÜŰ', 'aaeeiooouuuAAEEIOOOUUU'), '[^[:alnum:]áéíóöőúüűÁÉÍÓÖŐÚÜŰ]+', ' ', 'gi'))
||to_tsvector( 'english',
                REGEXP_REPLACE (TRANSLATE (new.nev, 'áäéëíóöőúüűÁÄÉËÍÓÖŐÚÜŰ', 'aaeeiooouuuAAEEIOOOUUU'), '[^[:alnum:]áéíóöőúüűÁÉÍÓÖŐÚÜŰ]+', ' ', 'gi'))
 ;
  
  RETURN new;
 END
$$;
(ez nem az eredeti, "éles" változat - a valódi a nev mezőn kívül még két másik mezőt is letárol ezen függvényhívások kombinációjával...)
2

Köszi a kimerítő választ. Az

dave · 2012. Júl. 16. (H), 12.56
Köszi a kimerítő választ.

Az elválasztás alatt kb. azt értem, ha a mezőben a kép-viselt szó szerepel, akkor hogy lehetne megoldani, hogy a képvisel szóra találat legyen

Ami furcsa volt még, hogy pl. a
SELECT to_tsvector ('hungarian', 'fordulói') @@ to_tsquery ('hungarian', 'fordulója');
TRUE-t ad vissza, viszont a 
SELECT to_tsvector ('hungarian', 'délelőtti') @@ to_tsquery ('hungarian', 'délelőtt');
FALSE-t. Erre valami ötlet?
3

intelligencs rendszerek...

bbalint2 · 2012. Júl. 16. (H), 17.12
...szerintem sikerül kihámoznia a dél-elő szóösszetételt, illetve annak múltidejű alakját... okós!

valószínűleg a szótövezős/szótár rész túl okos és tartalmazza ezt szóösszetételt. szerintem biztos még van egypár ilyen furaság :-/

egyik ötlet, hogy kijavítod a szótárt és/vagy szótövező algoritmust, vagy használod a FULLTEXT angol részét is:

SELECT (to_tsvector('english', 'délelőtti')
     || to_tsvector('hungarian','délelőtti'))
    @@ (to_tsquery('hungarian', 'délelőtt')
     || to_tsquery('english', 'délelőtt')); -- TRUE
hibakeresésre elvileg ott a ts_debug() függvény - kiír ilyen-olyan infókat, hogy mit gondol a beírt kifejezésről:

SELECT ts_debug('hungarian', 'délelőtt');
 -- (word,"Word, all letters",délelőtt,{hungarian_stem},hungarian_stem,{délelőt})
illetve a to_tsvector()-ok és to_tsquery()-k SELECT-es nézegetése is hasznos lehet:

SELECT to_tsvector('hungarian', 'délelőtti');
SELECT to_tsquery('hungarian', 'délelőtti');
SELECT to_tsvector('english', 'délelőtti');
SELECT to_tsquery('english', 'délelőtti');