Nous préparons une recherche plein texte dans Postgres. Partie 1

UPD 2e partie

Cet article est le premier d'une petite série d'articles sur la façon de configurer de manière optimale la recherche de texte intégral dans PostgreSQL. J'ai récemment dû résoudre un problème similaire au travail - et j'ai été très surpris de l'absence d'au moins quelques matériaux sensés à ce sujet. Mon expérience de combat sous la coupe.

Cravate

Je soutiens un projet relativement important qui a une recherche publique sur les documents. La base de données contient ~ 500 000 documents avec un volume total de ~ 3,6 Go. L'essence de la recherche est la suivante: l'utilisateur remplit un formulaire dans lequel il y a à la fois une requête de texte intégral et un filtrage par une variété de champs dans la base de données, y compris avec des jointures.

La recherche fonctionne (ou plutôt, a fonctionné) via Sphinx, et n'a pas très bien fonctionné. Les principaux problèmes étaient les suivants:

1. L'indexation a consommé environ 8 Go de RAM. Sur un serveur avec 8 Go de RAM, c'est un problème. La mémoire échangée, elle a conduit à des performances terribles .
2. L'index a été construit en environ 40 minutes. Il n'est pas question de cohérence des résultats de recherche, l'indexation est lancée une fois par jour.
3. La recherche a fonctionné longtemps . Des demandes ont été effectuées pendant une période particulièrement longue, ce qui correspondait à un grand nombre de documents: un grand nombre d'id-shniks ont dû être transférés du sphinx à la base de données, et triés par pertinence sur le backend.

En raison de ces problèmes, la tâche s'est posée - optimiser la recherche en texte intégral. Cette tâche a deux solutions:

1. Resserrer Sphinx: configurer un index en temps réel, stocker les attributs pour le filtrage dans l'index.
2. Utilisez le FTS PostgreSQL intégré.

Il a été décidé de mettre en œuvre la deuxième solution: de cette façon, vous pouvez fournir automatiquement la mise à jour automatique de l'index, vous débarrasser des longues communications entre deux services et surveiller un service au lieu de deux.

Cela semble être une bonne solution. Mais des problèmes nous attendent.

Commençons par le tout début.

Nous utilisons naïvement la recherche plein texte

Comme l'indique la documentation, les recherches en texte intégral nécessitent l'utilisation des tsquery tsvector et tsquery . Le premier stocke le texte du document sous une forme optimisée pour la recherche, le second stocke la requête de texte intégral.

Pour rechercher PostgreSQL, il existe des fonctions to_tsvector , plainto_tsquery , to_tsquery . Pour classer les résultats, il y a ts_rank . Leur utilisation est intuitive et elles sont bien décrites dans la documentation , nous ne nous attarderons donc pas sur les détails de leur utilisation.

Une requête de recherche traditionnelle les utilisant ressemblera à ceci:

 SELECT id, ts_rank(to_tsvector("document_text"), plainto_tsquery('')) FROM documents_document WHERE to_tsvector("document_text") @@ plainto_tsquery('') ORDER BY ts_rank(to_tsvector("document_text"), plainto_tsquery('')) DESC; 

Nous avons déduit les identifiants des documents dans le texte dont il y a le mot "requête", et les avons triés par ordre décroissant de pertinence. Tout semble aller bien? Non.

L'approche ci-dessus présente de nombreux inconvénients:

1. Nous n'utilisons pas d'index pour la recherche.
2. La fonction ts_vector est appelée pour chaque ligne de la table.
3. La fonction ts_rank est appelée pour chaque ligne du tableau.

Tout cela conduit au fait que la recherche prend très longtemps. EXPLAIN résultats sur une base de combat:

 Gather Merge (actual time=420289.477..420313.969 rows=58742 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual time=420266.150..420267.935 rows=19581 loops=3) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 2278kB -> Parallel Seq Scan on documents_document (actual time=65.454..420235.446 rows=19581 loops=3) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 140636 Planning time: 3.706 ms Execution time: 420315.895 ms 

