postgres full text search
1.0.0
Exemples d'options de recherche de texte intégral de Postgres (TRIRMAM, ILIKE).
ilike simplesilike pris en charge par Trigram Index >> CREATE DATABASE ftdb; Pour nourrir DB avec un exemple de jeu de données ( dataset.txt , 100k lignes, 15 mots chacun) J'ai utilisé le script python init_db.py .
ilike simples >> EXPLAIN ANALYZE
SELECT text , language
FROM public . document
WHERE
text ilike ' %field% '
AND text ilike ' %window% '
AND text ilike ' %lamp% '
AND text ilike ' %research% '
AND language = ' en '
LIMIT 1 ;
QUERY PLAN
-- --------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 0 . 00 .. 3734 . 02 rows = 1 width = 105 ) (actual time = 87 . 473 .. 87 . 474 rows = 0 loops = 1 )
- > Seq Scan on document (cost = 0 . 00 .. 3734 . 02 rows = 1 width = 105 ) (actual time = 87 . 466 .. 87 . 466 rows = 0 loops = 1 )
Filter: (( text ~~ * ' %field% ' :: text ) AND ( text ~~ * ' %window% ' :: text ) AND ( text ~~ * ' %lamp% ' :: text ) AND ( text ~~ * ' %research% ' :: text ))
Rows Removed by Filter: 100001
Planning Time : 2 . 193 ms
Execution Time : 87 . 500 msilike pris en charge par Trigram IndexQu'est-ce qu'un trigramme? Voir cet exemple:
>> CREATE EXTENSION pg_trgm;
CREATE EXTENSION
>> select show_trgm( ' fielded ' );
show_trgm
-- ---------------------------------------
{ " f " , " fi " ,ded, " ed " ,eld,fie,iel,lde} Nous pouvons améliorer les performances ilike en utilisant l'index Trigram, par exemple gin_trgm_ops .
>> CREATE INDEX ix_document_text_trigram ON document USING gin ( text gin_trgm_ops) where language = ' en ' ;
CREATE INDEX
>> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE
text ilike ' %field% '
AND text ilike ' %window% '
AND text ilike ' %lamp% '
AND text ilike ' %research% '
AND language = ' en '
LIMIT 1 ;
QUERY PLAN
-- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 176 . 00 .. 180 . 02 rows = 1 width = 105 ) (actual time = 1 . 473 .. 1 . 474 rows = 0 loops = 1 )
- > Bitmap Heap Scan on document (cost = 176 . 00 .. 180 . 02 rows = 1 width = 105 ) (actual time = 1 . 470 .. 1 . 471 rows = 0 loops = 1 )
Recheck Cond: (( text ~~ * ' %field% ' :: text ) AND ( text ~~ * ' %window% ' :: text ) AND ( text ~~ * ' %lamp% ' :: text ) AND ( text ~~ * ' %research% ' :: text ) AND ((language):: text = ' en ' :: text ))
- > Bitmap Index Scan on ix_document_text_trigram (cost = 0 . 00 .. 176 . 00 rows = 1 width = 0 ) (actual time = 1 . 466 .. 1 . 466 rows = 0 loops = 1 )
Index Cond: (( text ~~ * ' %field% ' :: text ) AND ( text ~~ * ' %window% ' :: text ) AND ( text ~~ * ' %lamp% ' :: text ) AND ( text ~~ * ' %research% ' :: text ))
Planning Time : 2 . 389 ms
Execution Time : 1 . 524 msPostgres ne prend pas en charge de nombreuses langues par défaut. Cependant, vous pouvez configurer la configuration assez facilement. Vous avez juste besoin de fichiers de dictionnaire supplémentaires. Voici un exemple de langue polonaise. Les fichiers de dictionnaire polonais peuvent être téléchargés à partir de: https://github.com/judehunter/polish-tsearch.
POLOSIQUE.Affix, POLOST.STOP ET POLOST.DICT Les fichiers doivent être copiés sur l'emplacement de PostgreSQL Sharedir tsearch_data , par exemple /usr/share/postgresql/13/tsearch_data . Pour déterminer votre emplacement partagéir, vous pouvez utiliser pg_config --sharedir
Il doit également être créé une configuration (voir les documents) dans la base de données:
>> DROP TEXT SEARCH DICTIONARY IF EXISTS polish_hunspell CASCADE;
CREATE TEXT SEARCH DICTIONARY polish_hunspell (
TEMPLATE = ispell,
DictFile = polish,
AffFile = polish,
StopWords = polish
);
CREATE TEXT SEARCH CONFIGURATION public . polish (
COPY = pg_catalog . english
);
ALTER TEXT SEARCH CONFIGURATION polish
ALTER MAPPING
FOR
asciiword, asciihword, hword_asciipart, word, hword, hword_part
WITH
polish_hunspell, simple;
Vous avez besoin de ces fichiers et de ces configurations car le moteur de recherche de texte intégral utilise la comparaison Lexeme pour trouver les meilleures correspondances (le modèle de requête et le texte stocké sont lexémisés):
>> SELECT to_tsquery( ' english ' , ' fielded ' ), to_tsvector( ' english ' , text )
FROM document
LIMIT 1 ;
to_tsquery | to_tsvector
-- ----------+----------------------------------------------------------------------------------------------------------------------------------------------------
' field ' | ' 19 ' : 16 ' bat ' : 12 ' dead ' : 8 ' degre ' : 1 ' depth ' : 5 ' field ' : 15 ' lamp ' : 13 ' men ' : 6 ' put ' : 14 ' ranch ' : 2 ' tall ' : 4 ' time ' : 3 ' underlin ' : 11 ' wast ' : 10 ' window ' : 9Si vous ne pouvez pas fournir de fichiers de dictionnaire, vous pouvez utiliser du texte intégral sous forme "simple" (sans transformation en lexème):
>> SELECT to_tsquery( ' simple ' , ' fielded ' ), to_tsvector( ' simple ' , text )
FROM document
LIMIT 1 ;
to_tsquery | to_tsvector
-- ----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
' fielded ' | ' 19 ' : 16 ' bat ' : 12 ' below ' : 7 ' dead ' : 8 ' degree ' : 1 ' depth ' : 5 ' field ' : 15 ' lamp ' : 13 ' men ' : 6 ' putting ' : 14 ' ranch ' : 2 ' tall ' : 4 ' time ' : 3 ' underline ' : 11 ' waste ' : 10 ' window ' : 9 >> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & lamp & depth & test ' )
LIMIT 1 ;
QUERY PLAN
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 1000 . 00 .. 18298 . 49 rows = 1 width = 103 ) (actual time = 489 . 802 .. 491 . 352 rows = 0 loops = 1 )
- > Gather (cost = 1000 . 00 .. 18298 . 49 rows = 1 width = 103 ) (actual time = 489 . 800 .. 491 . 349 rows = 0 loops = 1 )
Workers Planned: 1
Workers Launched: 1
- > Parallel Seq Scan on document (cost = 0 . 00 .. 17298 . 39 rows = 1 width = 103 ) (actual time = 486 . 644 .. 486 . 644 rows = 0 loops = 2 )
Filter: (((language):: text = ' en ' :: text ) AND (to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' lamp ' ' & ' ' depth ' ' & ' ' test ' ' ' ::tsquery))
Rows Removed by Filter: 50000
Planning Time : 0 . 272 ms
Execution Time : 491 . 376 ms
( 9 rows)L'indice partiel donne la possibilité de stocker des enregistrements dans différentes langues à l'aide d'une seule table et de les interroger efficacement.
>> CREATE INDEX ix_en_document_tsvector_text ON public . document USING gin (to_tsvector( ' english ' ::regconfig, text )) WHERE language = ' en ' ;
CREATED INDEX
>> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & lamp & depth & test ' )
LIMIT 1 ;
QUERY PLAN
-- --------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 1000 . 00 .. 18151 . 43 rows = 1 width = 103 ) (actual time = 487 . 120 .. 488 . 569 rows = 0 loops = 1 )
- > Gather (cost = 1000 . 00 .. 18151 . 43 rows = 1 width = 103 ) (actual time = 487 . 117 .. 488 . 567 rows = 0 loops = 1 )
Workers Planned: 1
Workers Launched: 1
- > Parallel Seq Scan on document (cost = 0 . 00 .. 17151 . 33 rows = 1 width = 103 ) (actual time = 484 . 418 .. 484 . 419 rows = 0 loops = 2 )
Filter: (to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' lamp ' ' & ' ' depth ' ' & ' ' test ' ' ' ::tsquery)
Rows Removed by Filter: 50000
Planning Time : 0 . 193 ms
Execution Time : 488 . 596 msAucune différence? L'index n'a pas été utilisé ... Pourquoi ne fonctionne-t-il pas? Ohh, regarde les documents d'index partiels:
Cependant, gardez à l'esprit que le prédicat doit correspondre aux conditions utilisées dans les requêtes qui sont censées bénéficier de l'indice. Pour être précis, un index partiel ne peut être utilisé dans une requête que si le système ne peut reconnaître que l'état où la requête implique mathématiquement le prédicat de l'index. PostgreSQL n'a pas de prover de théorème sophistiqué qui peut reconnaître les expressions mathématiquement équivalentes qui sont écrites sous différentes formes. (Non seulement un tel prover de théorème général est extrêmement difficile à créer, mais il serait probablement trop lent pour être d'une utilisation réelle.) Le système peut reconnaître des implications simples d'inégalité, par exemple "x <1" implique "x <2"; Sinon, la condition de prédicat doit correspondre exactement à la partie de la requête où l'état ou l'index ne sera pas reconnu comme utilisable. Le match a lieu au moment de la planification des requêtes, pas au moment de l'exécution. En conséquence, les clauses de requête paramétrés ne fonctionnent pas avec un index partiel.
Nous devons ajouter à interroger une condition qui a été utilisée pour créer un index partiel: document.language = 'en' :
>> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE
to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & lamp & depth & test ' )
AND language = ' en '
LIMIT 1 ; QUERY PLAN
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 64 . 00 .. 68 . 27 rows = 1 width = 103 ) (actual time = 0 . 546 .. 0 . 548 rows = 0 loops = 1 )
- > Bitmap Heap Scan on document (cost = 64 . 00 .. 68 . 27 rows = 1 width = 103 ) (actual time = 0 . 544 .. 0 . 545 rows = 0 loops = 1 )
Recheck Cond: ((to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' lamp ' ' & ' ' depth ' ' & ' ' test ' ' ' ::tsquery) AND ((language):: text = ' en ' :: text ))
- > Bitmap Index Scan on ix_en_document_tsvector_text (cost = 0 . 00 .. 64 . 00 rows = 1 width = 0 ) (actual time = 0 . 540 .. 0 . 540 rows = 0 loops = 1 )
Index Cond: (to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' lamp ' ' & ' ' depth ' ' & ' ' test ' ' ' ::tsquery)
Planning Time : 0 . 244 ms
Execution Time : 0 . 590 ms :* L'opérateur permet la recherche de préfixe. Il peut être utile d'exécuter une recherche de texte intégral lors de la saisie d'un mot.
>> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE
to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & l:* ' )
AND language = ' en '
LIMIT 1 ;
QUERY PLAN
-- ----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on document (cost = 168 . 00 .. 172 . 27 rows = 1 width = 102 ) (actual time = 5 . 207 .. 5 . 210 rows = 4 loops = 1 )
Recheck Cond: ((to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' l ' ' :* ' ::tsquery) AND ((language):: text = ' en ' :: text ))
Heap Blocks: exact = 4
- > Bitmap Index Scan on ix_en_document_tsvector_text (cost = 0 . 00 .. 168 . 00 rows = 1 width = 0 ) (actual time = 5 . 202 .. 5 . 202 rows = 4 loops = 1 )
Index Cond: (to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' l ' ' :* ' ::tsquery)
Planning Time : 0 . 240 ms
Execution Time : 5 . 240 ms
>> SELECT id, text
FROM public . document
WHERE
to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & l:* ' )
AND language = ' en '
LIMIT 20 ;
id | text
-- -----+-----------------------------------------------------------------------------------------------------------
1 | degree ranch time tall depth men below dead window waste underline bat lamp putting field +
20152 | Law pony follow memory star whatever window sets oxygen longer word whom glass field actual +
21478 | Dried symbol willing design managed shade window pick share faster education drive field land everybody +
30293 | Pencil seen engineer labor image entire smallest serve field should riding smaller window imagine traffic +
Il existe deux fonctions assez similaires pour classer les résultats de la recherche:
ts_rank , qui classe les vecteurs en fonction de la fréquence de leurs lexèmes correspondantsts_rank_cd , qui calcule le classement "densité de couverture"Pour plus d'informations, consultez les documents
>> SELECT
id,
ts_rank_cd(to_tsvector( ' english ' , text ), to_tsquery( ' english ' , ' fielded & wind:* ' )) rank,
text
FROM public . document
WHERE to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & wind:* ' )
ORDER BY rank DESC
LIMIT 20 ;
id | rank | text
-- ------+-------------+-----------------------------------------------------------------------------------------------------------
100002 | 0 . 1 | fielded window
9376 | 0 . 05 | Own mouse girl effect surprise physical newspaper forgot eat upper field element window simply unhappy +
96597 | 0 . 05 | Opinion fastened pencil rear more theory size window heading field understanding farm up position attack +
44626 | 0 . 033333335 | Symbol each halfway window swam spider field page shinning donkey chose until cow cabin congress +
80922 | 0 . 033333335 | Victory famous field shelter girl wind adventure he divide rear tip few studied ruler judge +
30293 | 0 . 025 | Pencil seen engineer labor image entire smallest serve field should riding smaller window imagine traffic +
1 | 0 . 016666668 | degree ranch time tall depth men below dead window waste underline bat lamp putting field +
21478 | 0 . 016666668 | Dried symbol willing design managed shade window pick share faster education drive field land everybody +
60059 | 0 . 016666668 | However hungry make proud kids come willing field officer row above highest round wind mile +
26001 | 0 . 014285714 | Earth earlier pocket might sense window way frog fire court family mouth field somebody recognize +
20152 | 0 . 014285714 | Law pony follow memory star whatever window sets oxygen longer word whom glass field actual +
37470 | 0 . 0125 | Farm weight balloon buried wind water donkey grain pig week should damage field was he +
49433 | 0 . 01 | Wind scientist leaving atom year bad child drink shore spirit field facing indicate wagon here +
37851 | 0 . 007142857 | Field cloud you wife rhythm upward applied weigh continued property replace ahead forgotten trip window +
text='fielded window' Record a été ajouté manuellement pour afficher le meilleur résultat de match.
Nous avons créé l'index de gin. Mais il y a aussi l'option d'index GIST. Lequel est le meilleur? Ça dépend...
>> EXPLAIN ANALYZE SELECT text , language
FROM public . document
WHERE
to_tsvector( ' english ' , text ) @@ to_tsquery( ' english ' , ' fielded & window & lamp & depth & test ' )
AND language = ' en '
LIMIT 1 ;
QUERY PLAN
-- ---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost = 0 . 28 .. 8 . 30 rows = 1 width = 103 ) (actual time = 2 . 699 .. 2 . 700 rows = 0 loops = 1 )
- > Index Scan using ix_en_document_tsvector_text on document (cost = 0 . 28 .. 8 . 30 rows = 1 width = 103 ) (actual time = 2 . 697 .. 2 . 697 rows = 0 loops = 1 )
Index Cond: (to_tsvector( ' english ' ::regconfig, text ) @@ ' ' ' field ' ' & ' ' window ' ' & ' ' lamp ' ' & ' ' depth ' ' & ' ' test ' ' ' ::tsquery)
Planning Time : 0 . 274 ms
Execution Time : 2 . 730 msGin semble être un peu plus rapide. Je ne pense pas que je pourrais l'expliquer mieux que les documents déjà:
Dans le choix du type d'index utiliser, Gist ou Gin, considérez ces différences de performances:
- Les recherches d'index Gin sont environ trois fois plus rapides que Gist
- Les index de gin prennent environ trois fois plus de plus à construire que l'essentiel
- Les index de Gin sont modérément plus lents à mettre à jour que les index GIST, mais environ 10 fois plus lents si la prise en charge rapide a été désactivée (voir la section 58.4.1 pour plus de détails)
- Les indices de gin sont deux à trois fois plus grands que les index GIST
