Forums PostgreSQL.fr

lolotte35

Membre

index varchar_pattern_ops fonctionne pour un equal ?

Bonjour,

je suis en Postgres 9.1.1, j'ai un index en varchar_pattern_ops sur une colonne, je pensais qu'il n'était utilisé que pour les LIKE.
Mais là j'ai fait une requête avec un = et je vois dans mon explain qu'il utilise mon index!

ça me va très bien, mais c'est normal?

Est ce que c'est uniquement parce que la table ne contient pas beaucoup d'enregistrement (53 000) ?
Ou alors le varchar_pattern_ops fonctionne pour les LIKE et les = ?

Car sur d'autres tables ( beaucoup plus volumineuses) nous avons créé 2 indexs sur la même colonne, l'un en varchar_pattern_ops et l'autre sans pour les cas où nous utilisons un LIKE ou un equal.

Merci pour vos réponses

arthurr

Membre

Re : index varchar_pattern_ops fonctionne pour un equal ?

bonjour

Dans la doc http://docs.postgresqlfr.org/9.0/indexes-opclass.html , il est bien précisé que ce type d'index ne travaille pas sur les "locales" de l'utilisateur donc ne permettent pas les comparaisons du type >,<, ...
mais ils savent travailler sur des égalités ("Néanmoins, des comparaisons d'égalité ordinaires peuvent utiliser ces classes d'opérateur.")

Marc Cousin

Membre

Re : index varchar_pattern_ops fonctionne pour un equal ?

C'est normal que les varchar_pattern_ops marchent aussi pour les =. En fait ce qu'explique la doc (en essayant de faire plus clair), c'est que les opérateurs par défauts utilisés pour indexer un champ texte travaillent avec la collation de la colonne, qui sera vraisemblablement un truc du genre fr_FR.utf8. Ce dont on parle, c'est d'une fonction à la cmp ou <=> qu'on rencontre dans d'autres langages (il va retourner par exemple -1 si a<b, 0 si a=b, 1 si a>b). Le varchar_pattern_ops utilise aussi un opérateur de comparaison entre les chaînes, mais il les compare «bêtement» par leur valeur ascii, octet par octet. On a donc bien aussi la possibilité d'utiliser =.

Là où le varchar_pattern_ops fait des choses où l'opérateur normal n'est pas bon, c'est dans les clauses LIKE 'toto%', ou ~ 'toto(a|k).*titi' (expression régulière). L'optimiseur sait, quand il a un varchar_pattern_ops, qu'il peut l'utiliser pour parcourir des ranges de valeurs et donc écrémer fortement par le parcours de l'index les valeurs à retourner.

Par exemple, le LIKE 'toto%' va être transformé au niveau du parcours de l'index en un ( > 'toto' and <'totp' )

Avec un index qui a une collation arbitraire, cette transformation est plus compliquée à faire. Je ne dis pas qu'elle serait impossible, mais la transformation dépendrait de la collation (par exemple, où arrive un caractère accentué  dans le tri alphabêtique ?), et ce n'est pas implémenté au niveau de l'optimiseur.

Et donc pour revenir à la question de départ, oui, les *_pattern_ops marchent pour les =. À partir de la 8.4. Avant, ils n'étaient pas utilisables pour les =.

En pré-8.4, on partait du fait que l'égalité entre 2 chaînes ne voulait pas forcément dire que les deux chaînes étaient égales au niveau octet (plusieurs façons d'écrire le même mot). Le cas est suffisamment peu fréquent, et peu utile dans la vraie vie, qu'en 8.4 et plus, PostgreSQL décide que deux chaînes sont égales si et seulement si elles ont exactement la même valeur binaire, ce qui permet d'utiliser les pattern_ops pour les tests d'égalité, et ne pas se trimbaler d'index supplémentaire juste pour l'égalité.

Ça ne veut pas dire que les index normaux ne servent plus à rien… Ils restent les seuls qui retournent les données triées avec le bon tri alphabétique. Ils ont donc aussi leurs cas d'utilisation (tous les cas, sauf si on veut faire du like ou de l'expression régulière).

---

Marc.

lolotte35

Membre

Re : index varchar_pattern_ops fonctionne pour un equal ?

ok merci, donc si on utilise des order sur une colonne, c'est bien de garder son index normal.

Merci pour toutes ces informations, c'est vrai qu'avant nous étions en 8.2.14 et que nous avions pris l'habitude de créer 2 indexs sur les colonnes où nous utilisions des LIKE et des EQUAL.

Marc Cousin

Membre

Re : index varchar_pattern_ops fonctionne pour un equal ?

Exactement.

---

Marc.