Fulltext v databáze prakticky, alebo čo vám nadšenci nepovedia

Ne­pat­rím k ľu­ďom, kto­rí za kaž­dú ce­nu mu­sia po­u­ží­vať svo­ju ob­ľú­be­nú tech­no­ló­giu na všet­ko. Prí­stup ty­pu „mám v ru­ke kla­di­vo a všet­ko je te­raz pre mňa kli­niec“ po­va­žu­jem za vy­so­ko kon­tra­pro­duk­tív­ny. Mo­ja sna­ha po­u­žiť da­ta­bá­zu na full­tex­to­vé vy­hľa­dá­va­nie by moh­la pô­so­biť prá­ve ta­kým doj­mom. Aby som rozp­tý­lil po­chyb­nos­ti, na za­čia­tok hneď na­pí­šem, že zvy­čaj­ne pra­cu­jem s po­mer­ne ma­lý­mi da­ta­bá­za­mi rôz­nych elek­tro­nic­kých ob­cho­dov, kde je do­ko­py nie­koľ­ko ti­síc až po stov­ky ti­síc pro­duk­tov. Ke­by som chcel sku­toč­ne kva­lit­né full­tex­to­vé vy­hľa­dá­va­nie nad veľ­kou da­ta­bá­zou, roz­hod­ne by som vy­be­ral špe­cia­li­zo­va­né full­tex­to­vé rie­še­nie, akým je na­prí­klad Elas­tic­se­arch.

Keď však po­tre­bu­jem nie­koľ­ko men­ších pro­jek­tov, nech­cem udr­žia­vať špe­ciál­ny ser­ver na Elas­tic­se­arch, kto­rý mu­sí mať via­cej RAM než vy­ža­du­jú všet­ky os­tat­né we­bo­vé ap­li­ká­cie do­ko­py.

Výkon

V pr­vej čas­ti sa bu­dem za­obe­rať rých­los­ťou odoz­vy pri vy­hľa­dá­va­ní. Ne­bu­de ma zau­jí­mať kva­li­ta a re­le­van­cia vý­sled­kov, pre­to­že v ďal­šej čas­ti si uká­že­me, že je mož­né zvý­šiť kva­li­tu bez zme­ny rých­los­ti.

Tak­tiež ne­bu­dem ro­zo­be­rať rých­losť ak­tu­ali­zá­cie in­de­xov keď­že väč­ši­nou sa full­tex­to­vá da­ta­bá­za omno­ho čas­tej­šie pre­hľa­dá­va než za­pi­su­je. V prí­pa­de, že by iš­lo sku­toč­ne o da­ta­bá­zu, kto­rá po­tre­bu­je čas­té zá­pi­sy, od­po­rú­čal by som ne­ak­tu­ali­zo­vať in­dex au­to­ma­tic­ky, ale spúš­ťať ak­tu­ali­zá­ciu až po ur­či­tom po­čte zmien / ča­se.

Ako da­ta­bá­zo­vý ser­ver bu­dem po­u­ží­vať Ma­riaDB 10.6 a Post­gre­SQL 15.2.

Skúšobné dáta

Aby bo­li tes­ty ako-tak re­a­lis­tic­ké, expor­to­val som re­ál­ne dá­ta z Li­nu­xOS.sk. Sú­bor li­nu­xo­s_tex­ty.csv.xz ob­sa­hu­je 81 MB tex­tu vo for­me CSV ta­buľ­ky s čís­lom do­ku­men­tu v pr­vom stĺp­ci a tex­tom v dru­hom.

Vytvorenie databázy

Da­ta­bá­za Ma­riaDB sa vy­tvo­rí na­sle­du­jú­cou sé­ri­ou prí­ka­zov:

SET NAMES utf8mb4;
CREATE DATABASE fulltext_test;
ALTER DATABASE fulltext_test CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
USE fulltext_test;
CREATE TABLE ft(
	id INT NULL AUTO_INCREMENT PRIMARY KEY,
	document LONGTEXT
);

Skú­šob­né úda­je sa na­hra­jú prí­ka­zom:

LOAD DATA INFILE "linuxos_texty.csv"
INTO TABLE ft
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';

Post­gre­SQL da­ta­bá­za sa vy­tvo­rí prí­kaz­mi:

CREATE DATABASE fulltext_test;
\c fulltext_test;
CREATE TABLE ft (
	id SERIAL,
	document TEXT,
	PRIMARY KEY(id)
);

-- Query OK, 34760 rows affected (0,889 sec)
-- Records: 34760  Deleted: 0  Skipped: 0  Warnings: 0

Pre na­čí­ta­nie dát po­mo­cou psql po­tom slú­ži prí­kaz:

\copy ft FROM linuxos_texty.csv DELIMITER ',' CSV;

-- COPY 34760
-- Time: 1725,099 ms (00:01,725)

Vyhľadávanie bez indexu

Full­tex­to­vé vy­hľa­dá­va­nie bez vy­tvo­re­né­ho in­de­xu mô­že slú­žiť ako cel­kom zau­jí­ma­vá re­fe­renč­ná hod­no­ta. Ak sa rých­losť vy­hľa­dá­va­nia bu­de blí­žiť rých­los­ti bez in­de­xu, bu­de to prav­de­po­dob­ne zna­me­nať, že in­de sa ne­pou­žil.

Vy­hľa­dá­vam jed­no z naj­čas­tej­ších slov na li­nu­xo­vom por­tá­le. Vy­be­rám frek­ven­to­va­né slo­vo, aby sa ne­skôr pre­ja­vi­la opti­ma­li­zá­cia pri po­u­ži­tí klau­zu­ly LIMIT.

Pre My­SQL po­u­ži­jem na­sle­du­jú­ci se­lect:

SELECT
	COUNT(*)
FROM ft
WHERE match(document) AGAINST ('+linux' IN BOOLEAN MODE);

-- ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

My­SQL (Ma­riaDB) ne­pod­po­ru­je vy­hľa­dá­va­nie bez in­de­xu. Ako jed­no­du­chú al­ter­na­tí­vu k full­tex­tu je mož­né po­u­žiť LIKE:

SELECT
	COUNT(*)
FROM ft
WHERE document LIKE "%linux%";
+----------+
| COUNT(*) |
+----------+
|    17932 |
+----------+
1 row in set (0,276 sec)

Post­gre­SQL pod­po­ru­je full­tex­to­vé vy­hľa­dá­va­nie aj bez in­de­xu. Je však ex­trém­ne po­ma­lé a v pra­xi ne­pou­ži­teľ­né.

SELECT
	COUNT(*)
FROM ft
WHERE to_tsvector('simple', document) @@ to_tsquery('simple', 'linux');

-- count
-- -----
-- 10531
-- (1 row)
--
-- Time: 3810,541 ms (00:03,811)

Na po­rov­na­nie eš­te pri­dá­vam vý­sled­ky LIKE s Post­gre­SQL:

SELECT
	COUNT(*)
FROM ft
WHERE document LIKE '%linux%';

-- count
-- -----
-- 14147
-- (1 row)
--
-- Time: 162,113 ms

Aby bol po­čet riad­kov rov­na­ký, je po­treb­né po­u­žiť klau­zu­lu ILIKE, kto­rá ne­roz­li­šu­je veľ­kosť pís­men.

SELECT
	COUNT(*)
FROM ft
WHERE document ILIKE '%linux%';

-- count
-- -----
-- 17932
-- (1 row)
--
-- Time: 646,595 ms

Vytvorenie indexu

