Variations des performances des requêtes PostgreSQL LIKE

FTS ne supporte pas LIKE

La réponse précédemment acceptée était incorrecte. La recherche en texte intégral avec ses index de texte intégral ne convient pas du tout à l’opérateur LIKE , elle possède ses propres opérateurs et ne fonctionne pas pour des chaînes arbitraires. Il opère sur des mots basés sur des dictionnaires et issus de dictionnaires. Il prend en charge la correspondance de préfixe pour les mots , mais pas avec l’opérateur LIKE :

• Obtenir une correspondance partielle à partir de la colonne TSVECTOR indexée par GIN

Index Trigram pour LIKE

Installez le module supplémentaire pg_trgm qui fournit des classes d’opérateur pour les index de sortinggrammes GIN et GiST afin de prendre en charge tous les modèles LIKE et ILIKE , et pas seulement ceux d’ancrage à gauche:

Exemple d’index:

 CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops ); 

Ou:

 CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops ); 

• Différence entre l’indice GiST et l’indice GIN

Exemple de requête:

 SELECT * FROM tbl WHERE col LIKE '%foo%'; -- leading wildcard SELECT * FROM tbl WHERE col ILIKE '%foo%'; -- works case insensitively as well 

Trigrammes? Qu’en est-il des cordes plus courtes?

Les mots contenant moins de 3 lettres dans les valeurs indexées fonctionnent toujours. Le manuel:

Chaque mot est considéré comme ayant deux espaces préfixés et un espace suffixé lors de la détermination de l’ensemble des sortinggrammes contenus dans la chaîne.

Et rechercher des motifs avec moins de 3 lettres? Le manuel:

Pour les recherches LIKE et les expressions rationnelles, gardez à l’esprit qu’un modèle sans sortinggramme extractible dégénérera en une parsing d’index complet.

Cela signifie que les parsings d’index / bitmap d’index fonctionnent toujours (les plans de requête pour l’instruction préparée ne sont pas interrompus), cela ne vous permettra pas d’obtenir de meilleures performances. En général, aucune perte importante, car les chaînes à une ou deux lettres sont peu sélectives (plus de quelques pour cent des correspondances sous-jacentes) et la prise en charge des index n’améliore pas les performances, car une parsing complète des tables est plus rapide.

text_pattern_ops pour le préfixe correspondant

Pour les modèles uniquement ancrés à gauche (pas de caractère générique), vous obtenez l’optimum avec une classe d’opérateur appropriée pour un index btree: text_pattern_ops ou varchar_pattern_ops . Les deux fonctionnalités intégrées de Postgres standard, aucun module supplémentaire nécessaire. Performance similaire, mais indice beaucoup plus petit.

Exemple d’index:

 CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops ); 

Exemple de requête:

 SELECT * FROM tbl WHERE col LIKE 'foo%' ; -- no leading wildcard 

Ou , si vous devez exécuter votre firebase database avec les parameters régionaux ‘C’ ( pas de parameters régionaux), alors tout est sortingé en fonction de l’ordre des octets et un index btree simple avec la classe d’opérateur par défaut fait le travail.

Plus de détails, explications, exemples et liens dans ces réponses sur dba.SE:

• Motif correspondant à LIKE, SIMILAR TO ou à des expressions régulières dans PostgreSQL
• Comment est-ce que LIKE est implémenté?
• Trouver rapidement des chaînes similaires avec PostgreSQL

Peut-être que les plus rapides sont des modèles ancrés avec une sensibilité à la casse comme cela peut utiliser des index. c’est-à-dire qu’il n’y a pas de caractère générique au début de la chaîne de correspondance afin que l’exécuteur puisse utiliser un balayage de plage d’index. ( le commentaire pertinent dans le document est ici ) Lower et ilike perdront également votre capacité à utiliser l’index à moins que vous ne créiez spécifiquement un index à cette fin (voir les index fonctionnels ).

Si vous souhaitez rechercher une chaîne au milieu du champ, vous devez rechercher des index en texte intégral ou en sortinggrammes . Le premier d’entre eux est dans le kernel de Postgres, l’autre est disponible dans les modules consortingb.

Vous pouvez installer Wildspeed , un type d’index différent dans PostgreSQL. Wildspeed fonctionne avec% word% wildcards, pas de problème. L’inconvénient est la taille de l’index, qui peut être grande, très grande.

Veuillez exécuter la requête mentionnée ci-dessous pour améliorer les performances de la requête LIKE dans postgresql. créer un index comme celui-ci pour des tables plus grandes:

 CREATE INDEX  ON  USING btree ( text_pattern_ops) 

Vos requêtes similaires ne peuvent probablement pas utiliser les index que vous avez créés car:

1) votre critère LIKE commence par un caractère générique.

2) vous avez utilisé une fonction avec vos critères LIKE.