420 secondes! Pour une seule demande!

La base génère également beaucoup de vorings du mot de forme [54000] word is too long to be indexed . Il n'y a rien à craindre. La raison en est que dans ma base de données se trouvent des documents créés dans l'éditeur WYSIWYG. Il insère beaucoup de   dans la mesure du possible, et il y en a 54 000 de suite. Postgres ignore les mots de cette longueur et écrit un vorning qui ne peut pas être désactivé.

Nous essaierons de résoudre tous les problèmes constatés et d'accélérer la recherche.

Nous optimisons naïvement la recherche

Nous ne jouerons pas avec la base de combat, bien sûr - nous allons créer une base de test. Il contient environ 12 000 documents. La demande de l'exemple il y a ~ 35 secondes. Impardonnablement long!

 Sort (actual time=35431.874..35432.208 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Seq Scan on documents_document (actual time=8.470..35429.261 rows=3593 loops=1) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 9190 Planning time: 0.200 ms Execution time: 35432.294 ms 

Index

Tout d'abord, bien sûr, vous devez ajouter un index. La manière la plus simple: un index fonctionnel.

 CREATE INDEX idx_gin_document ON documents_document USING gin (to_tsvector('russian', "document_text")); 

Un tel index sera créé pendant longtemps - cela a pris ~ 26 secondes sur la base de test. Il doit parcourir la base de données et appeler la fonction to_tsvector pour chaque enregistrement. Bien qu'il accélère toujours la recherche à 12 secondes, il est toujours impardonnablement long!

 Sort (actual time=12213.943..12214.327 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector('russian'::regconfig, document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Bitmap Heap Scan on documents_document (actual time=3.849..12212.248 rows=3593 loops=1) Recheck Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Heap Blocks: exact=946 -> Bitmap Index Scan on idx_gin_document (actual time=0.427..0.427 rows=3593 loops=1) Index Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Planning time: 0.109 ms Execution time: 12214.452 ms 

Appel répété to_tsvector

Pour résoudre ce problème, vous devez stocker tsvector dans la base de données. Lorsque vous modifiez des données dans un tableau avec des documents, vous devez bien sûr les mettre à jour - via des déclencheurs dans la base de données, en utilisant le backend.

Il existe deux façons de procéder:

1. Ajoutez une colonne de type tsvector au tableau contenant les documents.
2. Créez un tableau séparé avec une communication un à un avec le tableau des documents et stockez-y les vecteurs.

Les avantages de la première approche: le manque de jointures dans la recherche.
Les avantages de la deuxième approche: le manque de données supplémentaires dans le tableau avec les documents, il reste de la même taille qu'auparavant. Avec la sauvegarde, vous n'avez pas à perdre de temps et d'espace sur tsvector , que vous n'avez pas du tout besoin de sauvegarder.

Les deux trajets conduisent au fait que les données sur le disque deviennent deux fois plus: les textes des documents et leurs vecteurs sont stockés.

J'ai choisi la deuxième approche pour moi, ses avantages sont plus importants pour moi.

 CREATE INDEX idx_gin_document ON documents_documentvector USING gin ("document_text"); 

 SELECT documents_document.id, ts_rank("text", plainto_tsquery('')) FROM documents_document LEFT JOIN documents_documentvector ON documents_document.id = documents_documentvector.document_id WHERE "text" @@ plainto_tsquery('') ORDER BY ts_rank("text", plainto_tsquery('')) DESC; 

Ajoutez des données à la table liée et créez un index. L'ajout de données a pris 24 secondes sur une base de test, et la création d'un index n'a pris que 2,7 secondes . La mise à jour de l'index et des données, comme nous le voyons, ne s'est pas accélérée de manière significative, mais l'index lui-même peut désormais être mis à jour très rapidement.

Et combien de fois la recherche s'est-elle accélérée?

 Sort (actual time=48.147..48.432 rows=3593 loops=1) Sort Key: (ts_rank(documents_documentvector.text, plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Hash Join (actual time=2.281..47.389 rows=3593 loops=1) Hash Cond: (documents_document.id = documents_documentvector.document_id) -> Seq Scan on documents_document (actual time=0.003..2.190 rows=12783 loops=1) -> Hash (actual time=2.252..2.252 rows=3593 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 543kB -> Bitmap Heap Scan on documents_documentvector (actual time=0.465..1.641 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.404..0.404 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.410 ms Execution time: 48.573 ms 

 SELECT id, ts_rank("text", plainto_tsquery('')) AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

  Trier (temps réel = 44,339 à 44,487 lignes = 3593 boucles = 1)
   Clé de tri: (ts_rank (text, plainto_tsquery ('query' :: text))))
   Méthode de tri: quicksort Mémoire: 265 ko
   -> Bitmap Heap Scan sur documents_documentvector (temps réel = 0,692 à 43,682 lignes = 3593 boucles = 1)
         Vérifiez à nouveau Cond: (text @@ plainto_tsquery ('query' :: text))
         Blocs de tas: exact = 577
         -> Bitmap Index Scan sur idx_gin_document (temps réel = 0,577..0,577 lignes = 3593 boucles = 1)
               Index Cond: (text @@ plainto_tsquery ('query' :: text))
 Temps de planification: 0,182 ms
 Temps d'exécution: 44,610 ms

Incroyable! Et ce, malgré join et ts_rank . Déjà un résultat tout à fait acceptable, la plupart du temps est ts_rank non pas par la recherche, mais par le calcul de ts_rank pour chacune des lignes.

ts_rank appel multiple

Il semble que nous ayons résolu avec succès tous nos problèmes, sauf celui-ci. 44 millisecondes est un délai de livraison décent. Une fin heureuse semble proche? Ça y était!

Exécutez la même requête sans ts_rank et comparez les résultats.

 SELECT document_id, 1 AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

 Bitmap Heap Scan on documents_documentvector (actual time=0.503..1.609 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.439..0.439 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.147 ms Execution time: 1.715 ms 

1,7 ms! Trente fois plus vite! Pour une base de combat, les résultats sont d'environ 150 ms et 1,5 seconde. La différence dans tous les cas est un ordre de grandeur, et 1,5 seconde n'est pas le temps que vous souhaitez attendre une réponse de la base. Que faire?

Vous ne pouvez pas désactiver le tri par pertinence; vous ne pouvez pas réduire le nombre de lignes de comptage (la base de données doit calculer ts_rank pour tous ts_rank documents ts_rank , sinon ils ne peuvent pas être triés).

À certains endroits sur Internet, il est recommandé de mettre en cache les demandes les plus fréquentes (et, par conséquent, d'appeler ts_rank). Mais je n'aime pas cette approche: il est assez difficile de sélectionner correctement les bonnes requêtes, et la recherche ralentira toujours sur les mauvaises requêtes.

J'aimerais beaucoup qu'après avoir parcouru l'index, les données soient arrivées sous une forme déjà triée, comme le fait Sphinx. Malheureusement, rien ne peut être fait à partir de la boîte de PostgreSQL.

Mais nous avons eu de la chance - l'indice RUM peut le faire. Des détails à ce sujet peuvent être trouvés, par exemple, dans la présentation de ses auteurs . Il stocke des informations supplémentaires sur la demande, ce qui vous permet d'évaluer directement ce que l'on appelle. "distance" entre tsvector et tsquery et produit un résultat trié immédiatement après avoir numérisé l'index.

Mais lancer un GIN et installer RUM ne vaut pas la peine tout de suite. Il a des inconvénients, des avantages et des limites d'application - j'écrirai à ce sujet dans le prochain article.