Correspondance de modèles avec LIKE, SIMILAR TO ou expressions régulières dans PostgreSQL

Votre requête est à peu près optimale. La syntaxe « ne sera pas beaucoup plus courte, la requête » ne sera pas beaucoup plus rapide:

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; 

Si oui ous voulez vraiment raccourcir la syntaxe , utilisez une expression régulière avec branches :

... WHERE name ~ "^(B|D).*" 

Ou légèrement plus rapide, avec une classe de caractères :

... WHERE name ~ "^[BD].*" 

Un test rapide sans index donne des résultats plus rapides que pour SIMILAR TO dans les deux cas pour moi.
Avec un index B-Tree approprié en place, LIKE remporte cette course par ordre de grandeur.

Lisez les notions de base sur la correspondance de motifs dans le manuel .

Index pour des performances supérieures

Si vous êtes concerné avec les performances, créez un index comme celui-ci pour des tables plus grandes:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Rend ce type de requête plus rapide de plusieurs ordres de grandeur. Des considérations spéciales sappliquent à lordre de tri spécifique aux paramètres régionaux. En savoir plus sur les classes dopérateurs dans le manuel . Si vous utilisez la locale  » C  » (la plupart des gens ne le font pas), un index simple (avec la classe dopérateur par défaut) faire.

Un tel index nest bon que pour les motifs ancrés à gauche (correspondant au début de la chaîne).

SIMILAR TO ou les expressions régulières avec des expressions de base ancrées à gauche peuvent également utiliser cet index. Mais pas avec des branches (B|D) ou des classes de caractères [BD] (au moins dans mes tests sur PostgreSQL 9.0).

Les correspondances de trigrammes ou la recherche de texte utilisent des index spéciaux GIN ou GiST.

Vue densemble des opérateurs de correspondance de modèles

• LIKE ( ~~ ) est simple et rapide mais limité dans ses capacités.
  ILIKE ( ~~* ) la variante insensible à la casse.
  pg_trgm étend la prise en charge dindex pour les deux.

• ~ (correspondance dexpression régulière) est puissant mais plus complexe et peut être lent pour autre chose que des expressions de base.

• SIMILAR TO est simplement inutile . Un métis particulier de LIKE et dexpressions régulières. Je ne lutilise jamais. Voir ci-dessous.

• % est lopérateur de  » similarité « , fourni par le module supplémentaire pg_trgm. Voir ci-dessous.

• @@ est lopérateur de recherche de texte. Voir ci-dessous.

pg_trgm – correspondance de trigrammes

Commençant par PostgreSQL 9.1 vous pouvez faciliter lextension pg_trgm pour fournir une prise en charge dindex pour tout LIKE / ILIKE modèle (et modèles dexpression régulière simples avec ~) en utilisant un Index GIN ou GiST.

Détails, exemple et liens:

• Comment LIKE est-il implémenté?

pg_trgm fournit également ces opérateurs :

• % – le  » similarité  » opérateur
• <% (commutateur: %>) – le  » similitude_mots  » opérateur dans Postgres 9.6 ou version ultérieure
• <<% (commutateur: %>>) – le  » strict_word_similarity  » opérateur dans Postgres 11 ou version ultérieure

Recherche de texte

Est un type spécial de correspondance de modèle avec une infrastructure et des types dindex séparés. Il utilise des dictionnaires et des souches et est un excellent outil pour trouver des mots dans les documents, en particulier pour les langues naturelles.

Correspondance de préfixe est également pris en charge:

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

Ainsi que recherche de phrases depuis Postgres 9.6:

• Comment rechercher des mots avec trait dunion dans la recherche en texte intégral PostgreSQL?

introduction dans le manuel et présentation des opérateurs et des fonctions .

Outils supplémentaires pour la correspondance floue des chaînes

Le module supplémentaire fuzzystrmatch offre dautres options, mais les performances sont généralement inférieures à toutes celles ci-dessus.

En particulier, diverses les implémentations de la fonction levenshtein() peuvent être instrumentales.

Pourquoi les expressions régulières (~) sont-elles toujours plus rapides que SIMILAR TO?

La réponse est simple. Les expressions SIMILAR TO sont réécrites en expressions régulières en interne. Ainsi, pour chaque expression SIMILAR TO, il y a au moins une expression régulière plus rapide (qui évite la surcharge de réécriture de lexpression). Lutilisation de SIMILAR TO jamais ne permet pas de gagner en performances.

Et les expressions simples qui peuvent être faites avec LIKE (~~) sont plus rapides avec LIKE de toute façon.

SIMILAR TO nest supporté que dans PostgreSQL car il sest retrouvé dans les premières versions du standard SQL. Ils ne sen sont toujours pas débarrassés. Mais il est prévu de le supprimer et dinclure des correspondances dexpression régulière à la place – du moins cest ce que jai entendu.

EXPLAIN ANALYZE le révèle. Essayez avec nimporte quelle table!

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 

Révèle:

... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) 

SIMILAR TO a été réécrit avec une expression régulière (~).

Performances ultimes dans ce cas particulier

Mais EXPLAIN ANALYZE en révèle plus. Essayez, avec lindex susmentionné en place:

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; 

Révèle:

... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text)) 

