English disclaimer: this post will be in Polish only, as it’s use for other nationalities is somehow limited 
jakiś czas temu pisałem jak skonfigurować polskiego tsearcha pod postgresem 8.2.
od tamtej pory trochę się zmieniło, pojawił się 8.3, w którym tsearch jest wbudowany w bazowego postgresa, więc instalacja jest prostsza.
zakładam, że postgres jest skonfigurowany tak by działać w utf-8. jeśli jest to iso-8859-2, to trzeba po prostu pominąć kroki odpowiedzialne za zmianę kodowania plików.
zaczynamy od wizyty na znanej z poprzedniego postu stronie: http://kurnik.pl/dictionary/.
ona, przenosi nas na sjp.pl, ale to jest mało istotne. znajdujemy najnowszy słownik w formacie ispella, i pobieramy jego źródła. dziś plik nazywa się sjp-ispell-pl-20080422-src.tar.bz2 – jutro pewnie będzie inny
po ściągnięciu plik rozpakowuję:
tar xvjf sjp-ispell-pl-*-src.tar.bz2
a następnie, wchodzę do źródeł i wydaję sekwencję poleceń:
cd sjp-ispell-pl-[0-9]*
sort -u -t/ +0f -1 +0 -T /usr/tmp -o polish.med polish.all
for a in polish.aff polish.med; do cat $a | iconv -f iso8859-2 -t utf-8 > $a.utf8; done
cp polish.aff.utf8 `pg_config --sharedir`/tsearch_data/polish.affix
cp polish.med.utf8 `pg_config --sharedir`/tsearch_data/polish.dict
touch `pg_config --sharedir`/tsearch_data/polish.stop
ostatnie 3 polecenia mogą wymagać użycia sudo – zależnie od tego czy user z którego to odpalaliśmy miał prawa zapisu do katalogu “share” postgresa.
po zrobieniu tego pora na “przyjemności”.
łączę się psql’em do bazy w której chcę mieć tsearcha i wykonuję następujące sqlki:
CREATE TEXT SEARCH CONFIGURATION public.polish ( COPY = pg_catalog.english );
CREATE TEXT SEARCH DICTIONARY polish_ispell (
TEMPLATE = ispell,
DictFile = polish,
AffFile = polish,
StopWords = polish
);
ALTER TEXT SEARCH CONFIGURATION polish
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH polish_ispell, simple;
i to zasadniczo wszystko.
już teraz powinniście zobaczyć, że parser działa i odmienia prawidłowo:
SELECT * FROM ts_debug(
'public.polish',
'W Szczebrzeszynie chrząszcz brzmi w trzcinie, i Szczebrzeszyn z tego słynie'
) where alias <> 'blank';
alias | description | token | dictionaries | dictionary | lexemes
-----------+-------------------+-----------------+------------------------+---------------+-----------------
asciiword | Word, all ASCII | W | {polish_ispell,simple} | polish_ispell | {w}
asciiword | Word, all ASCII | Szczebrzeszynie | {polish_ispell,simple} | polish_ispell | {szczebrzeszyn}
word | Word, all letters | chrząszcz | {polish_ispell,simple} | polish_ispell | {chrząszcz}
asciiword | Word, all ASCII | brzmi | {polish_ispell,simple} | polish_ispell | {brzmieć}
asciiword | Word, all ASCII | w | {polish_ispell,simple} | polish_ispell | {w}
asciiword | Word, all ASCII | trzcinie | {polish_ispell,simple} | polish_ispell | {trzcina}
asciiword | Word, all ASCII | i | {polish_ispell,simple} | polish_ispell | {i}
asciiword | Word, all ASCII | Szczebrzeszyn | {polish_ispell,simple} | polish_ispell | {szczebrzeszyn}
asciiword | Word, all ASCII | z | {polish_ispell,simple} | polish_ispell | {z}
asciiword | Word, all ASCII | tego | {polish_ispell,simple} | polish_ispell | {ten,ty}
word | Word, all letters | słynie | {polish_ispell,simple} | polish_ispell | {słynąć}
(11 rows)
teraz, zakładając, że mamy tabelkę:
CREATE TABLE test (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL DEFAULT '',
lead TEXT NOT NULL DEFAULT '',
body TEXT
);
możemy dodać do niej pełnotekstowe wyszukiwanie na 2 sposoby: prosty, ale brzydki i utrudniający później pisanie. albo ciut bardziej skomplikowany, ale za to ładniejszy.
metoda 1:
CREATE INDEX tsearch_test ON test USING gin(to_tsvector('public.polish', title || lead || body));
i koniec.
minus jest taki, że teraz trzeba będzie za każdym razem:
select * from test where to_tsvector('public.polish', title || lead || body) @@ 'trzciny';
metoda 2:
alter table test add column ft tsvector;
update test set ft=to_tsvector('public.polish', title || lead || body);
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON test FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ft, 'public.polish', title, lead, body);
CREATE INDEX tsearch_test ON test USING gin( ft );
jak widać – roboty trochę więcej, ale za to zapytania będą ładniejsze:
select * from test where ft @@ 'trzciny'
dodatkowo – aby sobie ułatwić testowania można zrobić coś takiego:
alter database NAZWA_BAZY_DANYCH set default_text_search_config = 'public.polish';
dzięki czemu używając np. ts_debug() nie trzeba będzie podawać słownika (w 8.2 do detekcji używał encodingu, tu jest to inaczej rozwiązane).
mam nadzieję, że opis jest jasny. no i, że się przyda
23 comments
Jedna uwaga. Wydaje mi się, że w przypadku systemu z LC_ALL=pl_PL.UTF-8 należy zmienić kodowanie pliku polish.all przed jego posortowaniem. W przeciwnym wypadku uzyskany efekt jest trochę inny od zamierzonego
Świetne, dzięki za tą notke
Przydalo sie jak cholera, wkoncu moge ukonczyc swoj semiautomatyczny tlumacz… wyekstrakowanie tematu… przetlumaczenie przez soap… wgranie ajaxem… no i na koncu user tylko sobie klika ktore slowo najbardziej pasuje
na koncu zdanie przetlumaczone z tym ze niegramatycznie… ale najwazniejsze ze po tresci da sie bez problemu domyslec 
mam pare uwag do instrukcji, nie da sie wczytac inaczej zakodowanych slownikow niz w utf-8, za dokumentacja postgresa:
“Most types of dictionaries rely on configuration files, such as files of stop words. These files must be stored in UTF-8 encoding. They will be translated to the actual database encoding, if that is different, when they are read into the server.”
mimo, ze wszystkie pliki sa zakodowane w utf-8, mam problem z plikiem affix, przy probie wczytania pojawia sie nastepujacy blad:
CREATE TEXT SEARCH DICTIONARY polish_ispell (
TEMPLATE = ispell,
DictFile = polish,
AffFile = polish,
StopWords = polish
);
ERROR: syntax error
CONTEXT: line 175 of configuration file “/usr/local/postgresql-8.3.4/share/tsearch_data/polish.affix”: ” L E C > -C,GŁEŚ
(w pliku .affix jest poprawnie zakodowany, wiec nie wiem w czym moze byc problem:
L E C > -C,GĹ~AEM #zalec (15a)
L E C > -C,GĹ~AEĹ~Z
any ideas?
@grissom:
niestety – nie mam pomyslu. ten plik zrobiles tak jak opisałem?
specem od tsearcha nie jestem, bo go raczej nie uzywam. opisalem co i jak, bardziej jako sciagawke, gdybym jednek kiedys potrzebowal, niz cokolwiek innego.
zrobilem dokladnie jak pisales. Czy udalo Ci sie wczytac pliki ispella do bazy? (baza w latin2) a plik w utf-8 (zalecany w dokumetnacji – jak pisalem wyzej). W dokumentacji bylo napisane, zeby skorzystac z ispella z tej strony:
http://wiki.services.openoffice.org/wiki/Dictionaries#Polish_.28Poland.29, wczytalem to bez problemu, ale teraz musze wszystkie rekordy ponownie zreindeksowac, bo nie wyszukuje mi róznych fraz z odmianami ;(.
Czego uzywasz do wyszukiwania pelnotekstowego?
@grissom:
baza w latin2? czemu? uzywam tylko baz utf’owych.
co do wyszukiwania – lucene/solr/sphinx.
baza zostala stworzona jakies 4 lata temu i nie bylo okazji, zeby przesiasc sie na utf ;/
Oczywiście można używać:
select * from test where ft @@ ‘trzciny’
oraz z odmianą ‘trzciny’ -> trzcina
select * from test where ft @@ to_tsquery(‘public.polish’, ‘trzciny’)
# drugie rozwiązanie odmieni np: niedźwiedzia -> niedźwiedź
świetny artykuł, dzięki
Ja mam małe pytanie, co jest bardziej wydajne tsearch2 czy sphinx ?
Czy może aby uzyskać najlepsze efekty w wyszukiwaniu to trzeba użyć obu tych systemów ?
@kamil:
w/g mnie zawsze system dedykowany będzie szybszy od ogólnego. W tym przypadku sphinx od tsearcha, bo tsearch jest w ogólnej bazie danych.
Będę o tym pisał niedługo na blogu, więc podam też jakieś konkretniejsze uzasadnienia i przykłady.
Dzięki za szybką odpowiedź, poczekam na więcej informacji zanim zacznę robić coś więcej
Mam jeszcze pytanie odnośnie to_tsquery, utworzyłem sobie testową tabelę (id, nazwa, ft). Do tabeli dodałem jeden wiersz z wartością ‘sklepu’ w polu nazwa. Gdy robię wyszukiwanie:
select * from test where ft @@ ‘sklepu’;
Nie znaleziono danych. – dlatego, że nic nie zwraca to chcę użyć to_tsquery
Całkowity czas pracy: 4.743 ms.
select * from test where ft @@ ‘sklep’; – to zapytanie tylko po to aby pokazać jaki jest czas
1 wiersz(y)
Całkowity czas pracy: 5.786 ms.
select * from test where ft @@ to_tsquery(‘sklepu’);
1 wiersz(y)
Całkowity czas pracy: 1,853.085 ms.
no i dochodzimy do to_tsquery, dlaczego to zapytanie wykonuje się aż prawie 2sek, tak ma być czy ja coś zrobiłem nie tak ?
@kamil:
aby cokolwiek móc powiedzieć musiałbym zobaczyć explain analyze tych zapytań.
wrzucanie ich w komentarze jest trochę bez sensu bo wordpress spowoduje, że będą nieczytelne, więc prośba: wrzuć explain analyze wszystkich 3 zapytań na explain.depesz.com (jako oddzielne explainy, nie jako jeden z 3 planami) a tu podaj tylko linka do tych planow. i pokaż \d.
aha. czy *drugie* wywołanie select * from test where ft @@ to_tsquery(’sklepu’); też jest wolne? drugie w ramach tego samego połączenia do bazy.
@depesz:
nie wiem czy dobrze wkleiłem, bo wiersze mam na czerwono :/
select * from test where ft @@ ’sklep’;
http://explain.depesz.com/s/pz
select * from test where ft @@ to_tsquery(’sklepu’);
http://explain.depesz.com/s/g3
\d
http://img13.imageshack.us/img13/5365/73868906.png
Kurde zapomniałem napisać, kolejne wywołania tego zapytania w jednym połaczeniu wykonuja się od razu. Tylko mi kolejne wywołania nie będą potrzebne, chciałbym aby za pierwszym razem wyszukiwał szybko, może trzeba jekieś cache ustawić ?
@kamil:
pierwsze wywołanie w danym połączeniu do bazy jest *zawsze* wolne, bo postgres musi załadować słowniki.
i nie chodzi o to, że jak ktoś poda raz “depesz” a raz “kamil” to trzeba wykonać każde 2 razy.
po prostu po połączeniu do bazy musisz wykonać jedno obojętne jakie wyszukiwanie, on załaduje słowniki i każde kolejne wyszukiwanie w tym połączeniu do bazy będzie szybkie.
oznacza to, że musisz używać albo permanent connectów (a feeee) albo jakiegoś poolera połączeń, i tu ci polecę pgbouncera.
@depesz:
wielkie dzięki za pomoc, idę czytać o pgbouncerze
Po utworzeniu słowników wg tej recepty robię test i dostaję taki błąd?
text search configuration “public.polish” does not exist
Dodam, że \dF pokazuje mi w konfiguracji “public.polish”, a \dFd “public.polish_ispell”.
Witam. Bardzo ciekawy i pomocny artykul
Mam taki ciekawy problem do rozwiazania, moze ktos cos takiego juz probowal?
1. Tablica News i News_comments
2. Chcialbym aby tsearch za jednym zamachem przeszukal tylko jedna tablice, po co ma siegac do dwoch? Ale update musialbym zrobic z dwoch tablic. Czyli cos takigo jak ponizej:
update News n set ft = to_tsvector(‘public.polish’, title || lead || body || select tresc_kom from News_comments nn where nn.jdnews_id = n.jdnews_id);
No to niestety nie chce juz dzialac ;/
Czyli pewnie musialbym napisac/przebudowac f-cje postgresa to_tsvector() oraz tsvector_update_trigger() … a moze znacie jakis prostszy sposob ;/
pzdr
witam,
czyli nie trzeba już wykonywać tsearch2 z contiba ?
pozdrawiam
@Arpe: nie trzeba.
[quote]ERROR: syntax error
CONTEXT: line 175 of configuration file “/usr/local/postgresql-8.3.4/share/tsearch_data/polish.affix”: ” L E C > -C,GŁEŚ[/quote]
Mam to samo. Baza 8.3 UTF8. Pliki też. O co chodzi?
do @fryk
Miałem dokładnie to samo.
Musisz zrobić backup wszystkich baz danych np. poleceniem:
pg_dumpall -i -c -f ./dump_all.out -h localhost -U postgres
następnie skasować zawartość katalogu w którym postgresql trzyma dane -u mnie to: ‘/var/lib/pgsql/data’
zainicjować bazę danych tak, aby uwzględniała polskie locale:
initdb –encoding=UTF-8 –locale=pl_PL.UTF-8
a następnie oddtworzyć bazy z backapu:
psql -f ./dump_all.out postgres
Dla bezpieczeństwa, zatrzymaj postgresa i skopiuj zawartość powyższego katalogu jako dodatkowe zabezpieczenie. Plik z konfiguracją bazy może się przydać, ponieważ initdb tworzy nowy domyślny.
I jeszcze jedna bardzo ważna rzecz: upewnij się, czy sortowanie w systemie działa poprawnie, bez tego będzie lipa.