index ne prenant pas en compte les accents

Jiff · 30/12/2011 03:14:00

Salut forumers,

J'ai besoin d'indexer certaines colonnes case insensitive (ça, pas de PB) mais aussi et surtout en normalisant les accentuées.

Le soucis c'est que si unaccent fait bien son boulot, PG refuse de me créer un index l'utilisant (non-IMMUTABLE); d'après la ML de PG je pourrai forcer la construction mais ça ne serait pas tellement sérieux, vu que le jour de la V1.0 je ne suis pas sûr d'avoir un unaccent.rules complet pour toutes les langues CE & UE (je n'utilise que ce dico puisque je n'ai pas besoin de recherche textuelle... mais ça pourrait changer).

Donc, mon soucis Pal est de convertir *toutes* les accentuées en non-accentuées; Firebird a UNICODE_CI_AI, mais ça ne semble pas exister dans PG.

A vot'bon coeur pour une solution viable:)

gleu · 30/12/2011 09:13:48

J'utiliserais translate comme ceci : translate(la_colonne, 'àâäéèêëîïùûü', 'aaaeeeeiiuuu')

(évidemment, faut ajouter les autres caractères accentués, je n'en ai fait que quelques-uns)

Jiff · 30/12/2011 16:30:36

Ok, mais est-ce que c'est (très) rapide?
Parce que cette nuit j'ai trouvé une solution en contournant le PB de l'immutabilité, non-satisfaisante parce que correcte pour une recherche sur le contenu entier de la colonne, mais très lente sur une recherche partielle:

CREATE FUNCTION erpunaccent(text) RETURNS text AS $$
    SELECT unaccent($1);
$$ LANGUAGE sql  IMMUTABLE;

Recherche partielle sur un index normal:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(erpunaccent(name)) LIKE 'ogvvatoies%';
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on tst1m  (cost=0.00..28117.27 rows=500 width=100) (actual time=1.284..3569.742 rows=1 loops=1)
   Filter: (lower(erpunaccent((name)::text)) ~~ 'ogvvatoies%'::text)
 Total runtime: 3569.815 ms
(3 lignes)

Recherche partielle sur mon index case a accent insensitive:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE name LIKE 'oGvvÀtÖiÉ%';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on tst1m  (cost=0.00..2867.01 rows=10 width=100) (actual time=0.071..140.336 rows=1 loops=1)
   Filter: ((name)::text ~~ 'oGvvÀtÖiÉ%'::text)
 Total runtime: 140.418 ms
(3 lignes)

Ce que je n'arrive pas à comprendre, c'est que pour une recherche partielle l'index ne soit pas utilisé (en recherche std, il l'est et on tourne à ~0.177ms) mais surtout que ça soit 3x plus lent avec le mien qu'avec un index standard (à moins que l'index ne soit recalculé à la volée à chaque accès?)

Donc ma seconde question est: est-ce que je risque d'avoir le même PB avec TRANSLATE, et si oui quelle est la meilleure façon d'intégrer l'une ou l'autre méthode pour alimenter une colonne supplémentaire, étant donné qu'on ne peut pas définir un default à partir d'une autre colonne lors de la création de la table (un trigger AFTER INSERT & UPDATE?)

Jiff · 30/12/2011 16:34:12

