Full Text Search PostgreSQL

Voici le problème : dans une base de données PostgreSQL, j'ai importé dans une table au-delà de deux millions d'enregistrements qui correspondent à de l'information sur des entreprises. Si je fais une recherche uniquement sur cette table, sans jointure, avec la clause LIKE, la performance est plutôt rapide mais ce ne sont pas tous les noms qui sont trouvés car la comparaison de texte est sensible à la casse.

-- 1016 ms (~ 1 seconde d'exécution)
SELECT *
FROM companies
WHERE company_name LIKE '%steel%';

En utilisant à la place ILIKE, je réussis à en extraire davantage car le I dans ILIKE indique de procéder à la comparaison "Case Insensitive". Cependant, c'est au détriement de la performance:

-- 4781 ms (près de 5 secondes)
SELECT *
FROM companies
WHERE company_name ILIKE '%steel%';

Si je crée un index sur le champ de la table, ça risque d'aller plus vite ? Eh bien non, la même requête prend à peu près le même temps (même légèrement supérieur), en plus d'alourdir la taille de la base de données.

CREATE UNIQUE INDEX name_idx ON companies(company_name);

Supprimons cet index, et remplaçons-le par le Full Text Search (FTS). D'abord, j'ajoute une nouvelle colonne à ma table. À l'avenir, c'est sur celle-ci que je ferai la comparaison texte.

ALTER TABLE companies ADD COLUMN search_idx_company_name tsvector;

J'initialise le nouveau champ à partir de mes données (sur 2 millions d'enregistrements, j'ai patienté environ 2 minutes). Au besoin, vous pouvez spécifier un dictionnaire ("english", "french", etc.) dans to_tsvector(). Pour ma solution, je ne l'indique pas.

UPDATE companies
SET search_idx_company_name = to_tsvector(coalesce(company_name,''));

Pour maintenir l'intégrité des données entre la donnée réelle (champ "company_name") et le champ qui sera indexé (champ vecteur "search_idx_company_name"), il sera nécessaire de placer un trigger qui mettra à jour les données lorsque nécessaire.

Avant même de créer l'index, je vais évaluer la performance en lançant une requête FTS. Pour cela, j'utiliserai l'opérateur @@ et je traduirai le mot clé de recherche avec la fonction to_tsquery().

-- ouch, 17 secondes!
SELECT *
FROM companies
WHERE search_idx_company_name @@ to_tsquery('steel');

Ajoutons l'index (attendre un peu plus de 2 minutes) :

CREATE INDEX textsearch_idx ON companies
USING gin(search_idx_company_name);

Lançons la même requête à nouveau : les résultats sont instantanés (156 millisecondes)! Par contre, il faut noter que le full text search retourne moins de résultats que le ILIKE car il n'effectue pas la comparaison de texte de la même façon. À vrai dire, il se fie sur la similarité des mots plutôt que sur l'exactitude du terme, et tout cela est flexible et configurable (dictionnaire, synonymes, thesaurus, etc). À utiliser dans les cas où la vitesse est prioritaire à la justesse des données.