Pourquoi les requêtes PostgreSQL LIKE sont-elles si lentes et comment puis-je les rendre plus rapides ?

Optimisation des requêtes PostgreSQL LIKE : résolution des problèmes de performances

Les requêtes

PostgreSQL LIKE, utilisées pour la correspondance de sous-chaînes, présentent souvent des performances imprévisibles, allant de quelques millisecondes à quelques secondes. Cet article explore les causes profondes et les stratégies d'optimisation efficaces.

Comprendre la variabilité des performances

Les incohérences de performances proviennent des demandes de ressources inhérentes aux LIKE requêtes. Contrairement aux contrôles d'égalité, qui exploitent efficacement les index, les LIKE requêtes nécessitent généralement des analyses de table complètes.

Limitations de l'index

Bien que les index B-tree soient cruciaux pour les performances, ils ne sont pas intrinsèquement adaptés aux LIKE opérations. Un index sur une colonne (par exemple, owner1) améliore les correspondances exactes (SELECT * FROM parcels WHERE owner1 = 'John Doe'), mais offre un avantage limité pour les requêtes LIKE.

Techniques d'amélioration des performances

Plusieurs techniques peuvent améliorer considérablement les LIKE performances des requêtes :

Recherche en texte intégral (FTS)

Le FTS de PostgreSQL permet une recherche de texte efficace. Cependant, il ne prend pas directement en charge l'opérateur LIKE et opère sur des mots, et non sur des sous-chaînes arbitraires.

Optimisation de la correspondance des préfixes

Pour les modèles sans caractères génériques de début (par exemple, 'foo%'), utilisez des classes d'opérateurs comme text_pattern_ops ou varchar_pattern_ops avec un index B-tree. Ceux-ci optimisent la correspondance des modèles ancrés à gauche.

Index trigrammes pour une correspondance flexible

L'extension pg_trgm, couplée aux index GIN ou GiST, propose une indexation trigramme. Cela prend en charge tous les modèles LIKE et ILIKE, quelle que soit la position du caractère générique.

Autres considérations

• Longueur de la chaîne : Les mots indexés de moins de trois caractères fonctionneront, mais les modèles contenant moins de trois caractères peuvent toujours déclencher des analyses d'index complètes.
• PostgreSQL 11 et au-delà : PostgreSQL 11 a introduit l'opérateur ^@ et la fonction starts_with() pour une correspondance efficace des préfixes avec les index SP-GiST.
• Collation et indexation : L'utilisation de COLLATE "C" avec des index ou des classes d'opérateurs adaptés à des classements spécifiques peut optimiser la correspondance des préfixes et les LIKE performances des requêtes pour des scénarios spécifiques.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!