Woops (dommage qu'il n'y ait pas de preview pour les réponses:(
Les résultats sont bien sûr inversés et la différence n'est par de 3x mais de 25.5x.

frost242 · 30/12/2011 16:52:16

Ce que je vois, c'est que votre nouvel index n'est utilisé dans aucun cas (cf première ligne de l'explain qui donne un seq scan, donc une lecture séquentielle pour les deux requêtes).
La différence de temps d'exécution s'explique car pour la première requête, PostgreSQL va calculer le résultat de lower(erpunaccent(name)) pour chaque ligne lues et le comparer à 'ogvvatoies%' avec le LIKE. Dans le second cas, PostgreSQL va comparer directement la colonne name à 'oGvvÀtÖiÉ%', mais sans le moindre calcul.

Voici un exemple d'index sur fonction, chose que vous avez probablement faite:

postgres=# EXPLAIN SELECT * FROM test WHERE nom = 'tata';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Only Scan using idx_test_nom on test  (cost=0.00..4.30 rows=1 width=5)
   Index Cond: (nom = 'tata'::text)
(2 lignes)

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE nom = 'tata';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_test_nom on test  (cost=0.00..8.45 rows=9 width=5) (actual time=0.070..0.073 rows=1 loops=1)
   Index Cond: (nom = 'tata'::text)
 Total runtime: 0.135 ms
(3 lignes)

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) = 'tata';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..5092.17 rows=1311 width=5) (actual time=278.159..278.161 rows=1 loops=1)
   Filter: (lower(nom) = 'tata'::text)
   Rows Removed by Filter: 262144
 Total runtime: 278.215 ms
(4 lignes)

postgres=# CREATE INDEX idx_test_lower_nom ON test (lower(nom));
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) = 'tata';
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=26.44..1261.33 rows=1311 width=5) (actual time=0.272..0.273 rows=1 loops=1)
   Recheck Cond: (lower(nom) = 'tata'::text)
   ->  Bitmap Index Scan on idx_test_lower_nom  (cost=0.00..26.11 rows=1311 width=0) (actual time=0.253..0.253 rows=1 loops=1)
         Index Cond: (lower(nom) = 'tata'::text)
 Total runtime: 0.343 ms
(5 lignes)

Ensuite, pour utiliser LIKE, n'oubliez pas de créer l'index sur fonction avec l'option varchar_pattern_ops:

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) LIKE 'tat%';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..5092.17 rows=1311 width=5) (actual time=244.566..244.568 rows=1 loops=1)
   Filter: (lower(nom) ~~ 'tat%'::text)
   Rows Removed by Filter: 262144
 Total runtime: 244.620 ms
(4 lignes)

postgres=# DROP INDEX idx_test_lower_nom ;
DROP INDEX
postgres=# CREATE INDEX idx_test_lower_nom ON test (lower(nom) varchar_pattern_ops);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE lower(nom) LIKE 'tat%';
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=29.72..1264.61 rows=1311 width=5) (actual time=0.262..0.264 rows=1 loops=1)
   Filter: (lower(nom) ~~ 'tat%'::text)
   ->  Bitmap Index Scan on idx_test_lower_nom  (cost=0.00..29.39 rows=1311 width=0) (actual time=0.222..0.222 rows=1 loops=1)
         Index Cond: ((lower(nom) ~>=~ 'tat'::text) AND (lower(nom) ~<~ 'tau'::text))
 Total runtime: 0.335 ms
(5 lignes)

Dernière modification par frost242 (30/12/2011 16:53:33)

gleu · 30/12/2011 16:58:22

Il n'est pas possible d'utiliser un index lors de l'utilisation d'un LIKE avec une fonction en argument gauche et une chaîne dotée d'un pourcent en argument droit. Par contre, l'index sera utilisé pour les opérateurs =, >, <, etc. Ensuite, aucun des deux exemples ne montre une utilisation de l'index.

Concernant translate, vous aurez le mếme comportement avec un LIKE.

Et pour la dernière question, il n'y a que le trigger.

Jiff · 30/12/2011 17:43:16