En interne, avec un index qui nest pas compatible avec les paramètres régionaux (text_pattern_ops ou en utilisant les paramètres régionaux C) les expressions simples ancrées à gauche sont réécrites avec ces opérateurs de modèle de texte: ~>=~, ~<=~, ~>~, ~<~. Cest le cas pour ~, ~~ ou SIMILAR TO.

Il en va de même pour les index sur les types varchar avec varchar_pattern_ops ou char avec bpchar_pattern_ops.

Donc, appliqué à la question dorigine, voici le moyen le plus rapide possible :

SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 

Bien sûr, si vous devez rechercher des initiales adjacentes , vous pouvez simplifier davantage:

WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C 

Le gain par rapport à lutilisation simple de ~ ou ~~ est minime. Si les performances ne sont pas votre exigence primordiale, vous devez simplement vous en tenir aux opérateurs standard – pour arriver à ce que vous avez déjà dans la question.

Commentaires

• LOP na ‘ aucun index sur le nom, mais savez-vous, si tel était le cas, sa requête dorigine impliquerait-elle 2 recherches de plage et similar un scan?
• @MartinSmith: Un test rapide avec EXPLAIN ANALYZE montre 2 scans dindex bitmap.Plusieurs analyses dindex bitmap peuvent être combinées assez rapidement.
• Merci. Il y aurait donc un kilométrage en remplaçant OR par UNION ALL ou en remplaçant name LIKE 'B%' par name >= 'B' AND name <'C' dans Postgres?
• @MartinSmith: UNION a gagné ‘ t mais, oui, combiner les plages en une seule clause WHERE accélérera la requête. Jai ajouté plus à ma réponse. Bien sûr, vous devez tenir compte de votre localisation. La recherche tenant compte des paramètres régionaux est toujours plus lente.
• @a_horse_with_no_name: Je ne mattends pas. Les nouvelles capacités de pg_tgrm avec les index GIN sont un régal pour la recherche de texte générique. Une recherche ancrée au début est déjà plus rapide que cela.

Que diriez-vous dajouter une colonne au table. Selon vos besoins réels:

person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) 

PostgreSQL ne prend pas en charge les colonnes calculées dans les tables de base à la SQL Serveur , mais la nouvelle colonne peut être gérée via un déclencheur. Évidemment, cette nouvelle colonne serait indexée.

Alternativement, un index sur une expression vous donnerait la même chose, moins chère. Par exemple:

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Les requêtes qui correspondent à lexpression dans leurs conditions peuvent utiliser cet index.

De cette façon, latteinte des performances est prise lorsque les données sont créées ou modifiées, ce qui peut ne convenir quà un environnement à faible activité (cest-à-dire beaucoup moins décritures que de lectures).

Vous pourriez essayer

SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 

Je ne sais pas si ce qui précède ou votre expression originale est commercialisable dans Postgres.

Si vous créez lindex suggéré, vous seriez également intéressé de savoir comment ceci se compare aux autres options.

SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 

Commentaires

• Cela a fonctionné et jai eu un coût de 1.19 où javais 1.25. Merci!

Ce que jai fait dans le passé, face à un problème de performances similaire, cest de incrémentez le caractère ASCII de la dernière lettre et effectuez un BETWEEN. Vous obtenez alors les meilleures performances, pour un sous-ensemble de la fonctionnalité LIKE. Bien sûr, cela ne fonctionne que dans certaines situations, mais pour les ensembles de données ultra-volumineux où vous « recherchez un nom par exemple, cela rend les performances abyssales à acceptables.

Très vieille question, mais jai trouvé une autre solution rapide à ce problème:

SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 

Depuis la fonction ascii ( ) ne regarde que le premier caractère de la chaîne.

Commentaires

• Cela utilise-t-il un index sur (name)?

Pour vérifier les initiales, jutilise souvent le cast en "char" (avec les guillemets doubles). Ce nest pas portable, mais très rapide. En interne, il détruit simplement le texte et renvoie le premier caractère, et les opérations de comparaison « char » sont très rapides car le type est de longueur fixe de 1 octet:

SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 

Notez que la conversion en "char" est plus rapide que la slution ascii() par @ Sole021, mais elle nest pas compatible UTF8 (ou tout autre encodage pour qui importe), renvoyant simplement le premier octet, donc ne devrait être utilisé que dans les cas où la comparaison est avec des caractères ASCII 7 bits simples.

Il existe deux méthodes non encore mentionnées pour traiter de tels cas:

1. index partiel (ou partitionné – sil est créé manuellement pour une plage complète) – plus utile lorsque seul un sous-ensemble de données est requis (par exemple lors dune maintenance ou temporaire pour certains rapports):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. partitionner la table elle-même (en utilisant le premier caractère comme clé de partitionnement) – cette technique est en particulier le moût h considérant dans PostgreSQL 10+ (partitionnement moins pénible) et 11+ (élagage de partition lors de lexécution de la requête).

De plus, si les données dune table sont triées, on peut bénéficier de lutilisation de l index BRIN (sur le premier caractère).

Probablement plus rapide pour effectuer une comparaison avec un seul caractère:

SUBSTR(s.name,1,1)="B" OR SUBSTR(s.name,1,1)="D" 

Commentaires

• Non vraiment. column LIKE 'B%' sera plus efficace que lutilisation de la fonction de sous-chaîne sur la colonne.