April 22nd, 2008 by depesz | Tags: , , , | 33 comments »
Did it help? If yes - maybe you can help me?

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 🙂

  1. 33 comments

  2. # Yogi
    Apr 23, 2008

    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 🙂

  3. # destroyer
    May 12, 2008

    Świetne, dzięki za tą notke 😉

  4. # Mateusz
    Aug 27, 2008

    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 🙂

  5. # grissom
    Oct 12, 2008

    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?

  6. Oct 12, 2008

    @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.

  7. # grissom
    Oct 15, 2008

    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?

  8. Oct 15, 2008

    @grissom:
    baza w latin2? czemu? uzywam tylko baz utf’owych.

    co do wyszukiwania – lucene/solr/sphinx.

  9. # grissom
    Oct 16, 2008

    baza zostala stworzona jakies 4 lata temu i nie bylo okazji, zeby przesiasc sie na utf ;/

  10. # spoksss
    Dec 5, 2008

    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

  11. # kamil
    Mar 15, 2009

    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 ?

  12. Mar 15, 2009

    @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.

  13. # kamil
    Mar 15, 2009

    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 ?

  14. Mar 15, 2009

    @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.

  15. # kamil
    Mar 15, 2009

    @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

  16. # kamil
    Mar 15, 2009

    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ć ?

  17. Mar 15, 2009

    @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.

  18. # kamil
    Mar 15, 2009

    @depesz:
    wielkie dzięki za pomoc, idę czytać o pgbouncerze 🙂

  19. # tm
    May 22, 2009

    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”.

  20. # vojtas
    Aug 3, 2009

    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

  21. # Arpe
    Nov 10, 2009

    witam,
    czyli nie trzeba już wykonywać tsearch2 z contiba ?

    pozdrawiam

  22. Nov 10, 2009

    @Arpe: nie trzeba.

  23. Dec 3, 2009

    [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?

  24. Dec 11, 2009

    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.

  25. # Raven
    Sep 28, 2010

    Witam, mam mały problem z tsearch2. Otórz np. dla tekstu ‘Różni Wykonawcy – BURN ONE UP !’ tsvector wyglada tak: ‘up’:5 ‘one’:4 ‘burn’:3 ‘różny’:1 ‘różnić’:1 ‘wykonawca’:2

    …i teraz próbując wykonać zapytanie:
    select * from search where primary_data_ft @@ ‘wykonawca’
    oczywiście dany rekord jest zwracany, lecz już dla zapytania:
    select * from search where primary_data_ft @@ ‘wykonawcy’
    nic nie jest zwracane.

    Czy to jest prawidłowe zachowanie? Wydaje mi się cokolwiek dziwne.

  26. Sep 28, 2010

    zamiast robić @@ ‘string’ używaj poprawnego to_tsquery, i podaj której konfiguracji ma użyć – np. ‘polish’

  27. # Wojtek
    Nov 25, 2010

    Miałem podobny problem jak RAVEN, ale zastosowałem się do podpowiedzi i zadziałało :).

    Ale mam inny problem pytanie:
    Czy możliwe jest szukanie w tsearch po fragmęncie wyrazu ?
    Chciałbym odnaleźć wwszystkie zdjęcia z opisaem np. plaża lub innymi pasujacymi do ‘pla’ tak jak w przykładzie poniżej:
    select * from photo
    where
    photo_ft @@ plainto_tsquery(‘polish’, ‘pla’)

  28. Nov 25, 2010

    @Wojtek:
    to zależy. “pla” jest nie tylko fragmentem słowa “plaża”, ale też (co ważne) prefixem.

    szukanie po prefixach jest relatywnie proste – http://www.depesz.com/index.php/2008/05/17/waiting-for-84-partial-match-support-in-gin-and-sequence-restart/

    szukanie po fragmentach nie będących początkiem jest mocno trudniejsze, ale możliwe – po prostu musisz budować słownik słów, i fragmentami szukasz w słowniku, wyciągając konkretne słowa, a potem słowami w tsearchu.

  29. # Arek
    Mar 28, 2011

    Cześć, testuję full text search pod postgresql 9 i mam problem, sam text search działa ok, korzysta z indexu ale: gdy w zapytaniu tabelę z kolumną z full tekstem joinuje z innymi tabelami to jeżeli wyszukiwane w full searchu frazy są łączone jako ‘or’ to do wyszukiwania z tych innych tabel nie są brane indeksy (a istnieją – zwykłe btree), robiony jest sequence scan, gdy frazy są łączone jako ‘and’ są używane indeksy…

    select … from
    t join t2 on …
    where t.fullidx @@ to_tsquery(‘LITWA|POLSKA’) – indeks dla t2 unused

    where t.fullidx @@ to_tsquery(‘LITWA&POLSKA’) – indeks dla dla t2 used

    prosze o wytłumaczenie o co chodzi:-)

  30. Mar 28, 2011

    @Arek:
    wrzuć explainy (najlepiej explain analyze) na explain.depesz.com i daj linki. zobaczę czy uda mi się coś wyczytać.

  31. # Arek
    Mar 29, 2011

    powalczyłem trochę i wnioski na dziś są: indeksy tabel joinowanych nie są używane gdy: wyniki zapytania są sortowane po polu tekstowym (kosztowne) i gdy jest dużo (np. kilka tysięcy) wyników wyszukiwania po full text search, ja obliczam ts_rank i po nim sortuję bo chce mieć najlepiej pasujące na początku, jeśli posortuję po jakims integer to jest ok – używa indeksy, będę wdzięczny za wyjaśnienie zjawiska, pozdrawiam

    http://explain.depesz.com/s/Ktc used index – ok
    http://explain.depesz.com/s/ovF unused index

  32. Mar 29, 2011

    tak patrząc na plany, to całe to zapytanie mi się nie podoba.

    tzn. pg wybiera taki a nie inny plan, bo masz najprawdopodobniej dosyć szybkie dyski i/lub sporo pamięci (w stosunku do wielkości bazy), ale niedokonfigurowanego postgresql.conf’a.

    natomiast.

    to zapytanie i tak w/g mnie należy przepisać. co do tego “jak” – nie widząc całości zapytania nie jestem w stanie powiedzieć, ale na oko patrząc to przesuwając where’a fulltextowego, sort i limit do subselecta uzyskasz spore przyspieszenie.

  33. # PiotreK
    Apr 17, 2011

    Bardzo przydatny post. Dzięki wielkie.

    Chcę stworzyć uniwersalne wyszukiwanie słownikowe w 3 językach w PostgreSQL 9.0. Czy istnieje jakiś zgrabny sposób na to (może połączenie słowników)? Moje podejście do tematu:

    SELECT
    TAB.id
    GREATEST(
    ts_rank(contentpl, to_tsquery(‘orzeł|świnka’)),
    ts_rank(contenten, to_tsquery(‘orzeł|świnka’)),
    ts_rank(contentde, to_tsquery(‘orzeł|świnka’))
    )
    FROM
    table TAB
    WHERE
    TAB.contentpl @@ ‘orzeł|świnka’
    OR TAB.contenten @@ ‘orzeł|świnka’
    OR TAB.contentde @@ ‘orzeł|świnka’
    ORDER BY 2 DESC

    … wystarczy powiedzieć, że TAB zawiera odpowiednio zapełnione pola tsvector:

    contentpl =~ to_tsvector(‘polish’, prepared || content) .. itp. dla ‘english’ i ‘deutch’

  34. Jul 26, 2012

    Gotowy polski słownik można pobrać stąd:

    http://code.google.com/p/tsearch-polish/

    Zawiera też synonimy dla słów bez polskich ogonków, tak że szukając “zlobek” czy “zlobkiem” znajdzie nam “żłobek”.

Leave a comment