Pourquoi mes requêtes PostgreSQL LIKE sont-elles si lentes et comment puis-je les accélérer ?

Requêtes PostgreSQL LIKE : goulots d'étranglement des performances et stratégies d'optimisation

Les requêtes

PostgreSQL LIKE, connues pour leur intensité de calcul, ont montré des performances incohérentes par rapport à une table de base de données spécifique. Alors que certaines requêtes se terminent en 200 à 400 millisecondes, d'autres prennent jusqu'à 30 secondes.

Limites d'indexation standard

Une première tentative d'utilisation d'un index b-tree sur le champ owner1 n'a pas réussi à améliorer les performances. Les expériences avec diverses LIKE variations de syntaxe n'ont également donné aucun résultat significatif.

Indices trigrammes : une solution supérieure

L'extension pg_trgm propose des index trigrammes (GIN/GiST) qui offrent des améliorations substantielles des performances pour les opérations LIKE et ILIKE, en particulier avec des chaînes plus longues. Il est important de noter que ces index prennent également en charge les mots de moins de trois caractères.

Optimisation de la correspondance des préfixes

Pour les recherches sans caractères génériques, envisagez ces alternatives :

• ^@ Opérateur/starts_with() Fonction (PostgreSQL 11) : L'opérateur ^@ et la fonction starts_with(), introduits dans PostgreSQL 11, offrent une correspondance de préfixe efficace lorsqu'ils sont utilisés avec les index SP-GiST .
• COLLATE "C" : Les index créés à l'aide de COLLATE "C" se comportent de la même manière que la classe d'opérateur text_pattern_ops, permettant une correspondance de préfixe optimisée avec les index b-tree.
• text_pattern_ops Classe d'opérateur : Cette classe d'opérateur crée des index b-tree spécialement conçus pour les modèles ancrés à gauche (pas de caractère générique de début).

Ressources supplémentaires

Pour une compréhension plus détaillée de la correspondance de modèles dans PostgreSQL, reportez-vous à ces ressources :

• Correspondance de modèles avec LIKE, SIMILAR TO ou des expressions régulières
• Comment LIKE est-il implémenté ?
• Trouver efficacement des chaînes similaires dans PostgreSQL

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!