Variations des performances de la requête LIKE de PostgreSQL

Question

J'ai constaté une variation assez importante des temps de réponse en ce qui concerne LIKE à une table particulière de ma base de données. Parfois, j'obtiens des résultats dans un délai de 200 à 400 ms (très acceptable), mais d'autres fois, cela peut prendre jusqu'à 30 secondes.

Je comprends que LIKE Les requêtes sont très gourmandes en ressources, mais je ne comprends pas pourquoi il y a une telle différence dans les temps de réponse. J'ai construit un index btree sur le fichier owner1 mais je ne pense pas qu'il soit utile dans le cas de LIKE questions. Quelqu'un a-t-il une idée ?

Exemple de SQL :

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

J'ai également essayé :

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

Et.. :

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

Avec des résultats similaires.
Nombre de lignes de la table : environ 95 000.

Answer 1

Les STF ne prennent pas en charge LIKE

En réponse précédemment acceptée n'était pas correcte. Recherche en texte intégral avec ses index en texte intégral est pas pour les LIKE Il a ses propres opérateurs et ne fonctionne pas pour les chaînes de caractères arbitraires. Il opère sur mots sur la base de dictionnaires et de troncs d'arbre. Il s'agit hace soutien correspondance des préfixes pour les mots mais pas avec le LIKE de l'opérateur :

• Obtenir une correspondance partielle à partir d'une colonne TSVECTOR indexée par GIN

Indice de trigramme pour LIKE

Installer le module supplémentaire pg_trgm qui fournit des classes d'opérateurs pour les Index des trigrammes GIN et GiST pour soutenir a LIKE y ILIKE modèles et pas seulement ceux qui sont ancrés à gauche :

Exemple d'index :

CREATE INDEX tbl\_col\_gin\_trgm\_idx  ON tbl USING **gin**  (col **gin\_trgm\_ops**);

Ou bien :

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%';
SELECT \* FROM tbl WHERE col LIKE '%foo%';   -- works with leading wildcard, too
SELECT \* FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

Trigrammes ? Qu'en est-il des chaînes plus courtes ?

Mots avec 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 lors de la détermination de l'ensemble des trigrammes contenus dans la chaîne.

Et les modèles de recherche de moins de 3 lettres ? Le manuel :

Pour les deux LIKE et les recherches par expressions régulières, gardez à l'esprit qu'un sans trigrammes extractibles dégénérera en une recherche d'index complet.

Cela signifie que les balayages d'index / d'index bitmap fonctionnent toujours (les plans de requête pour les instructions préparées ne seront pas interrompus), mais qu'ils ne vous permettront pas d'obtenir de meilleures performances. En général, ce n'est pas une grosse perte, car les chaînes de 1 ou 2 lettres ne sont guère sélectives (plus de quelques pour cent des correspondances de la table sous-jacente) et la prise en charge de l'index n'améliorerait pas (beaucoup) les performances au départ, car un balayage complet de la table est plus rapide.

Correspondance des préfixes

Les modèles de recherche sans caractère générique en tête : col LIKE 'foo%' .

^@ opérateur / starts_with() fonction

Citant le notes de mise à jour de Postgres 11 :

Ajouter l'opérateur de correspondance de préfixe texte ^@ texte, qui est pris en charge par SP-GiST (Ildus Kurbangaliev)

Cette méthode est similaire à l'utilisation de var LIKE 'word%' avec un index btree, mais elle est plus efficace.

Exemple de requête :

SELECT \* FROM tbl WHERE **col ^@ 'foo'**;  -- no added wildcard

Mais le potentiel de l'opérateur et de la fonction reste limité jusqu'à ce que La prise en charge des planificateurs est améliorée dans Postgres 15 et le ^@ est documenté correctement. Les notes de mise à jour :

Autoriser le ^@ et l'opérateur "start-with" et l'opérateur "start-with" et l'opérateur "start-with". starts_with() à la fonction utiliser les index btree si l'on utilise la collation C (Tom Lane)

Auparavant, ces derniers ne pouvaient utiliser que des SP-GiST index.

COLLATE "C"

Depuis Postgres 9.1, un index avec COLLATE "C" offre les mêmes fonctionnalités que la classe d'opérateurs text_pattern_ops décrite ci-dessous. Voir aussi

• Y a-t-il une différence entre text_pattern_ops et COLLATE "C" ?

text_pattern_ops (réponse originale)

Pour seulement ancré à gauche (sans caractère générique), vous obtenez l'optimum avec une valeur de classe d'opérateurs pour un index btree : text_pattern_ops o varchar_pattern_ops . Ces deux fonctionnalités sont intégrées au système Postgres standard, aucun module supplémentaire n'est nécessaire. Performances similaires, mais index 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 faire fonctionner votre base de données avec l'option 'C' locale (en fait no locale), tout est de toute façon trié selon l'ordre des octets et un simple index btree avec la classe d'opérateurs par défaut fait l'affaire.

---

Pour en savoir plus

• Recherche de motifs avec LIKE, SIMILAR TO ou expressions régulières dans PostgreSQL
• Comment LIKE est-il mis en œuvre ?
• Trouver rapidement des chaînes de caractères similaires avec PostgreSQL

Answer 2

Les plus rapides sont peut-être les motifs ancrés avec la distinction entre majuscules et minuscules qui peuvent utiliser des index, c'est-à-dire qu'il n'y a pas de joker au début de la chaîne de correspondance, ce qui permet à l'exécuteur d'utiliser un balayage de plage d'index. ( le commentaire pertinent dans la documentation est ici ) Lower and ilike perdra également la possibilité d'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 de caractères au milieu du champ, vous pouvez consulter le site suivant texte complet o index des trigrammes . Le premier se trouve dans le noyau de Postgres, l'autre est disponible dans les modules contrib.

Answer 3

J'ai récemment rencontré un problème similaire avec une table contenant 200 000 enregistrements et je dois effectuer des requêtes LIKE répétées. Dans mon cas, la chaîne recherchée a été fixée. Les autres champs variaient. C'est pourquoi j'ai pu réécrire :

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

comme

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

J'ai été ravi de voir que les requêtes revenaient rapidement et j'ai vérifié que l'index était utilisé avec EXPLAIN ANALYZE :

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms

Answer 4

Vous pouvez installer Vitesse du vent un type d'index différent dans PostgreSQL. Wildspeed fonctionne avec les caractères génériques %word%, sans problème. L'inconvénient est la taille de l'index, qui peut être grande, très grande.

Answer 5

Lorsque vous utilisez une clause sur une colonne avec des fonctions telles que LIKE, ILIKE, upper, lower etc. Postgres ne prend pas en compte votre index normal. Il effectuera un balayage complet de la table en passant par chaque ligne, ce qui le rendra lent.

La bonne méthode consiste à créer un nouvel index en fonction de votre requête. Par exemple, si je veux faire correspondre une colonne sans tenir compte de la casse et que ma colonne est un varchar. Vous pouvez alors procéder comme suit.

create index ix_tblname_col_upper on tblname (UPPER(col) varchar_pattern_ops);

De même, si votre colonne est un texte, vous devez faire quelque chose comme ceci

create index ix_tblname_col_upper on tblname (UPPER(col) text_pattern_ops);

De même, vous pouvez remplacer la fonction supérieure par toute autre fonction de votre choix.