De retour au clan des Géants ! Les proverbes du clan, très nombreux, sont en fait des poèmes de quelques lignes. Margiono, le scribe, les a stockés dans sa base de données PostgreSQL. Un jour Oumpfor, le chef du clan, lui demande de retrouver le fameux proverbe comportant "NUGE LHTGR". Margiono se lance donc dans la recherche :
Après une recherche infructueuse et quelques sueurs froides, Margiono a eu une intuition en considérant que NUGE pouvait être à la fin dune ligne et LHTGR au début de la suivante. Il a concaténé les lignes de chaque proverbe en respectant leur ordre et effectué une recherche avec LIKE.
Le hic cest que la recherche a pris 13s. Cest long et indexer la colonne TEXTE_PROVERBE avec un index B-Tree, BRIN ou BLOOM ny changera rien. Langoisse monte car, pour linstant, un seul des 42 tomes de proverbes a été chargé. Un ami suggère à Margiono de faire appel à Lucene mais il décide dabord de donner sa chance aux capacités de recherche FULL TEXT de PostgreSQL. Le scribe utilise pour cela une structure dédiée, le TSVECTOR et tente les 2 méthodes dindexation proposées, GIST et GIN :
Conclusion
Bien sûr la recherche FULL TEXT de PostgreSQL va bien plus loin que cet exemple et les capacités sont même très étendues. La langue des géants nest pas connue mais, si votre texte est en français ou en anglais, vous pourrez le prendre en compte. Si les recherches concernent plusieurs colonnes, vous pourrez leur donner des poids différents afin dinfluencer lordre daffichage des résultats.
Ici cest surtout laspect indexation qui nous intéressait. Les index GIST sont légers, cest à dire rapides à créer et peu volumineux. Ici lindex GIST a été créé en 11s et pèse 26Mo. Il permet de faire passer la recherche de 13s à moins de 0s3.
Les index GIN sont plus lourds donc plus lents à créer et plus volumineux. Ici lindex GIN a été créé en 7min25s, augmenter maintenance_work_mem permettrait cependant de diminuer drastiquement le temps de création. Il pèse 871Mo mais il permet de faire passer le temps dexécution de la recherche à moins de 2ms.
A vous de choisir lindexation la plus adaptée en fonction de vos critères. Si la recherche FULL TEXT est ponctuellement utilisée sur un environnement OLTP alors lindexation GIST peut être le bon choix. Si la recherche FULL TEXT constitue un aspect très important et que les temps dexécutions des recherches doivent être très bas alors lindexation GIN est sans doute plus adaptée.
Si vous avez plusieurs dizaines de Go par heure à indexer dans le seul but de faire de la recherche FULL TEXT alors un SGBDR complet comme PostgreSQL nest probablement pas le produit à privilégier. Mais si votre besoin est hybride et implique, par exemple, dindexer quelques dizaines de Go par mois dans le but de faire de la recherche FULL TEXT en complément dautres requêtes SQL, alors il ny a aucune raison de se servir systématiquement dun outil externe.