@gleu: Non, il semble que tu te trompes qq part (ou bien c'est moi qui ai de la m..e dans les yeux, ce qui est tt à fait poss avec 5H de sommeil: ça me tracassait graâve).
@frost: YES, j'ai recréé la même Fn mais en utilisant VARCHAR au lieu de TEXT:

CREATE OR REPLACE FUNCTION jyunaccent(varchar) RETURNS varchar AS $$
   SELECT unaccent($1);
$$ LANGUAGE sql  IMMUTABLE;

Puis j'ai lû la doc des indexes, supprimé l'index original et recréé avec ce que tu m'as conseillé:

CREATE INDEX tst1m_name_lu_key ON tst1m(lower(jyunaccent(name)) varchar_pattern_ops);

Et là, le résultat à quelque peu changé...:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(jyunaccent(name)) LIKE 'ogvvatoie%';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tst1m  (cost=17.90..1211.87 rows=500 width=100) (actual time=0.253..0.255 rows=1 loops=1)
   Filter: (lower((jyunaccent(name))::text) ~~ 'ogvvatoie%'::text)
   ->  Bitmap Index Scan on tst1m_name_lu_key  (cost=0.00..17.78 rows=500 width=0) (actual time=0.054..0.054 rows=1 loops=1)
         Index Cond: ((lower((jyunaccent(name))::text) ~>=~ 'ogvvatoie'::text) AND (lower((jyunaccent(name))::text) ~<~ 'ogvvatoif'::text))
 Total runtime: 0.337 ms
(5 lignes)

Ce qui n'est pas si mal sur une table de test de 10,000 rows et une colonne contenant une string aléatoire de longueur [14-32].

Merci beaucoup, c'est génial parce que ça m'emmerdait vraiment d'ajouter une colonne et un trigger rien que pour ce type de recherche (ET du coup je vais passer en revue tout le script de création de la DB parce que ça n'est sûrement pas un cas isolé, y'a du gain dans l'air:)!

gleu · 30/12/2011 18:25:47

Rahh, crotte, j'ai encore oublié ce foutu varchar_pattern_ops. Désolé.

cedric · 31/12/2011 13:49:04

Jiff a écrit :

@frost: YES, j'ai recréé la même Fn mais en utilisant VARCHAR au lieu de TEXT:
!

vous pouvez rester sur du TEXT, mais il faut utiliser la classe d'opérateur correspondant: text_pattern_ops
cf http://www.postgresql.org/docs/8.1/stat … class.html

Jiff · 31/12/2011 14:56:56

@gleu: C'est le mélange crack/héro/corned-beef qui brouille l'écoute - (c'est terriblement fort le corned-beef:)
@cedric: Bizarrement j'ai fait le test... en me trompant (I/O=text, mais varchar_pattern_ops) et le résultat a été identique; étant donné ce que disent différentes docs et papiers que j'ai pu lire sur le web, je ne serais pas plus étonné que cela qu'il ne s'agisse que d'un alias de l'un vers l'autre.
Maintenant, il va falloir que je comprenne pourquoi, avec svr & clients en fr_FR.utf-8, j'ai des bizarreries dans l'ordonnance des rows: malgré un ORDER BY...DESC (d° en ASC, œuf corse), j'ai un mix up de rows commençant par 'Z', 'z', '-'(WTF?) et 'ẞ'(RE-WTF?); sachant que la colonne en question est aléatoirement remplie par tous les caractères CE possibles.
Mais j'ai bien peur que ça soit dû à la collation, étant donnée que le planner ne se trompe pas d'index:

EXPLAIN SELECT * FROM tst1m ORDER BY note DESC;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan Backward using ix_tst1m_note on tst1m  (cost=0.00..27831.58 rows=200001 width=120)
(1 ligne)

Tiens: je n'avais pas remarqué qu'en DESC les 'E' diacritiques (Ёё) arrivaient AVANT les 'Z':(((

#1 30/12/2011 03:14:00

index ne prenant pas en compte les accents

#2 30/12/2011 09:13:48

Re : index ne prenant pas en compte les accents

#3 30/12/2011 16:30:36

Re : index ne prenant pas en compte les accents

#4 30/12/2011 16:34:12

Re : index ne prenant pas en compte les accents

#5 30/12/2011 16:52:16

Re : index ne prenant pas en compte les accents

#6 30/12/2011 16:58:22

Re : index ne prenant pas en compte les accents

#7 30/12/2011 17:43:16

Re : index ne prenant pas en compte les accents

#8 30/12/2011 18:25:47

Re : index ne prenant pas en compte les accents

#9 31/12/2011 13:49:04

Re : index ne prenant pas en compte les accents

#10 31/12/2011 14:56:56

Re : index ne prenant pas en compte les accents

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux