Polish TSearch in 8.3 (polski tsearch w postgresie 8.3)

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 🙂

33 thoughts on “Polish TSearch in 8.3 (polski tsearch w postgresie 8.3)”

  1. 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. 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 🙂

  4. 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?

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

  6. 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?

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

    co do wyszukiwania – lucene/solr/sphinx.

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

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

  10. 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 ?

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

  12. 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 ?

  13. @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. 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ć ?

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

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

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

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

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

    pozdrawiam

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

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

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

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

  26. 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’)

  27. @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 – https://www.depesz.com/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.

  28. 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:-)

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

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

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

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

  32. 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’

Comments are closed.