Forums PostgreSQL.fr

ladeche

Membre

[RESOLU]Non utilisation d'index sur un LIKE en pl/pgsql

Bonjour,

Soit une bête table avec une clef primaire et un champ varchar indexé :

CREATE TABLE matable
(
  id integer NOT NULL,
  chaine character varying,
  CONSTRAINT pk_id PRIMARY KEY (id )
) WITH (  OIDS=FALSE);
CREATE INDEX idx_chaine  ON matable  USING btree   (chaine COLLATE pg_catalog."default" );

Si je fais

 
SELECT * FROM matable where chaine = '000ZX34/14'; 
SELECT * FROM matable where chaine LIKE '000ZX34/14' ;

Dans les deux cas l'optimiseur utilise l'index idx_chaine.

Par contre si je fais le script suivant :

DO $$
DECLARE
    y record;
    x record;
BEGIN
FOR y IN (
    SELECT id,chaine
    FROM matable
    WHERE id < 200
    ) LOOP
    FOR x IN (SELECT count(1) nb
                 FROM matable m
                 WHERE m.chaine LIKE y.chaine
                 AND m.id <> y.id
                 )
       LOOP                                                             
        RAISE INFO '%-%-%',y.id,y.chaine,x.nb;
       END LOOP;
END LOOP;
END$$;

L'index ne sera pas utilisé dans la requête imbriquée (FOR x), à la place on a un seq scan. En remplaçant le like par =, l'index est utilisé.
J'ai essayé en ajoutant un index sur chaine avec la classe opérateur "varchar_pattern_ops" mais sans plus de succès.

Le bout de code ci-dessus est un peu idiot mais je voudrais surtout comprendre le fonctionnement du LIKE dans ce cas là et pourquoi il n'utilise pas l'index.

Merci.

Dernière modification par ladeche (08/01/2014 09:19:23)

gleu

Administrateur

Re : [RESOLU]Non utilisation d'index sur un LIKE en pl/pgsql

Le problème vient du fait que PostgreSQL met en cache le plan d'exécution de la requête avec PL/pgsql.

Quand vous mettez l'opérateur =, il s'agit d'une égalité pure. Si votre index est très discriminant, PostgreSQL choisira l'index une fois pour toutes, que ce soit une bonne idée ou pas. Quand vous mettez l'opérateur LIKE, il ne s'agit pas forcément d'une égalité pure. y.chaine peut contenir des caractères joker (_ et %) qui feront que PostgreSQL pourra ou non utiliser l'index. Comme il ne peut pas le savoir, il part d'emblé sur la seule solution acceptable (en terme de résultat juste) : le parcours séquentiel.

Le seul moyen d'éviter cela est de passer par une requête dynamique pour éviter la mise en cache du plan. Voir http://docs.postgresql.fr/9.3/plpgsql-s … cuting-dyn pour les détails.

---

Guillaume.

ladeche

Membre

Re : [RESOLU]Non utilisation d'index sur un LIKE en pl/pgsql

Merci pour cette réponse très claire.

Voila donc la transformation du code qui fonctionne :

DO $$
DECLARE
    y record;
    x record;
BEGIN
FOR y IN (
    SELECT id,chaine
    FROM matable
    WHERE id < 1000
    ) LOOP
      
    FOR x IN EXECUTE concat ('SELECT count(1) nb'
                            ,' FROM matable m' 
                            ,' WHERE m.chaine LIKE ',quote_literal(y.chaine)
                            ,' AND m.id <> ',y.id
                            )
    LOOP
        RAISE INFO '%-%-%',y.id,y.chaine,x.nb;
    END LOOP;
END LOOP;
END$$;

A noter qu'il faut impérativement ne pas mettre de parenthèses dans "FOR x IN EXECUTE" (autrement dit FOR x IN (EXECUTE...) ne fonctionne pas, alors que FOR x IN (SELECT ...) fonctionne).

gleu

Administrateur

Re : [RESOLU]Non utilisation d'index sur un LIKE en pl/pgsql

FOR x IN (EXECUTE...) ne fonctionne pas car ce qui est entre parenthèse doit être une requête. Or EXECUTE n'est pas une instruction du langage SQL.

---

Guillaume.