jak część z was wie byłem ostatnio na pgcon'ie.
tu od razu informacyjnie – byłem dzięki mojemu pracodawcy – firmie eo networks, któremu niniejszym publicznie bardzo dziękuję za umożliwienie mi wzięcia udziału w tej imprezie – jeśli szukacie pracy i znacie się na javie, bazach danych (głównie postgres, ale slyszałem też o jakichś projektach na innych bazach) – warto się odezwać.
wracając do meritum.
byłem tam na prezentacji olega bartunova nt. nowego tsearcha. nowego – nie znaczy, że będzie tsearch3. nowego – czyli tsearch2 zintegrowanego z samym postgresem.
jak się uda wszystko co zaplanowali to będzie tak w 8.3, ale jak się nie uda – no cóż. zobaczymy.
na prezentacji podpatrzyłem jedną rzecz którą wam tu teraz pokażę: wyszukiwanie pełnotekstowe z “poprawianiem" literówek (fuzzy-full text search).
zacznijmy od postawienia bazy:
ściągamy źródła: wget ftp://sunsite.icm.edu.pl/site/postgres/source/v8.2.4/postgresql-8.2.4.tar.bz2
tworzę konto test, a na nim katalogi:
- /home/test/src
- /home/test/data
- /home/test/work
.bashrc konta zawiera:
export PGROOT=/home/test export PGDATA=$PGROOT/data export PATH=$PGROOT/work/bin:/usr/local/bin:/usr/bin:/bin export LANG=pl_PL.UTF-8 export LC_CTYPE="pl_PL.UTF-8" export LC_NUMERIC="pl_PL.UTF-8" export LC_TIME="pl_PL.UTF-8" export LC_COLLATE="pl_PL.UTF-8" export LC_MONETARY="pl_PL.UTF-8" export LC_MESSAGES="pl_PL.UTF-8" export LC_PAPER="pl_PL.UTF-8" export LC_NAME="pl_PL.UTF-8" export LC_ADDRESS="pl_PL.UTF-8" export LC_TELEPHONE="pl_PL.UTF-8" export LC_MEASUREMENT="pl_PL.UTF-8" export LC_IDENTIFICATION="pl_PL.UTF-8"
w katalogu src rozpakowuję źródła.
przechodzę do: /home/test/src/postgresql-8.2.4 i wykonuję:
./configure \ --prefix=/home/test/work \ --with-pgport=5820 \ --with-tcl \ --with-perl \ --with-python \ --enable-integer-datetimes \ --without-krb5 \ --without-pam \ --without-bonjour \ --with-openssl \ --with-readline \ --with-zlib \ --with-gnu-ld
oczywiście opcje można podać inne. używam niestandardowego portu bo używam wielu postgresów na jednej maszynie.
zakładając, że configure się udało robię: make; make install
to jest standard. teraz coś mniej standardowego:
cd contrib make make install
jeśli nie wiesz co się stanie – stanie się tyle, że skomplikuję i zainstaluję wszystkie modułky z contriba. nie znaczy, że będę ich wszystkich używał. po prostu – skompiluję i będą czekały na użycie 🙂
po instalacji – pora na initdb.
ponieważ mamy PGDATA ustawiane w .bashrc (więc powinno być też w aktualnym shellu), wystarczy:
=> initdb
bez opcji.
ponieważ to instancja testowa – nie zawracam sobie głowy konfigurowaniem niczego, po prostu podnoszę postgresa:
pg_ctl start
tak utworzony postgres ma:
1. superusera o nazwie test
2. bazy danych: template0, template1, postgres
stwórzmy więc bazę testową:
=> createdb
znowu bez parametrów 🙂
i szybki test:
=> psql ... test=# SELECT version(); version ------------------------------------------------------------------------------------------------ PostgreSQL 8.2.4 ON i686-pc-linux-gnu, compiled BY GCC gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4) (1 ROW)
działa.
ok.
to teraz. mam bazę testową której niestety nie mogę udostępnić.
ale oto kilka danych na jej temat:
test=# \d test TABLE "public.test" COLUMN | TYPE | Modifiers --------+--------+----------- id | BIGINT | NOT NULL tekst | text | Indexes: "test_pkey" PRIMARY KEY, btree (id) test=# SELECT COUNT(*), MIN(LENGTH(tekst)), avg(LENGTH(tekst)), MAX(LENGTH(tekst)), SUM(LENGTH(tekst)), pg_size_pretty(pg_relation_size('test')) AS table_size, pg_size_pretty(pg_relation_size('test_pkey')) AS pkey_size FROM test; COUNT | MIN | avg | MAX | SUM | table_size | pkey_size --------+-----+----------------------+---------+-----------+------------+----------- 646731 | 0 | 567.1078795826091259 | 1107001 | 366739592 | 312 MB | 23 MB (1 ROW)
no to zróbmy pierwszy test. dwa razy, od razu po sobie, explain analyze wyszukiwania:
test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE tekst ilike '%pieluchy%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=47993.16..47993.17 ROWS=1 width=0) (actual TIME=25660.035..25660.037 ROWS=1 loops=1) -> Seq Scan ON test (cost=0.00..47993.14 ROWS=8 width=0) (actual TIME=2.856..25659.908 ROWS=26 loops=1) FILTER: (tekst ~~* '%pieluchy%'::text) Total runtime: 25660.114 ms (4 ROWS) test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE tekst ilike '%pieluchy%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=47993.16..47993.17 ROWS=1 width=0) (actual TIME=24783.887..24783.889 ROWS=1 loops=1) -> Seq Scan ON test (cost=0.00..47993.14 ROWS=8 width=0) (actual TIME=1.005..24783.772 ROWS=26 loops=1) FILTER: (tekst ~~* '%pieluchy%'::text) Total runtime: 24783.940 ms (4 ROWS)
ok. jak widać – czas w okolicach 24-25 sekund, zwrócone 26 rekordów (tzn. 1 rekord z liczbą 26 🙂
no to teraz dodajemy tsearcha:
test=# \i /home/test/work/share/postgresql/contrib/tsearch2.sql
no to teraz pora na to co tygrysy “lubią" najbardziej. konfiguracja tsearcha.
( tu wtręt. poniżej zapisuję polecenia których użyłem. nie mówię, że je rozumiem. robią co trzeba, wziąłem je z docy nt. niemieckiego tsearcha, zmodyfikowałem lekko. możliwe, że coś da się zrobić lepiej/skuteczniej – jak tak jest – napisz w komentarzu ).
- wchodzimy na http://www.kurnik.pl/dictionary/ i pobieramy najnowszy plik będący źródłami (src) do słowników ispella. obecnie jest to alt-ispell-pl-20060823-src.tar.bz2, czyli:
wget http://www.kurnik.pl/dictionary/alt-ispell-pl-20060823-src.tar.bz2
- tar xvjf alt-ispell-pl-*.tar.bz2
- cd alt-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
- mkdir /home/test/work/share/tsearch
- cp polish.{aff,med}.utf8 /home/test/work/share/tsearch
- psql
- UPDATE pg_ts_cfg SET locale = ‘pl_PL.UTF-8' WHERE ts_name = ‘default';
- INSERT INTO pg_ts_dict
(SELECT ‘pl_ispell',
dict_init,
‘DictFile="/home/test/work/share/tsearch/polish.med.utf8″,' ||
‘AffFile="/home/test/work/share/tsearch/polish.aff.utf8″‘,
dict_lexize
FROM pg_ts_dict
WHERE dict_name = ‘ispell_template'); - UPDATE pg_ts_cfgmap SET dict_name = ‘{pl_ispell,simple}' WHERE ts_name = ‘default' AND tok_alias LIKE ‘%word';
ok. teoretycznie skonfigurowane. teraz – trzeba się rozłączyć z psql'a i połączyć jeszcze raz. i robimy test:
test=# SELECT * FROM ts_debug('Kupiłem krzesło marki JUWENTUS. test. ąćę 1010'); ts_name | tok_type | description | token | dict_name | tsvector ---------+----------+------------------+----------+--------------------+------------------------- DEFAULT | word | Word | Kupiłem | {pl_ispell,simple} | 'kupić' DEFAULT | word | Word | krzesło | {pl_ispell,simple} | 'krzesło' DEFAULT | lword | Latin word | marki | {pl_ispell,simple} | 'marek' 'marka' 'marki' DEFAULT | lword | Latin word | JUWENTUS | {pl_ispell,simple} | 'juwentus' DEFAULT | lword | Latin word | test | {pl_ispell,simple} | 'test' 'testo' DEFAULT | nlword | Non-latin word | ąćę | {pl_ispell,simple} | 'ąćę' DEFAULT | uint | UNSIGNED INTEGER | 1010 | {simple} | '1010' (7 ROWS)
na oko jest ok. zwracam uwagę na to, że słowo “marki" ma w tsvectorze 3 słowa (a test dwa). tak jest dobrze.
to teraz. dodaję do oryginalnej tabeli pole które będzie przechowywało tsvectory (czyli to po czym się szuka):
ALTER TABLE test ADD COLUMN fts tsvector;
i wstawiamy tam wartości:
UPDATE test SET fts = to_tsvector( tekst );
to trochę trwa, więc w międzyczasie uwaga – nadpisałem konfigurację domyślną (default) – bo mogłem. lepiej jest jednak zrobić od podstaw swoją. tsearch potrafi sam wybrać konfigurację bazując na locale (dlatego musiałem ustawić pl_PL.UTF-8 w tabeli pg_ts_cfg), więc nazwa jako taka ma niewielkie znaczenie.
(piszę ten tekst i jednocześnie robię to o czym piszę, więc teraz mam długą przerwę … )
40 minut później.
update zrobiony. teraz vacuum full by zmniejszyć tabelę.
kolejne 11 minut minęło 🙂
ok. no to zobaczmy o ile wzrosła tabelka:
test=# SELECT pg_size_pretty(pg_relation_size('test')); pg_size_pretty ---------------- 546 MB (1 ROW)
sporo. 75% przyrostu.
zobaczmy czy zadziała proste wyszukiwanie (na razie bez indeksów!):
tradyjnie 2 explainy:
test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Aggregate (cost=77974.76..77974.77 ROWS=1 width=0) (actual TIME=142736.967..142736.969 ROWS=1 loops=1) -> Seq Scan ON test (cost=0.00..77973.14 ROWS=647 width=0) (actual TIME=299.344..142736.687 ROWS=49 loops=1) FILTER: (fts @@ '''pielucha'''::tsquery) Total runtime: 142772.450 ms (4 ROWS) test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Aggregate (cost=77974.76..77974.77 ROWS=1 width=0) (actual TIME=138817.011..138817.013 ROWS=1 loops=1) -> Seq Scan ON test (cost=0.00..77973.14 ROWS=647 width=0) (actual TIME=1527.378..138816.783 ROWS=49 loops=1) FILTER: (fts @@ '''pielucha'''::tsquery) Total runtime: 138817.060 ms (4 ROWS)
ojć. długo.
ale za to – zwróćcie uwagę, że zapytanie zwraca 49 rekordów. a nie 26. czemu więcej? bo zadziało odmienianie słów :). czyli trafione zostały też teksty zawierające “pielucha", “pieluchami" itd. 🙂
sweet.
no to teraz indeksy.
najpierw tradycyjny – gist:
test=# CREATE INDEX gist_idx ON test USING gist(fts);
7 minut i 174 mega na dysku później mam indeks.
test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2501.97..2501.98 ROWS=1 width=0) (actual TIME=51209.957..51209.959 ROWS=1 loops=1) -> Bitmap Heap Scan ON test (cost=98.16..2500.35 ROWS=647 width=0) (actual TIME=9204.616..51209.734 ROWS=49 loops=1) FILTER: (fts @@ '''pielucha'''::tsquery) -> Bitmap INDEX Scan ON gist_idx (cost=0.00..98.00 ROWS=647 width=0) (actual TIME=8992.233..8992.233 ROWS=4967 loops=1) INDEX Cond: (fts @@ '''pielucha'''::tsquery) Total runtime: 51233.151 ms (6 ROWS) TIME: 51291,839 ms test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2501.97..2501.98 ROWS=1 width=0) (actual TIME=1817.866..1817.868 ROWS=1 loops=1) -> Bitmap Heap Scan ON test (cost=98.16..2500.35 ROWS=647 width=0) (actual TIME=995.168..1817.640 ROWS=49 loops=1) FILTER: (fts @@ '''pielucha'''::tsquery) -> Bitmap INDEX Scan ON gist_idx (cost=0.00..98.00 ROWS=647 width=0) (actual TIME=990.989..990.989 ROWS=4967 loops=1) INDEX Cond: (fts @@ '''pielucha'''::tsquery) Total runtime: 1817.951 ms (6 ROWS)
pierwszy czas kiepski, ale drugi – z wypełnionymi cache'ami już w lepszym czasie.
pierwszy był na tyle kiepski, że rozłączyłem się, połączyłem (z bazą) znowu i wykonałem te 2 explainy jeszcze raz. wynik: 530 i 555 milisekund. nice 🙂
w 8.2 dodali indeksy GiN. zobaczmy więc jak one się sprawdzą.
test=# DROP INDEX gist_idx; test=# CREATE INDEX gin_idx ON test USING gin(fts);
minęło jakieś 1.5 godziny (nie wiem dokładnie ile bo zapomniałem włączyć \timing).
mam indeks. wielkość: 427mega. ałć.
no to zobaczmy co potrafi przy wyszukiwaniu.
test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2631.27..2631.28 ROWS=1 width=0) (actual TIME=390.270..390.272 ROWS=1 loops=1) -> Bitmap Heap Scan ON test (cost=227.45..2629.65 ROWS=647 width=0) (actual TIME=32.043..390.100 ROWS=49 loops=1) Recheck Cond: (fts @@ '''pielucha'''::tsquery) -> Bitmap INDEX Scan ON gin_idx (cost=0.00..227.29 ROWS=647 width=0) (actual TIME=12.565..12.565 ROWS=49 loops=1) INDEX Cond: (fts @@ '''pielucha'''::tsquery) Total runtime: 390.371 ms (6 ROWS) test=# EXPLAIN analyze SELECT COUNT(*) FROM test WHERE fts @@ plainto_tsquery('pieluchy'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2631.27..2631.28 ROWS=1 width=0) (actual TIME=0.432..0.434 ROWS=1 loops=1) -> Bitmap Heap Scan ON test (cost=227.45..2629.65 ROWS=647 width=0) (actual TIME=0.121..0.311 ROWS=49 loops=1) Recheck Cond: (fts @@ '''pielucha'''::tsquery) -> Bitmap INDEX Scan ON gin_idx (cost=0.00..227.29 ROWS=647 width=0) (actual TIME=0.093..0.093 ROWS=49 loops=1) INDEX Cond: (fts @@ '''pielucha'''::tsquery) Total runtime: 0.513 ms (6 ROWS)
WOW!
pomny problemów przy giście, rozłączam się i próbuję jeszcze raz. 18.9 i 0.375 milisekundy! kolejne wyniki w czasach między 0.3 a 0.5ms.
ok. czyli mam działającego fts'a, na tsearch2 z indeksami gin.
a co dokładnie chciałem pokazać nt. trgm?
ładujemy trigramy:
\i /home/test/work/share/postgresql/contrib/pg_trgm.sql
tworzymy tabelę potrzebną do fuzzy-searchowania:
SELECT * INTO fuzzy_helper FROM stat('select fts from test') ORDER BY ndoc DESC, nentry DESC, word;
1.5 godziny później 🙂
CREATE INDEX trgm_idx ON fuzzy_helper USING gist(word gist_trgm_ops);
tym razem tylko 2 minuty 🙂
no to testujemy.
test=# SELECT word, similarity(word, 'kompiuter') AS sml FROM fuzzy_helper WHERE word % 'kompiuter' ORDER BY sml DESC, word LIMIT 5; word | sml ------------+---------- komputer | 0.583333 /komputer | 0.583333 2.komputer | 0.5 3.komputer | 0.5 kompter | 0.5 (5 ROWS)
ponieważ kolumny ndoc i nentry zawierają ilości dokumentów (oraz ilość wystąpień danego słowa) można użyć tego nawet gdy mamy w bazie teksty niepoprawne ortograficznie.
test=# SELECT word, similarity(word, 'samohód') AS sml, ndoc, nentry FROM fuzzy_helper WHERE word % 'samohód' ORDER BY sml DESC, word LIMIT 15; word | sml | ndoc | nentry -------------+----------+-------+-------- samohód | 1 | 4 | 4 samohzód | 0.7 | 1 | 1 samocód | 0.6 | 3 | 3 samolód | 0.6 | 1 | 1 samcohód | 0.545455 | 2 | 2 samocgód | 0.545455 | 3 | 3 samochód | 0.545455 | 30472 | 46018 samochółd | 0.545455 | 2 | 2 samockód | 0.545455 | 2 | 2 samoczód | 0.545455 | 2 | 2 samorząd | 0.545455 | 453 | 580 samochoód | 0.5 | 2 | 2 samoochód | 0.5 | 3 | 3 samopchód | 0.5 | 1 | 1 samochochód | 0.461538 | 1 | 1 (15 ROWS)
oczywiście dobranie odpowiednich warunków na ndoc i/lub nentries może kosztować trochę czasu, ale jak widać – potencjał jest spory.
co do czasu wyszukiwania słów podobnych:
test=# EXPLAIN analyze SELECT word, similarity(word, 'samochody') AS sml, ndoc, nentry FROM fuzzy_helper WHERE word % 'samochody' AND ndoc > 100 ORDER BY sml DESC, word LIMIT 15; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- LIMIT (cost=1961.39..1961.43 ROWS=15 width=40) (actual TIME=947.836..947.903 ROWS=13 loops=1) -> Sort (cost=1961.39..1961.97 ROWS=233 width=40) (actual TIME=947.830..947.853 ROWS=13 loops=1) Sort KEY: similarity(word, 'samochody'::text), word -> Bitmap Heap Scan ON fuzzy_helper (cost=37.84..1952.23 ROWS=233 width=40) (actual TIME=941.712..947.752 ROWS=13 loops=1) Recheck Cond: (word % 'samochody'::text) FILTER: (ndoc > 100) -> Bitmap INDEX Scan ON trgm_idx (cost=0.00..37.78 ROWS=698 width=0) (actual TIME=941.557..941.557 ROWS=824 loops=1) INDEX Cond: (word % 'samochody'::text) Total runtime: 948.107 ms (9 ROWS) test=# EXPLAIN analyze SELECT word, similarity(word, 'samochody') AS sml, ndoc, nentry FROM fuzzy_helper WHERE word % 'samochody' AND ndoc > 100 ORDER BY sml DESC, word LIMIT 15; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- LIMIT (cost=1961.39..1961.43 ROWS=15 width=40) (actual TIME=947.310..947.376 ROWS=13 loops=1) -> Sort (cost=1961.39..1961.97 ROWS=233 width=40) (actual TIME=947.304..947.327 ROWS=13 loops=1) Sort KEY: similarity(word, 'samochody'::text), word -> Bitmap Heap Scan ON fuzzy_helper (cost=37.84..1952.23 ROWS=233 width=40) (actual TIME=941.114..947.222 ROWS=13 loops=1) Recheck Cond: (word % 'samochody'::text) FILTER: (ndoc > 100) -> Bitmap INDEX Scan ON trgm_idx (cost=0.00..37.78 ROWS=698 width=0) (actual TIME=940.960..940.960 ROWS=824 loops=1) INDEX Cond: (word % 'samochody'::text) Total runtime: 947.489 ms (9 ROWS)
nie jest za dobrze. ale z drugiej strony – jeśli zawsze stosowalibyśmy warunek dodatkowy typu ndoc > 100 to można by użyć indeksu częściowego. a wyniki wtedy wychodzą takie:
test=# DROP INDEX trgm_idx ; test=# CREATE INDEX trgm_idx ON fuzzy_helper USING gist(word gist_trgm_ops) WHERE ndoc > 100;
czas zakładania – praktycznie pomijalny. rzędu 2 sekundy 🙂
test=# EXPLAIN analyze SELECT word, similarity(word, 'samochody') AS sml, ndoc, nentry FROM fuzzy_helper WHERE word % 'samochody' AND ndoc > 100 ORDER BY sml DESC, word LIMIT 15; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- LIMIT (cost=92.67..92.71 ROWS=15 width=24) (actual TIME=111.551..111.616 ROWS=13 loops=1) -> Sort (cost=92.67..92.73 ROWS=23 width=24) (actual TIME=111.545..111.566 ROWS=13 loops=1) Sort KEY: similarity(word, 'samochody'::text), word -> Bitmap Heap Scan ON fuzzy_helper (cost=4.43..92.15 ROWS=23 width=24) (actual TIME=111.404..111.481 ROWS=13 loops=1) Recheck Cond: ((word % 'samochody'::text) AND (ndoc > 100)) -> Bitmap INDEX Scan ON trgm_idx (cost=0.00..4.43 ROWS=23 width=0) (actual TIME=111.371..111.371 ROWS=13 loops=1) INDEX Cond: (word % 'samochody'::text) Total runtime: 111.723 ms (8 ROWS) test=# EXPLAIN analyze SELECT word, similarity(word, 'samochody') AS sml, ndoc, nentry FROM fuzzy_helper WHERE word % 'samochody' AND ndoc > 100 ORDER BY sml DESC, word LIMIT 15; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- LIMIT (cost=92.67..92.71 ROWS=15 width=24) (actual TIME=98.133..98.215 ROWS=13 loops=1) -> Sort (cost=92.67..92.73 ROWS=23 width=24) (actual TIME=98.127..98.156 ROWS=13 loops=1) Sort KEY: similarity(word, 'samochody'::text), word -> Bitmap Heap Scan ON fuzzy_helper (cost=4.43..92.15 ROWS=23 width=24) (actual TIME=97.894..98.038 ROWS=13 loops=1) Recheck Cond: ((word % 'samochody'::text) AND (ndoc > 100)) -> Bitmap INDEX Scan ON trgm_idx (cost=0.00..4.43 ROWS=23 width=0) (actual TIME=97.849..97.849 ROWS=13 loops=1) INDEX Cond: (word % 'samochody'::text) Total runtime: 98.330 ms (8 ROWS)
miłe. czyż nie?
i na tym chyba zakończę.
trochę się napisałem 🙂
Wielkie dzięki:-) Szukałem czegoś podobnego od pewnego czasu:-)
A tak na marginesie – czyżby powrót – przynajmniej postów dotyczących PostgreSQL-a?
ja też szukałem. w końcu stwierdziłem, że napiszę sobie jak to się od początku do końca zestawia 🙂
co do powrotu. nie wiem. mam trochę więcej czasu, ale nie chcę nic obiecywać.
OK. Nic regularnego:-) Od czasu do czasu. Twoje posty dotyczące PostgreSQL-a są że się tak wyrażę unikalne na skalę kraju;-) Przez co wartościowe. A z czasem jak to z czasem;-) Sam też mam go mało – mam nadzieje że już niedługo będzie z tym trochę lepiej i sam zadbam nieco bardziej o swojego bloga;-)
hm…. jak dodac indexy GiN do postgresa? o ile poradzilem Sobie z GIST uruchamiajac contrib/btree_gist.sql to nie moge znalesc sposobu na GiN’a ;/
gista nie musisz dodawać. jest od razu.
a gin – jest w 8.2.
to co dodałeś z contriba to implementacja btree na gistach – zasadniczo zbędne.
depesz
eh moglem od razu podać błąd jaki otrzymuje:
ERROR: data type text has no default operator class for access method “gin”
Stan SQL:42704
Wskazówka:You must specify an operator class for the index or define a default operator class for the data type.
przebrnalem przez dokumenacje i za bardzo nie umiem tego ustawic
ten indeks zaklada sie nie na polu z textem tylko z tsvectorem.
> alter table test add column fts tsvector;
to pole trzeba aktualizować triggerem.
depesz
Niestety zaraz po konfiguracji gdy sprawdzam czy działa select * from ts_debug(‘test’) dostaję komunikat ERROR: Affix parse error at 214 line
CONTEXT: SQL function “ts_debug” statement 1
jakieś wskazówki?
niestety – nie spotkałem się nigdy z niczym takim 🙁
Mam pewne podejrzenia że to coś z konfiguracją Postgresa albo nawet całego systemu (coś z kodowaniem) ale jak narazie bez zmian.
Jakkolwiek dzięki za tutorial, bardzo przydatna sprawa.
ten sam problem miałem na windowsie, instalacja i uruchomienie na linuxie poszlo bez wiekszego ALE
Jednak konfiguracja systemu powodowała błąd, na innym linuxie z dobrą konfiguracją poszło bez problemów 🙂
No chyba że było coś nie tak z ispellem – kilka dni temu ściągałem wersje z 200707… i z tą były problemy a następnego dnia do ściągnięcia była spowrotem 2006… i ta już działała.
Przerwałem tworzenie indeksu CREATE INDEX gin_idx ON test USING gin(fts); i nie moge teraz usunąć tabeli na której był tworzony indeks.
Nie moge tez usunąć indeksu :/
Bede wdzięczny za pomoc.
co znaczy nie mogę?
jaki pojawia się komunikat błędu?
co w logach?
czy restartowałeś postgresa?
Nie moge = pusto, po 30 min nadal żadnego odzewu, pomaga tylko ctrl+c następstwem czego jest komunikat o przerwanym procesie.
Mam ograniczony dostęp do bazy (serwer jest w pracy a ja nie jestem adminem).
Aktualnie poinformowałem admina, możliwe że restart wystarczy.
jak brak odzewu to zobacz czy przypadkiem na jakis lockach nie wisi.
mozliwe, ze proces “create index” zostal
trzeba by go wtedy zkillować.
^ Pomógł restart ^
Zostały jeszcze conajmniej dwa problemy:
1. Dowolny kod SQL wywołany bezpośrednio w bazie z explain analyze daje czas np 0.4ms, ten sam kod uruchomiony w PHP via pg_exec daje czas 1.2sek – czy te czasy powinny być porównywalne? Jeżeli tak to co może być przyczyną takiego wolnego działania pod PHP?
2. Analizuję dopiero fuzzy-searching ale jeszcze nie doszedłem do tego w jaki najlepszy sposób powiązać go z tsearchem? Chodzi mi o konkretny kod SQL.
Będę wdzięczny za pomoc.
1. napisz może na grupę pl.comp.bazy-danych.
staram się pomagać, ale mój blog to tak nie do końca helpdesk.
2. nie mam pojęcia. php nie dotykam.
3. fuzzy search – zapytaniem do tabelki fuzzy_helper znajdujesz podobne słowa. załóżmy, że bierzesz 5 pierwszych.
potem z nich robisz query w ten sposób:
fts @@ plainto_tsquery(‘slowo1 | slowo2 | slowo3 | slowo4 | slowo5’);
albo podobnie – nie mam teraz pod ręką nigdzie instalacji tsearcha więc nie mogę sprawdzić czy to na pewno taka jest składnia.
może nie plainto_tsquery tylko np. ‘slowo1 | slowo2 | …’::tsquery ? nie pamiętam.
w manualu do tsearcha na http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html
jest taki przykład:
=# SELECT to_tsquery(‘(leads|forks) & !crawl’)
Może i nie helpdesk ale gdyby nie Twój artykuł byłoby troche więcej czasu straconego na analize manuala tsearch’a 😉
Thx
Jeżeli chodzi o błąd z czasami w PHP okazało się że w kilku miejscach brakuje pg_close zamykającego połączenie z bazą.
Mam postgresa 8.2 skompilowanego bez modułów contrib.
W jaki sposób moge dodać tsearcha do tak zainstalowanej bazy?
eh
niet=# sELECT set_curdict(‘pl_ispell’);
ERROR: Affix parse error at 215 line
kurde ciągle mam problem z tym affix, jak wyzeruję plik.aff to jest ok … do niczego …
Depesz miałeś tak ?
S pozdravem abram
@michal abramowicz:
nie. ani razu.
kodowanie się zgadza (utf8/latin) ?
plik jest poprawny?
co jest w tej linii w aff’ie?
niet=# select * from ts_debug(‘Kup’);
ERROR: could not find tsearch config by locale
KONTEKST: SQL function “_get_parser_from_curcfg” statement 1
SQL function “ts_debug” during startup
eh …
kurde gupim. poddaję się.
NA pld@AC pomaga
su – postgres
initdb -E UTF8 –locale=pl_PL.UTF-8 😉
jak mi się uda co i jak szczególnie z błędem affix cośtam (poprawiam plik.aff w lini kŧóry to numer podaje i błąd znika) to teraz może z 1 wersją pliku aff. bedzie lepiej ;-))))
eh.
/me gamoń
Część!
Czy ktoś wie może jak zmusić postgresa, do ładowania słownika ispell “raz a dobrze” a nie przy każdym połączeniu z bazą?
Nigdzie nie mogę tego znaleźć 🙁
Z góry dziękuję za pomoc!
magarac
@magarac:
nie ma takiej opcji.
stosuj cioś do pool-owania połączenień: pgpool lub (lepiej) pgbouncer) wtedy połączenia z aplikacji będą już miały odzyskane połączenia, czyli już z załadowanymi słownikami.
kumam!
piękne dzięki za odpowiedź i za świetny (jedyny taki? 🙂 ) artykuł.
Czy ktoś wie jak wyszukiwać po części wyrazu ??
@damian:
zdefiniuj co dokładnie potrzebujesz.
czy naprawdę po dowolnej części wyrazu? czy może prefixowo (po początkach)? a może po końcach?
czy dane to faktycznie tekst (po co ci wtedy wyszukiwanie po *środkach* wyrazów?). czy może coś innego?
np, instrukcja:
select ‘ala’::tsquery @@ ‘alama kota’::tsvector ;
zwraca false
Czy da się wyszukać tak by leksemy zawierały dane słowo ?
@damian:
tsearchem – nie.
bo niby czemu tsearch ma dzielić jedno słowo na kilka!
możesz zrobić searcha po prefixach słów w samym postgresie.
zobacz choćby tu:
https://www.depesz.com/2007/09/15/speeding-up-like-xxx/
Bardzo fajny artykuł, ale w wersji 8.3 sporo sie pozmieniało i nigdzie nie mogę znaleźć instrukcji jak zainstalować polski pakiet dla wersji 8.3
Mógłbyś udostępnić pliki ispell’a? Zależy mi głównie na tych przekonwertowanych.
nie za bardzo. to jest projekt i pliki ludzi z kurnika, ściągnij od nich – nie chcę by ktokolwiek uznał, że podszywam się pod twórców.
a dodatkowo – jak bym tu udostępnił to bym musiał potem aktualizować jak oni zmienią wersję. wolę podać linka do oryginału, i każdy może ściągnąć aktualną wersję. link był w treści, gdzieś wyżej. ogólnie: http://www.kurnik.pl/dictionary/
@aasat:
wydaje mi się, że nie powinieneś mieć problemu z dostosowaniem tego do 8.3. jak nie – no cóż. teraz nei za bardzo mam natchnienie do przepisywania tego na 8.3, sorry. może kiedyś …
Nie wiem czy ktoś jeszcze tu zaglada.. Ale mam taki problem:
Skonfigurowalam sobie tsearcha zgodnie z instrukcja na serwerze developerskim i smiga pieknie (mam tu na mysli slownik ispella). Problemy sie zaczely, gdy probowalam zrobic DOKLADNIE to samo na produkcji (serwer, db o identycznej konfiguracji co devel). Gdy testuje dzialanie slownika:
select lexize(‘pl_ispell’, ‘tomek’);
na produkcji wyskakuje blad:
ERROR: could not load dictionary file “/home/x/tsearch/polish.med.utf8”
Szukalam wszedzie, myslalam, glowilam sie…plik jest na miejscu, ma prawa dostepu, wyglada w porzadku.. zreszta identyczny jest na develu i dziala..
Ma ktos pomysl co to moze byc?…
@Marysia:
pokaż:
ls -lad / /home /home/x /home/x/tsearch /home/x/tsearch/polish.med.utf8
pokaż:
ls -lad / /home /home/x /home/x/tsearch /home/x/tsearch/polish.med.utf8
prosz:
drwxr-xr-x 26 root root 4096 2008-08-19 17:13 /
drwxr-xr-x 11 root root 288 2008-08-10 12:16 /home
drwxr-xr-x 21 xxx xyz 1160 2008-12-05 10:23 /home/x
drwxr-xr-x 4 xxx xyz 376 2008-11-27 12:09 /home/x/tsearch
-rwxrwxrwx 1 xxx xyz 4436203 2008-11-26 09:30 /home/x/tsearch/polish.med.utf8
@Marysia:
nie wiem. trzeba by popatrzec – strace powinien pokazac w czym jest problem, ale tak zdalnie to nie podajmuje sie zdiagnozowac.
@depesz
czy da się w jakiś sensowny sposób połączyć trgm z tsearch2, chodzi o to aby tsearch szukał również na podstawie błędnie podanego wyrazu ?
@kamil:
tak, ale musisz dodać tabelę ze słowami z tsearcha, na niej używać trgm’a. i po prostu zamiast szukać jednego słowa w tsearchu, to szukać alternatywy słów znalezionych przez trgm.