Vy­hľa­dá­va­nie v My­SQL má viac-me­nej roz­um­né štan­dard­né na­sta­ve­nie, ne­má žiad­nu mož­nosť zme­niť pa­ra­met­re. Pre­to je vy­tvo­re­nie in­de­xu veľ­mi jed­no­du­ché.

CREATE FULLTEXT INDEX document_idx ON ft(document);
Query OK, 0 rows affected (4,699 sec)
Records: 0  Duplicates: 0  Warnings: 0

Vy­tvo­re­nie in­de­xu v Post­gre­SQL sa zá­sad­ne lí­ši. Hlav­né roz­die­ly sú:

• tex­to­vý stĺpec ne­mô­že mať full­tex­to­vý in­dex
• je mož­né vy­brať typ in­de­xu
• je mož­né vy­brať kon­fi­gu­rá­ciu full­tex­to­vé­ho vy­hľa­dá­va­nia

Full­tex­to­vé vy­hľa­dá­va­nie v Post­gre­SQL po­u­ží­va špe­ciál­ny typ stĺp­ca tsvector. Pr­vé vy­hľa­dá­va­nie bo­lo po­ma­lé, pre­to­že kaž­dý do­ku­ment mu­sel byť kon­ver­to­va­ný fun­kci­ou to_tsvector. Vý­sle­dok sa na­po­kon po­rov­ná­va s do­ta­zom ty­pu tsquery.

In­de­xy v Post­gre­SQL ne­vy­ža­du­jú exis­ten­ciu stĺp­ca a da­jú sa vy­tvo­riť na­prí­klad na vo­la­nie fun­kcie. Na­sle­du­jú­ce vo­la­nie vy­tvo­rí in­dex ty­pu GIN nad fun­kci­ou to_tsvector s kon­fi­gu­rá­ci­ou simple.

CREATE INDEX document_idx
	ON ft
	USING GIN (to_tsvector('simple', document));

-- Time: 11936,167 ms (00:11,936)

Vyhľadávanie

V na­sle­du­jú­cich tes­toch bu­de po­u­ži­té bež­né slo­vo na Li­nu­xo­vom por­tá­li - li­nux, zried­ka­vé slo­vo zá­vis­losť a ich kom­bi­ná­cia s ope­rá­to­rom AND, te­da v do­ku­men­te sa mu­sia vy­sky­to­vať oba slo­va. Tes­ty sa ro­bia 3x za se­bou. Do gra­fu sa za­kres­lí naj­lep­ší vý­sle­dok.

Do­ta­zy pre My­SQL a Post­gre­SQL vy­ze­ra­jú na­sle­dov­ne:

-- MySQL
SELECT COUNT(*) FROM ft WHERE match(document) AGAINST ('+linux' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|    11457 |
+----------+

-- PostgreSQL
SELECT COUNT(*) FROM ft WHERE to_tsvector('simple', document) @@ to_tsquery('simple', 'linux');
count
-----
10531

Kom­bi­no­va­ný vý­raz má tvar +linux +závislosť v My­SQL a linux & závislosť v Post­gre­SQL. Do vý­sled­kov som za­hr­nul aj RUM in­dex, ku kto­ré­mu sa vrá­tim ne­skôr.

Aby bo­lo v gra­fe vô­bec nie­čo vi­dieť, mu­sím tro­cha zre­zať stĺpec My­SQL.

Zoradenie podľa relevancie

Vrá­te­nie všet­kých vý­sled­kov nie je ty­pic­ké pre full­tex­to­vé vy­hľa­dá­va­nie. Omno­ho čas­tej­šie chce po­u­ží­va­teľ vi­dieť nie­koľ­ko pr­vých vý­sled­kov zo­ra­de­ných pod­ľa re­le­van­cie.

Než sa pus­tím do sa­mot­né­ho tes­tu vy­tvo­rím v Post­gre­SQL re­ál­ny tsvector stĺpec, kto­rý sa bu­de au­to­ma­tic­ky ak­tu­ali­zo­vať pri zme­ne do­ku­men­tu. Syn­tax se­lec­tov bu­de po tom­to kro­ku mier­ne jed­no­duch­šia, pre­to­že už ne­bu­de po­treb­né vo­la­nie to_tsvector.

DROP INDEX document_idx;
ALTER TABLE ft
	ADD COLUMN document_tsvector tsvector
	GENERATED ALWAYS AS (to_tsvector('simple', document)) STORED;
CREATE INDEX document_idx
	ON ft
	USING GIN (document_tsvector);

Vý­pis 10 vý­sled­kov zo­ra­de­ných po­ľa re­le­van­cie vy­ze­rá v My­SQL na­sle­dov­ne:

SELECT
	id,
	match(document) AGAINST ('+linux' IN BOOLEAN MODE) AS score
FROM ft
WHERE match(document) AGAINST ('+linux' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 10;

+-------+--------------------+
| id    | score              |
+-------+--------------------+
|  1165 | 113.36463165283203 |
| 24467 | 112.66340637207031 |
|  6899 |   84.8481674194336 |
|  5973 |  78.53714752197266 |
| 24940 |  64.04517364501953 |
| 14574 |  61.94150161743164 |
|   482 | 55.162994384765625 |
| 15541 |  52.12435531616211 |
| 11346 |  50.72190856933594 |
| 14764 | 48.384490966796875 |
+-------+--------------------+
10 rows in set (0,372 sec)

EXPLAIN SELECT id, MATCH(document) AGAINST ('+linux' IN BOOLEAN MODE) AS score FROM ft WHERE match(document) AGAINST ('+linux' IN BOOLEAN MODE) ORDER BY score DESC, id ASC LIMIT 10;
+------+-------------+-------+----------+---------------+--------------+---------+------+------+----------------------------------------------+
| id   | select_type | table | type     | possible_keys | key          | key_len | ref  | rows | Extra                                        |
+------+-------------+-------+----------+---------------+--------------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | ft    | fulltext | document_idx  | document_idx | 0       |      | 1    | Using where; Using temporary; Using filesort |
+------+-------------+-------+----------+---------------+--------------+---------+------+------+----------------------------------------------+

Ten is­tý do­taz v Post­gre­SQL vy­ze­rá tak­to:

SELECT
	id,
	ts_rank(document_tsvector, to_tsquery('simple', 'linux')) AS rank
FROM ft
WHERE document_tsvector @@ to_tsquery('simple', 'linux')
ORDER BY rank
DESC LIMIT 10;

 id      rank

----- -----------
24940  0.09976207
 1165  0.09976207
14574  0.09976207
 5973  0.09976207
24467  0.09976207
15541  0.09972675
11346  0.09972046
 6899  0.09967747
  482 0.099663176
23873  0.09965749
(10 rows)

Time: 66,747 ms


EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT id, ts_rank(document_tsvector, to_tsquery('simple', 'linux')) AS rank FROM ft WHERE document_tsvector @@ to_tsquery('simple', 'linux') ORDER BY rank DESC LIMIT 10;
                                  QUERY PLAN
-------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Sort (actual rows=10 loops=1)
        Sort Key: (ts_rank(document_tsvector, '''linux'''::tsquery)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on ft (actual rows=10531 loops=1)
              Recheck Cond: (document_tsvector @@ '''linux'''::tsquery)
              Heap Blocks: exact=4021
              ->  Bitmap Index Scan on document_idx (actual rows=10531 loops=1)
                    Index Cond: (document_tsvector @@ '''linux'''::tsquery)
Planning Time: 0.115 ms
Execution Time: 60.946 ms

Z vý­pi­su My­SQL sa ne­doz­vie­me nič zau­jí­ma­vé ok­rem to­ho, že do­taz sa vy­ko­ná­va nie­koľ­ko­ná­sob­ne po­mal­šie. Vý­pis Post­gre­SQL je však omno­ho zau­jí­ma­vej­ší.

Z vý­pi­su vy­plý­va, že in­dex vrá­til 10 531 vý­sled­kov. Z nich sa po­mo­cou dá­to­vej štruk­tú­ry he­ap vy­bra­lo 10 naj­re­le­van­tnej­ších vý­sled­kov po­stup­ným ske­no­va­ním cez všet­kých re­le­vant­ných 10 531 riad­kov. Ak by ne­bol po­u­ži­tý LIMIT, ale­bo by bol prí­liš vy­so­ký, Post­gre­SQL by zvo­lil pria­mo stra­té­giu zo­ra­de­nia vý­sled­kov bez vý­be­ru naj­čas­tej­ších cez he­ap.

Kým v pr­vom tes­te bol rá­do­vý roz­diel me­dzi My­SQL a Post­gre­SQL, v tom­to tes­te to už na ta­kú do­mi­nan­ciu ne­vy­ze­rá.

Prístupová metóda RUM

Vrá­te­nie vý­sled­kov v Post­gre­SQL je rých­le, ale zo­ra­de­nie vý­sled­kov pod­ľa re­le­van­cie je po­ma­lé. Prob­lém zo­ra­de­nia rie­ši RUM prí­stu­po­vá me­tó­da.

Roz­ší­re­nie nie je sú­čas­ťou štan­dard­nej in­šta­lá­cie Post­gre­SQL, ale väč­ši­nou je do­stup­né v dis­tri­buč­ných ba­lí­koch. Li­cen­cia je rov­na­ká, ako v Post­gre­SQL. Po in­šta­lá­cii je vý­me­na GIN in­de­xu veľ­mi jed­no­du­chá:

DROP INDEX document_idx;
CREATE EXTENSION rum;
CREATE INDEX document_idx
	ON ft
	USING RUM(document_tsvector rum_tsvector_ops);

Do­taz pre zo­ra­de­nie po­u­ží­va tro­chu iný spô­sob vý­poč­tu re­le­van­cie. Upra­ve­ný do­taz vy­ze­rá tak­to:

SELECT
	id,
	document_tsvector <=> to_tsquery('simple', 'linux') AS "rank"
FROM ft
WHERE document_tsvector @@ to_tsquery('simple', 'linux')
ORDER BYrank
LIMIT 10;

 id     rank
----- ---------
24467  10.02385
14574  10.02385
24940  10.02385
 5973  10.02385
 1165  10.02385
15541 10.027397
11346 10.028029
 6899 10.032354
  482 10.033794
23873 10.034368
(10 rows)

Time: 5,757 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT id, document_tsvector <=> to_tsquery('simple', 'linux') AS "rank" FROM ft WHERE document_tsvector @@ to_tsquery('simple', 'linux') ORDER BY rank LIMIT 10;
                            QUERY PLAN
------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Index Scan using document_idx on ft (actual rows=10 loops=1)
        Index Cond: (document_tsvector @@ '''linux'''::tsquery)
        Order By: (document_tsvector <=> '''linux'''::tsquery)
Planning Time: 0.155 ms
Execution Time: 7.598 ms

Pod­ľa vý­pi­su explain sa vy­uži­lo pres­ne 10 riad­kov zís­ka­ných rých­lym in­dex ske­nom.

To je 64-ná­sob­ne rých­lej­šie opro­ti My­SQL a sko­ro 12-ná­sob­ne rých­lej­šie opro­ti GIN in­de­xu pri čas­tých slo­vách.

Stabilné zoradenie alebo Huston, máme problém

Da­ta­bá­zo­vý sys­tém Post­gre­SQL mô­že pod­ľa po­tre­by me­niť a spra­vid­la aj me­ní pri rôz­nych hod­no­tách LI­MIT a OFF­SET stra­té­giu prí­stu­pu k riad­kom. Ak sú riad­ky zo­ra­de­né iba pod­ľa ran­ku, mô­že sa po­ra­die riad­kov s rov­na­kým ran­kom zme­niť. Za­me­dziť sa to­mu dá na­prí­klad pri­da­ním id do zo­ra­de­nia - ORDER BY rank, id.

SELECT
	id,
	document_tsvector <=> to_tsquery('simple', 'linux') AS "rank"
FROM ft
WHERE document_tsvector @@ to_tsquery('simple', 'linux')
ORDER BY rank, id
LIMIT 10;

…

Time: 64,808 ms

Zra­zu sa vý­ho­da RUM prí­stu­po­vej me­tó­dy nie­kde stra­ti­la.

Stabilné zoradenie

Mys­lím, že prob­lém zo­ra­de­nia je do­sť zau­jí­ma­vý na to, aby som mu ve­no­val jed­nu ma­lú ka­pi­to­lu. Tá sa ne­bu­de tý­kať pria­mo full­tex­to­vé­ho vy­hľa­dá­va­nia, ale bu­de o zo­ra­de­ní vše­obec­ne.

Tes­ty bu­dú pre­bie­hať ne jed­no­du­chej ta­buľ­ke so stĺp­ca­mi ID, čís­lo s plá­va­jú­cou de­sa­tin­nou čiar­kou a bod na plo­che. V ta­buľ­ke bu­de 10 000 000 ná­hod­ných zá­zna­mov. Kaž­dý stĺpec bu­de mať in­dex. Naj­skôr za­čnem My­SQL:

Ta­buľ­ka je vy­tvo­re­ná za 47 se­kúnd a in­de­xy za ďal­ších 43 se­kúnd. Vy­tvo­re­nie ná­hod­ných dát si môž­me skon­tro­lo­vať:

SELECT COUNT(*) FROM s;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1,293 sec)

SELECT * FROM s LIMIT 10;
+----+---------------------+---------------------------+
| id | d                   | p                         |
+----+---------------------+---------------------------+
|  1 |  0.9137433598877335 |        â¶]w¸mç?ɱu@rlí?       |
|  2 | 0.40100952368323645 |        /@~º
                                        Ï?zlzp?      |
|  3 |  0.4096927758396536 |        å¹úTy®î?
                                            OȦÂâ?         |
|  4 |   0.948166525874442 |        ¯g^Ø뙧?®Ïbë³Ø?          |
|  5 |   0.791618720434251 |        JkfҚé?+PÊJä?      |
|  6 |  0.7290030342796846 |        Z8âè?Ù)SMvÊä?        |
|  7 |  0.9465388887995285 |        cKäÆØé?PlÁ ´?       |
|  8 | 0.04003077656033521 |        {¥`çî?^´Zm­æ?         |
|  9 |  0.6459137253890873 |        C+j´Њº?#SʸȔâ?       |
| 10 |  0.5922686621474742 |        OpÈãÌ?%êQ!zÔ?        |
+----+---------------------+---------------------------+

My­SQL ne­vie vy­pí­sať typ point, pre­to sa na­mies­to hod­nôt zo­bra­zu­jú bi­nár­ne dá­ta.

Po­dob­ná ta­buľ­ka sa v Post­gre­SQL vy­tvo­rí na­sle­du­jú­cou sé­ri­ou prí­ka­zov:

CREATE TABLE s (id SERIAL, d DOUBLE PRECISION, p POINT, PRIMARY KEY(id));
INSERT INTO s (SELECT generate_series(1, 10000000), random(), point(random(), random()));
-- Time: 12890,828 ms (00:12,891)
CREATE INDEX d_idx ON s USING btree(d);
-- Time: 3037,142 ms (00:03,037)
CREATE INDEX p_idx ON s USING gist(p);
-- Time: 13632,420 ms (00:13,632)

Opäť na­sle­du­je kon­tro­la úda­jov:

SELECT COUNT(*) FROM s;
 count
--------
10000000
(1 row)

Time: 145,828 ms

SELECT * FROM s LIMIT 10;
id          d                              p
-- ------------------- ------------------------------------------
 1 0.44124624029167525 (0.5581773825358121,0.308156906191311)
 2 0.09759067947576683 (0.07393451293407116,0.027710011644558552)
 3 0.18220250470085753 (0.2150454304704421,0.0663700457660914)
 4 0.14895286756377923 (0.8180180986740628,0.9082178023263434)
 5   0.969328504585125 (0.8755523928021021,0.3570828194116715)
 6   0.930351821656485 (0.22167092351788575,0.3140584019365069)
 7 0.12802135544719762 (0.21196181026395955,0.8765903299419724)
 8  0.9919567843128472 (0.5165731957649631,0.38102477994165995)
 9  0.3887398838466707 (0.8947758235085952,0.16025279565670614)
10  0.5337890264197422 (0.36275067768781977,0.8799555047323149)
(10 rows)

Time: 0,448 ms

Výpis údajov zoradených podľa stĺpca

Naj­jed­no­duch­šie, čo môž­me spra­viť je vy­pí­sať si úda­je zo­ra­de­né pod­ľa stĺp­ca so zo­ra­de­ným in­de­xom. V My­SQL je to veľ­mi rých­la ope­rá­cia, kto­rá vy­uží­va in­dex:

SELECT d, id FROM s ORDER BY d DESC LIMIT 10;
+-------------------+---------+
| d                 | id      |
+-------------------+---------+
| 0.999999949708581 | 8815993 |
| 0.999999949708581 | 8716153 |
| 0.999999949708581 | 7083193 |
| 0.999999949708581 | 5502393 |
| 0.999999949708581 | 5068153 |
| 0.999999949708581 | 1854393 |
| 0.999999376945197 | 4773466 |
| 0.999999376945197 | 4578586 |
| 0.999999376945197 | 4244186 |
| 0.999999376945197 | 2979866 |
+-------------------+---------+
10 rows in set (0,000 sec)


EXPLAIN SELECT d, id FROM s ORDER BY d DESC LIMIT 10;
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|    1 | SIMPLE      | s     | index | NULL          | d_idx | 9       | NULL | 10   | Using index |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0,000 sec)

Post­gre­SQL ro­bí v zá­sa­de to is­té:

SELECT d, id FROM s ORDER BY d DESC LIMIT 10;
        d            id
------------------ -------
0.9999999729912039 7971458
0.9999996724381648 5372047
 0.999999547273573 3593807
0.9999995099932779 2321234
0.9999994120221605 5259286
0.9999993095493327 3329543
 0.999999264140115 2294627
0.9999991310452243 2958705
0.9999990016531421 7973130
0.9999989705945129 5978095
(10 rows)

Time: 0,443 ms


EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT d, id FROM s ORDER BY d DESC LIMIT 10;
                            QUERY PLAN
-------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Index Scan Backward using d_idx on s (actual rows=10 loops=1)
Planning Time: 0.098 ms
Execution Time: 0.087 ms

Obe da­ta­bá­zy si so se­lec­tom po­ra­di­li bez prob­lé­mov.

Zoradenie podľa 2 stĺpcov

Pri full­tex­to­vom vy­hľa­dá­va­ní sa prob­lém pre­ja­vil až pri po­ku­se o zo­ra­de­nie pod­ľa 2 stĺp­cov. Poď­me si vy­skú­šať zo­ra­de­nie pod­ľa 2 oby­čaj­ných stĺp­cov bez full­tex­tu. Naj­skôr My­SQL:

SELECT
	d,
	id
FROM s
ORDER BY d DESC, id
LIMIT 10;
+-------------------+---------+
| d                 | id      |
+-------------------+---------+
| 0.999999949708581 | 1854393 |
| 0.999999949708581 | 5068153 |
| 0.999999949708581 | 5502393 |
| 0.999999949708581 | 7083193 |
| 0.999999949708581 | 8716153 |
| 0.999999949708581 | 8815993 |
| 0.999999376945197 |   79386 |
| 0.999999376945197 |   91866 |
| 0.999999376945197 | 2979866 |
| 0.999999376945197 | 4244186 |
+-------------------+---------+
10 rows in set (2,467 sec)

EXPLAIN SELECT d, id FROM s ORDER BY d DESC, id LIMIT 10;
+------+-------------+-------+-------+---------------+-------+---------+------+---------+-----------------------------+
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows    | Extra                       |
+------+-------------+-------+-------+---------------+-------+---------+------+---------+-----------------------------+
|    1 | SIMPLE      | s     | index | NULL          | d_idx | 9       | NULL | 9712432 | Using index; Using filesort |
+------+-------------+-------+-------+---------------+-------+---------+------+---------+-----------------------------+
1 row in set (0,000 sec)

Zra­zu pri tak­mer iden­tic­kých vý­sled­koch tr­vá do­taz 2,467 s a po­tre­bu­je ske­no­vať 9712432 riad­kov. Zau­jí­ma­vé je, že ak oto­čím zo­ra­de­nie pri ID, rých­losť sa zá­sad­ne zme­ní, pri­čom oba stĺp­ce ma­jú na se­be ne­zá­vis­lé in­de­xy.

SELECT d, id FROM s ORDER BY d DESC, id DESC LIMIT 10;
…
10 rows in set (0,000 sec)

EXPLAIN SELECT d, id FROM s ORDER BY d DESC, id DESC LIMIT 10;
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|    1 | SIMPLE      | s     | index | NULL          | d_idx | 9       | NULL | 10   | Using index |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0,000 sec)

Pri rov­na­kom za­da­ní Post­gre­SQL do­ká­že vy­uží­vať in­de­xy aj keď ma­jú opač­né zo­ra­de­nie:

SELECT
	d,
	id
FROM s
ORDER BY d DESC, id
LIMIT 10;
        d            id
------------------ -------
0.9999999729912039 7971458
0.9999996724381648 5372047
 0.999999547273573 3593807
0.9999995099932779 2321234
0.9999994120221605 5259286
0.9999993095493327 3329543
 0.999999264140115 2294627
0.9999991310452243 2958705
0.9999990016531421 7973130
0.9999989705945129 5978095
(10 rows)

Time: 0,547 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE)  SELECT d, id FROM s ORDER BY d DESC, id LIMIT 10;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Incremental Sort (actual rows=10 loops=1)
        Sort Key: d DESC, id
        Presorted Key: d
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        ->  Index Scan Backward using d_idx on s (actual rows=11 loops=1)
Planning Time: 0.109 ms
Execution Time: 0.080 ms

Z vý­pi­su vy­plý­va, že in­dex scan v spä­tom sme­re vrá­til len 11 zá­zna­mov. Tie bo­li ná­sled­ne zo­ra­de­né me­tó­dou in­kre­men­tál­ne­ho zo­ra­de­nia, či­že zo­ra­dia sa len tie, kto­ré rov­na­kú hod­no­tu v stĺp­ci d.

Zoradenie podľa vzdialenosti

Bež­nou po­žia­dav­kou je zo­bra­ze­nie najb­liž­ších bo­dov v oko­lí. Pri tes­toch sa ge­ne­ro­va­li ná­hod­ne bo­dy v roz­sa­hu 0-1 na x-ovej aj y-ovej osi. Úda­je sa bu­dú zo­ra­ďo­vať pod­ľa vzdia­le­nos­ti od stre­du. Naj­skôr do­taz v My­SQL, kto­rý ani ne­bu­dem ro­zo­be­rať, le­bo ani tie jed­no­du­ché do­ta­zy ne­fun­go­va­li dob­re.

SELECT ST_DISTANCE(p, POINT(0.5, 0.5)) dist, id FROM s ORDER BY dist LIMIT 10;
…
10 rows in set (5,301 sec)

Rov­na­ký do­taz v Post­gre­SQL pre­beh­ne pod­ľa oča­ká­va­nia cel­kom rých­lo:

SELECT
	p <-> point(0.5, 0.5) dist,
	id
FROM s
ORDER BY dist
LIMIT 10;
…
Time: 0,650 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT p <-> point(0.5, 0.5) dist, id FROM s ORDER BY dist LIMIT 10;
                        QUERY PLAN
----------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Index Scan using p_idx on s (actual rows=10 loops=1)
        Order By: (p <-> '(0.5,0.5)'::point)
Planning Time: 0.112 ms
Execution Time: 0.354 ms

GIST In­dex v Post­gre­SQL vie ukla­dať bo­dy do R-tree, čo umož­ňu­je efek­tív­ne pre­hľa­dá­vať bo­dy pod­ľa vzdia­le­nos­ti. In­dex tak sku­toč­ne vrá­ti najb­liž­šie bo­dy bez nut­nos­ti do­da­toč­né­ho zo­ra­de­nia. Čo sa však sta­ne keď je po­ža­do­va­né za­ra­de­nie bo­dov s rov­na­kou vzdia­le­nos­ťou pod­ľa dru­hé­ho stĺp­ca?

SELECT
	p <-> point(0.5, 0.5) dist,
	id
FROM s
ORDER BY dist, id
LIMIT 10;

Time: 420,092 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT p <-> point(0.5, 0.5) dist, id FROM s ORDER BY dist, id LIMIT 10;
                       QUERY PLAN
--------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Sort (actual rows=10 loops=1)
        Sort Key: ((p <-> '(0.5,0.5)'::point)), id
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on s (actual rows=10000000 loops=1)
Planning Time: 0.080 ms
Execution Time: 1151.684 ms

Ne­exis­tu­je ob­jek­tív­ny dô­vod, pre­čo by Post­gre­SQL ne­ma­la po­u­ží­vať rov­na­kú me­tó­du zo­ra­de­nia Inc­re­men­tal Sort, ako v pred­chá­dza­jú­com prí­pa­de. Na­priek to­mu ho ne­pou­ží­va.

Strá­vil som nie­koľ­ko ho­dín hra­ba­ním sa v zdro­jo­vých kó­doch Post­gre­SQL, aby som ob­ja­vil dô­vod. V pr­vom ra­de by som rád po­chvá­lil vý­vo­já­rov za veľ­mi pre­hľad­ný kód, v kto­rom som ne­mal ani zďa­le­ka tak veľ­ký prob­lém zo­rien­to­vať sa než som pô­vod­ne ča­kal.

Na­ko­niec prí­či­nou prob­lé­mu je to, že Post­gre­SQL vie po­u­žiť Inc­re­men­tal Sort len v prí­pa­de keď po­u­ží­va zo­ra­de­ný in­dex. Len­že in­dex bo­dov nie je zo­ra­de­ný, pre­to­že tu zo­ra­de­nie ani ne­dá­va zmy­sel. Čo však dá­va zmy­sel je zo­ra­de­nie pod­ľa vzdia­le­nos­ti vo­či re­fe­renč­né­mu bo­du. In­dex, ale­bo prí­stu­po­vá me­tó­da (ac­cess met­hod) na to im­ple­men­tu­je or­der ope­ra­tor.

Je­di­nou chy­bou je, že kým pri zo­ra­de­nom in­de­xe sa kon­tro­lu­je, či in­dex má spo­loč­ný pre­fix s ORDER klau­zu­lou, pri ne­zo­ra­de­nom in­de­xe s or­der ope­rá­to­rom sa kon­tro­lu­je exakt­ná zho­da ORDER klau­zu­ly. Prob­lém sa dá vy­rie­šiť ma­lým pat­chom, kto­rý som za­slal vý­vo­já­rom. Po je­ho ap­li­ká­cii vy­ze­ra­jú vý­sled­ky tak­to:

SELECT
	p <-> point(0.5, 0.5) dist,
	id
FROM s
ORDER BY dist, id
LIMIT 10;
…

Time: 0,707 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT p <-> point(0.5, 0.5) dist, id FROM s ORDER BY dist, id LIMIT 10;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Incremental Sort (actual rows=10 loops=1)
        Sort Key: ((p <-> '(0.5,0.5)'::point)), id
        Presorted Key: ((p <-> '(0.5,0.5)'::point))
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        ->  Index Scan using p_idx on s (actual rows=11 loops=1)
              Order By: (p <-> '(0.5,0.5)'::point)
Planning Time: 0.121 ms
Execution Time: 0.356 ms

Výkon vo fulltextovom vyhľadávaní

Po ap­li­ko­va­ní zá­pla­ty fun­gu­je už aj zo­ra­de­nie pod­ľa via­ce­rých stĺp­cov po­mer­ne rých­lo.

SELECT
	id,
	document_tsvector <=> to_tsquery('simple', 'linux') AS "rank"
FROM ft
WHERE document_tsvector @@ to_tsquery('simple', 'linux')
ORDER BY rank, id
LIMIT 10;
…
Time: 5,658 ms

EXPLAIN (COSTS FALSE, ANALYZE TRUE, TIMING FALSE) SELECT id, document_tsvector <=> to_tsquery('simple', 'linux') AS "rank"  FROM ft WHERE document_tsvector @@ to_tsquery('simple', 'linux') ORDER BY rank, id LIMIT 10;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
  ->  Incremental Sort (actual rows=10 loops=1)
        Sort Key: ((document_tsvector <=> '''linux'''::tsquery)), id
        Presorted Key: ((document_tsvector <=> '''linux'''::tsquery))
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        ->  Index Scan using document_idx on ft (actual rows=11 loops=1)
              Index Cond: (document_tsvector @@ '''linux'''::tsquery)
              Order By: (document_tsvector <=> '''linux'''::tsquery)
Planning Time: 0.221 ms
Execution Time: 8.861 ms

Väč­ši­na blo­gov o full­tex­to­vom vy­hľa­dá­va­ní sa za­obe­rá iba vy­hľa­dá­va­ním zried­ka­vých slov, ale­bo sa vô­bec ne­za­obe­rá po­ra­dím vý­sled­kov. Prá­ve kom­bi­ná­cia veľ­ké­ho množ­stva a zo­ra­de­nia je prob­le­ma­tic­ká. Do­vo­lím si tvr­diť, že v tom­to blo­gu som za­šiel ďa­lej než kto­koľ­vek iný, čo skon­či­lo ma­lou, ale ne­vy­hnut­nou úp­ra­vou zdro­jo­vých kó­dov post­gre­su.

Vý­sled­ky uka­zu­jú, že Post­gre­SQL je do­sta­toč­ne vý­kon­nou pre full­tex­to­vé vy­hľa­dá­va­nie na ma­lých až stred­ne veľ­kých da­ta­bá­zach.

My­SQL je ka­pi­to­la sa­ma o se­be. Sťaž­nos­ti na mi­zer­ný opti­ma­li­zá­tor po­čú­vam už naj­me­nej 15 ro­kov a te­raz som sa mo­hol pre­sved­čiť, že je to prav­da do­kon­ca aj pri veľ­mi jed­no­du­chých do­ta­zoch.

Kvalita výsledkov v PostgreSQL

Pr­vá časť člán­ku bo­la za­me­ra­ná len na ma­xi­ma­li­zá­ciu vý­ko­nu. Prav­dou je, že tak­to na­vr­hnu­té vy­hľa­dá­va­nie ne­náj­de ani len vý­raz zá­vis­losť ak ho za­dám bez diak­ri­ti­ky. V tej­to čas­ti uká­žem ako pri vy­hľa­dá­va­ní ig­no­ro­vať diak­ri­ti­ku, či vy­hľa­dá­vať slo­vá so sk­lo­ňo­va­ním.

Trocha teórie

Vy­hľa­dá­va­nie v Post­gre­SQL pre­bie­ha nad špe­ciál­nym dá­to­vým ty­pom tsvector. Ako kon­krét­ne vy­ze­rá si môž­me vy­skú­šať po­u­ži­tím fun­kcie to_tsvector. Ako po­kus­nú ve­tu bu­dem po­u­ží­vať tre­tí ter­mo­dy­na­mic­ký zá­kon:

Zme­na en­tro­pie sú­sta­vy sa pri ľu­bo­voľ­nom izo­ter­mic­kom de­ji pre­bie­ha­jú­com pri tep­lo­te ab­so­lút­nej nu­ly rov­ná nu­le.

Do­taz SELECT to_tsvector('simple', 'Zmena entropie sústavy sa pri ľubovoľnom izotermickom deji prebiehajúcom pri teplote absolútnej nuly rovná nule.') vy­pí­še na­sle­du­jú­cu štruk­tú­ru (do­vo­lil som si ju pre lep­ší pre­hľad pre­for­má­to­vať).

'absolútnej': 12
'deji': 8
'entropie': 2
'izotermickom': 7
'nule': 15
'nuly': 13
'prebiehajúcom': 9
'pri': 5, 10
'rovná': 14
'sa': 4
'sústavy': 3
'teplote': 11
'zmena': 1
'ľubovoľnom': 6

Text bol na­hra­de­ný zo­zna­mom slov a ich po­zí­ci­ou v do­ku­men­te. Ak sa jed­no slo­vo vy­sky­tu­je v nie­koľ­kých exem­plá­roch, má v štruk­tú­re via­cej po­zí­cií.

Dru­há štruk­tú­ra, kto­rá sa po­u­ží­va pri vy­hľa­dá­va­ní je tsquery, čo je štruk­tú­ro­va­ný do­taz. Pri vy­tvo­re­ní do­ta­zu je k dis­po­zí­cii hneď nie­koľ­ko rôz­nych fun­kcií pre zo­stro­je­nie do­ta­zu.

Fun­kcia to_tsquery bo­la po­u­ži­tá už v čas­ti o vý­ko­ne. Fun­kcia po­u­ží­va špe­ciál­nu syn­tax pre za­dá­va­né vý­ra­zy a po­zor, pri chy­be syn­ta­xe vy­ho­dí vý­nim­ku. Keď chcem vy­hľa­dať vý­ra­zy ob­sa­hu­jú­ce na­prí­klad slo­vá "zme­na" a "en­tro­pie" zo­stro­jím do­taz ako to_tsquery('simple', 'zmena & entropie').

SELECT to_tsquery('simple', 'zmena & entropie');
     to_tsquery
--------------------
'zmena' & 'entropie'

Jed­no­duch­šie do­ta­zy sa da­jú do­siah­nuť vo­la­ním plainto_tsquery. Tá­to fun­kcia ne­vy­ha­dzu­je vý­nim­ku a zvo­le­ný vý­raz sa zlo­ží ako ke­by bol me­dzi kaž­dým slo­vom ope­rá­tor AND.

SELECT plainto_tsquery('simple', 'zmena entropie');
  plainto_tsquery
--------------------
'zmena' & 'entropie'

Naj­nov­šou fun­kci­ou je websearch_to_tsquery, kto­rá umož­ňu­je vy­tvo­riť aj zlo­ži­tej­šie do­ta­zy, ale zá­ro­veň ni­kdy ne­vy­ha­dzu­je vý­nim­ku. Vhod­ná je prá­ve na uží­va­te­ľom za­da­ný vstup.

SELECT websearch_to_tsquery('simple', 'zmena entropie');
websearch_to_tsquery
--------------------
'zmena' & 'entropie'

Fun­kcia websearch_to_tsquery umož­ňu­je kom­bi­no­vať vý­ra­zy na­prí­klad s ope­rá­to­rom OR, ale­bo vy­nú­tiť po­ra­die slov, či za­ká­zať ur­či­tý vý­raz vo vy­hľa­dá­va­ní.

-- alebo
SELECT websearch_to_tsquery('simple', 'zmena or entropie');
websearch_to_tsquery
--------------------
'zmena' | 'entropie'

-- presné poradie slov
SELECT websearch_to_tsquery('simple', '"zmena entropie"');
 websearch_to_tsquery
----------------------
'zmena' <-> 'entropie'

-- len dokumenty s výrazom zmena neobsahujúce slovo entropie
websearch_to_tsquery
---------------------
'zmena' & !'entropie'

Zho­da do­ku­men­tu s hľa­da­ným vý­ra­zom sa kon­tro­lu­je ope­rá­to­rom dokument @@ query

SELECT
	to_tsvector('simple', 'Zmena entropie sústavy sa pri ľubovoľnom izotermickom deji prebiehajúcom pri teplote absolútnej nuly rovná nule.')
	@@
	websearch_to_tsquery('simple', 'Zmena entropie');
?column?
--------
t
(1 row)

Ignorovanie diakritiky

Ako im­ple­men­to­vať ig­no­ro­va­nie diak­ri­ti­ky? Veľ­mi jed­no­du­cho, sta­či­la by fun­kcia, kto­rá upra­ví tex­ty pri kon­ver­zii do tsvector a tsquery. Po­tom by sa au­to­ma­tic­ky vy­hľa­dá­val vý­raz s od­strá­ne­nou diak­ri­ti­kou v do­ku­men­te s od­strá­ne­nou diak­ri­ti­kou.

Úp­ra­va vý­ra­zov je im­ple­men­to­va­ná ako sú­časť kon­fi­gu­rá­cie full­tex­to­vé­ho vy­hľa­dá­va­nia. Do­te­raz bo­la vo všet­kých vý­ra­zoch po­u­ži­tá kon­fi­gu­rá­cia 'simple'. Vsta­va­ných je nie­koľ­ko ďal­ších kon­fi­gu­rá­cií a ich zo­znam sa dá zo­bra­ziť prí­ka­zom \dFd+.

Na­sle­du­jú­ci kód vy­tvo­rí kon­fi­gu­rá­ciu 'unaccent' full­tex­tu pre vy­hľa­dá­va­nie bez diak­ri­ti­ky. Zvlášt­ne vý­ra­zy ako asciihword sú po­pí­sa­né v do­ku­men­tá­cii. Roz­ší­re­nie unaccent je ap­li­ko­va­né iba na slo­vá, kto­ré mô­žu ob­sa­ho­vať diak­ri­ti­ku. Tie, kto­ré sú bez diak­ri­ti­ky sú po­ne­cha­né bez zme­ny.

CREATE EXTENSION unaccent;
CREATE TEXT SEARCH CONFIGURATION unaccent (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION unaccent
	ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
	WITH simple;
ALTER TEXT SEARCH CONFIGURATION unaccent
	ALTER MAPPING FOR hword, hword_part, word
	WITH unaccent, simple;

Te­raz je po­treb­né na­hra­diť in­dex, kto­rý vy­uží­val kon­fi­gu­rá­ciu 'simple' in­de­xom s kon­fi­gu­rá­ci­ou 'unaccent'.

DROP INDEX document_idx;
ALTER TABLE ft DROP COLUMN document_tsvector;
ALTER TABLE ft
	ADD COLUMN document_tsvector tsvector
	GENERATED ALWAYS AS (to_tsvector('unaccent', document)) STORED;
CREATE INDEX document_idx ON ft USING GIN (document_tsvector);

Po­čet vý­sled­kov vý­ra­zu "zá­vis­losť" sa zvý­šil z 45 na 108.

SELECT COUNT(*) FROM ft WHERE document_tsvector @@ to_tsquery('unaccent', 'závislosť');
count
-----
  108

Skloňovanie

Ok­rem od­strá­ne­nia diak­ri­ti­ky je mož­né po­čas kon­ver­zie zme­niť tvar slov na zá­klad­ný, vďa­ka čo­mu bu­de mož­né vy­hľa­dá­vať v ľu­bo­voľ­nom tva­re.

Kon­fi­gu­rá­cie pre nie­kto­ré ja­zy­ky sú už vsta­va­né, ale slo­ven­či­na me­dzi ne ne­pat­rí. Ok­rem to­ho vsta­va­ná pod­po­ra po­u­ží­va sno­wball stem­mer (prog­ram pre pre­vod slov na zá­klad­ný tvar). Ne­vý­ho­dou sno­wbal­lu je, že ne­pou­ží­va slov­ník. Na­mies­to ne­ho sa sna­ží od­strá­niť pred­po­ny / prí­po­ny al­go­rit­mom, čo nie je vždy správ­ne. Či už je ne­ja­ká kon­fi­gu­rá­cia vsta­va­ná v post­gre­se, ale­bo nie, stá­le má zmy­sel vy­tvo­riť si vlast­nú s po­u­ži­tím slov­ní­ka.

No­vá kon­fi­gu­rá­cia bu­de mať ná­zov 'sk'.

CREATE TEXT SEARCH CONFIGURATION sk (COPY = simple);
CREATE TEXT SEARCH DICTIONARY slovak_ispell (
	TEMPLATE = ispell,
	DictFile = sk,
	AffFile = sk,
	Stopwords = sk
);

Prí­kaz prav­de­po­dob­ne skon­čí chy­bou ERROR: F0000: could not open dictionary file "/usr/share/postgresql-15/tsearch_data/sk.dict". Zna­me­ná to, že sú­bo­ry sk.dict, sk.affix a sk.stop z ba­lí­ka is­pell ma­jú byť sko­pí­ro­va­né do /usr/share/postgresql-15/tsearch_data/.

Sú­bo­ry s prí­po­nou .affix a .dict sú do­stup­né ako sú­časť is­pell slov­ní­ka. Sú­bor čas­tých slov, kto­ré sa ne­ma­jú in­de­xo­vať je po­treb­né do­pl­niť ruč­ne, ale­bo nájsť nie­kto­rý s vhod­nou li­cen­ci­ou. Je­den s MIT li­cen­ci­ou je do­stup­ný na git­hu­be.

Po sko­pí­ro­va­ní sú­bo­rov do tsearch_data už bu­de mož­né vy­tvo­riť slov­ník.

CREATE TEXT SEARCH DICTIONARY slovak_ispell (
	TEMPLATE = ispell,
	DictFile = sk,
	AffFile = sk,
	Stopwords = sk
);
ALTER TEXT SEARCH CONFIGURATION sk
	ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
	WITH slovak_ispell, simple;
ALTER TEXT SEARCH CONFIGURATION sk
	ALTER MAPPING FOR hword, hword_part, word
	WITH slovak_ispell, unaccent, simple;

Opäť je po­treb­né upra­viť document_tsvector a vy­ge­ne­ro­vať in­dex.

DROP INDEX document_idx;
ALTER TABLE ft DROP COLUMN document_tsvector;
ALTER TABLE ft
	ADD COLUMN document_tsvector tsvector
	GENERATED ALWAYS AS (to_tsvector('sk', document)) STORED;
CREATE INDEX document_idx ON ft USING GIN (document_tsvector);

Naj­skôr pre ukáž­ku, že kon­fi­gu­rá­cia pra­cu­je správ­ne na­kŕ­mim fun­kciu to_tsvector rôz­ny­mi tvar­mi slo­va. Vý­sled­kom by ma­lo byť zlú­če­né to is­té slo­vo v zá­klad­nom tva­re.

SELECT to_tsvector('sk', 'závislosť závislosti závislostí');
   to_tsvector
-----------------
'závislosť':1,2,3

Full­tex­to­vé vy­hľa­dá­va­nie pod­ľa oča­ká­va­nia vrá­ti aj riad­ky s os­tat­ný­mi tvar­mi slo­va, čo zvý­ši po­čet vý­sled­kov zo 108 na 527.

SELECT COUNT(*) FROM ft WHERE document_tsvector @@ to_tsquery('sk', 'závislosť');
count
-----
  527

Sk­lo­ňo­va­nie však ne­fun­gu­je pre do­ta­zy za­da­né bez diak­ri­ti­ky.

SELECT to_tsvector('sk', 'závislosť závislost');
        to_tsvector
---------------------------
'zavislost':2 'závislosť':1

Vý­raz bez diak­ri­ti­ky sa ne­na­chá­dza v slov­ní­ku, pre­to sa je­ho tvar ne­zme­ní na zá­klad­ný. Či už je po­u­ži­tý skôr slov­ník a po­tom unaccent, ale­bo opač­ne, stá­le bu­de prob­lém s vy­hľa­dá­va­ním bez diak­ri­ti­ky.

Prob­lém sa dá veľ­mi jed­no­du­cho vy­rie­šiť od­strá­ne­ním diak­ri­ti­ky zo slov­ní­ka na­prí­klad po­mo­cou ná­stro­ja Uni­de­co­de.

unidecode < sk.dict > sk_unaccent.dict
unidecode < sk.affix > sk_unaccent.affix
unidecode < sk.stop > sk_unaccent.stop

De­fi­ní­cia no­vej kon­fi­gu­rá­cie vy­hľa­dá­va­nia vy­ze­rá tak­to:

CREATE TEXT SEARCH CONFIGURATION sk_unaccent (COPY = simple);
CREATE TEXT SEARCH DICTIONARY slovak_unaccent_ispell (
	TEMPLATE = ispell,
	DictFile = sk_unaccent,
	AffFile = sk_unaccent,
	Stopwords = sk_unaccent
);
ALTER TEXT SEARCH CONFIGURATION sk_unaccent
	ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
	WITH slovak_unaccent_ispell, simple;
ALTER TEXT SEARCH CONFIGURATION sk_unaccent
	ALTER MAPPING FOR hword, hword_part, word
	WITH unaccent, slovak_unaccent_ispell, simple;

Opäť na­sle­du­je vy­ge­ne­ro­va­nie no­vé­ho in­de­xu:

DROP INDEX document_idx;
ALTER TABLE ft DROP COLUMN document_tsvector;
ALTER TABLE ft
	ADD COLUMN document_tsvector tsvector
	GENERATED ALWAYS AS (to_tsvector('sk_unaccent', document)) STORED;
CREATE INDEX document_idx ON ft USING GIN (document_tsvector);

Te­raz už kon­ver­zia rôz­nych tva­rov a spô­so­bu za­dá­va­nia skon­čí rov­na­kým slo­vom. Do­kon­ca z in­de­xu sú zá­ro­veň vy­lú­če­né čas­té slo­vá ako "a", "ale­bo", "ani" …

SELECT to_tsvector('sk_unaccent', 'závislosť závislosti zavislost zavislosti alebo a ani');
    to_tsvector
-------------------
'zavislost':1,2,3,4

Vy­hľa­dá­va­nie zried­ka­vé­ho slo­va te­raz vrá­ti 25x viac vý­sled­kov než pô­vod­ne v Post­gre­SQL a 10x viac než v My­SQL, pre­to­že vo vy­hľa­dá­va­ní sú za­hr­nu­té rôz­ne tva­ry slov.

SELECT
	COUNT(*)
FROM ft
WHERE document_tsvector @@ to_tsquery('sk_unaccent', 'zavislost');
count
-----
 1107

Tých­to nie­koľ­ko tri­kov vý­raz­ne zlep­ši­lo kva­li­tu vy­hľa­dá­va­nia. Spo­lu s trik­mi v pr­vej čas­ti člán­ku má­me k dis­po­zí­cii sluš­né vy­hľa­dá­va­nie s ak­cep­to­va­teľ­nou rých­los­ťou pre men­šie a stred­né ap­li­ká­cie.

Ďalšie tipy

Ne­zna­me­ná to, že by bo­li všet­ky prob­lé­my vy­hľa­dá­va­nia vy­rie­še­né. Stá­le je čo vy­lep­šo­vať a pre­to do­pĺňam nie­kto­ré zau­jí­ma­vé mož­nos­ti.

Váha

Nie kaž­dá časť člán­ku má rov­na­kú vá­hu. V Post­gre­SQL sú k dis­po­zí­cii 4 úrov­ne - A - D. Štan­dard­ná vá­ha je tá naj­niž­šia - D.

Ak má­me na­prí­klad ta­buľ­ku s ti­tul­kom a do­ku­men­tom a chce­me vy­tvo­riť kom­bi­no­va­ný vek­tor, je dob­ré zvý­šiť pri­ori­tu ti­tul­ku fun­kci­ou setweight.

setweight(to_tsvector('sk_unaccent', title), 'A') || setweight(to_tsvector('sk_unaccent', content), 'C')

Ako vy­ze­rá vy­tvo­re­ný vek­tor si môž­me vy­skú­šať na­sle­du­jú­cim se­lec­tom:

SELECT
	setweight(to_tsvector('sk_unaccent', 'obsah titulku'), 'A') ||
	setweight(to_tsvector('sk_unaccent', 'obsah stránky'), 'C');


-- 'obsah': 1A, 3C
-- 'stranka': 4C
-- 'titulok': 2A

Opravy slov

Čas­to sa stá­va, že uží­va­teľ za­dá slo­vo s pre­kle­pom. Post­gre­SQL ne­ob­sa­hu­je žiad­nu fun­kciu pre au­to­ma­tic­ké roz­poz­na­nie a opra­vu chýb. Nie je prob­lém si na­vr­hnúť vlast­ný jed­no­du­chý sys­tém.

Ako de­tek­cia mô­že po­slú­žiť na­prí­klad to, že vy­hľa­dá­va­nie ne­vrá­ti žiad­ne vý­sled­ky, ale­bo vrá­ti má­lo vý­sled­kov. V ta­kom prí­pa­de zis­tí­me po­dob­né slo­vá v da­ta­bá­ze slov.

Na­sle­du­jú­ca sé­ria prí­ka­zov vy­tvo­rí da­ta­bá­zu slov.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE words (
	id SERIAL,
	word VARCHAR(100),
	ndoc int,
	PRIMARY KEY(id)
);

-- Naplnenie tabuľky
INSERT INTO words(word, ndoc)
	SELECT word, ndoc
	FROM ts_stat('SELECT to_tsvector(''simple'', document) FROM ft');

CREATE INDEX word_idx
	ON words USING GIST (word gist_trgm_ops);

Ta­buľ­ka so slo­va­mi je na­pl­ne­ná všet­ký­mi slo­va­mi z do­ku­men­tov. Ako jed­no­du­chý to­ke­ni­zér je po­u­ži­tý to_tsvector. V no­vej ta­buľ­ke sa na­chá­dza stĺpec so slo­vom (word) a po­čet vý­sky­tov (ndoc). No­vá ta­buľ­ka za­hŕňa 552 314 slov.

Vý­ber slov zo­ra­de­ný pod­ľa po­dob­nos­ti vy­ze­rá na­sle­dov­ne:

SELECT
	word,
	ndoc,
	word <-> 'zévislosť' AS sml
FROM words
WHERE word % 'zévislosť'
ORDER BY sml
LIMIT 4;

--    word     ndoc    sml
-- ----------- ---- ----------
-- závislosť     45 0.46153843
-- ávislosť       1 0.53846157
-- súvislosť     23 0.57142854
-- nezávislosť    5      0.625

Zvýraznenie vyhľadávaných výrazov

Pri po­u­ži­tí sk­lo­ňo­va­nie nie je jed­no­du­ché zis­tiť slo­vá, kto­ré bo­li vy­hľa­da­né v do­ku­men­te, keď­že mô­žu mať iný tvar než za­da­ná frá­za. Z to­ho dô­vo­du je v Post­gre­SQL im­ple­men­to­va­ná fun­kcia ts_headline.

SELECT
	ts_headline(
		'sk_unaccent',
		document,
		to_tsquery('sk_unaccent', 'závislosť'),
		'MaxFragments=10, MaxWords=7, MinWords=3, StartSel=«, StopSel=», FragmentDelimiter= … '
	)
FROM ft
WHERE document_tsvector @@ to_tsquery('sk_unaccent', 'závislosť')
ORDER BY document_tsvector <=> to_tsquery('sk_unaccent', 'závislosť')
LIMIT 1;

--                                                 ts_headline
-- ------------------------------------------------------------------------------------------------------------
-- Anketa -> Vaše «závislosti» okrem Linuxu                                                                    +
-- Aké…Vaše "«závislosti»"? Zámerne som zvolil túto anketu…Anketa -> Vaše «závislosti» okrem Linuxu            +
-- Odkazy…anketa pokračuje -> Vaše «závislosti» okrem Linuxu…Anketa -> Vaše «závislosti» okrem Linuxu          +
-- Hmm, «zavislosti»…mojou asi najvacsou «zavislostou» su ZENY!! Takze…Anketa -> Vaše «závislosti» okrem Linuxu+
-- Heeh, dobra…Anketa -> Vaše «závislosti» okrem Linuxu…Anketa -> Vaše «závislosti» okrem Linuxu               +
-- strašne veľa…Anketa -> Vaše «závislosti» okrem Linuxu                                                       +
-- A zabudol