Forums PostgreSQL.fr

flo

Membre

Question sur les index multi-colonnes

Bonjour,

J'ai une table, qui ressemble à ceci :
id character(8) not null,
modulo numeric(2) not null,
numero numeric(2) not null,
service numerec(3) not null,
[...] autres colonnes.

clé primaire sur :
(modulo, id, numero, service)

c'est une base 8.1, et elle est actuellement en production.

Le souci,  c'est que j'ai des tas d'index définis dans tous les sens sur cette table, notamment des index sur :
(id, modulo)
(id, modulo, numero),
(modulo, id, service),
(id, numero, service). Je dois faire un peu de "ménage".
Le modulo est en fait calculé à partir de l'id (il ne dépend que de l'id)
La plupart des requêtes ont comme critère de sélection ou de jointure, soit id seul, soit id et modulo. Aucune n'utilise le modulo seul.

Mon idée de départ était de remettre dans l'ordre la clé primaire, pour avoir l'index sur id, puis modulo. Seulement vu que c'est en production, c'est un peu compliqué (il y a beaucoup de  clés étrangères qui en dépendent, et la table fait 7 millions de ligne). Pensez-vous que cela pose problème, dans ce cas de figure, d'avoir la clé primaire d'abord sur le modulo? C'est une base dont les temps de réponse en lecture sont plus importants pour le client que les écritures, et il y a bien plus de lectures que de mises à jour.

Et une autre question : j'ai fait un test :

explain analyze
select id from latable where id='111111' and modulo = '4';

et contrairement à ce que je pensais, c'est un index sur (modulo, id, service) qui est utilisé, alors que je pensais que ce serait un index dont les premiers champs étaient (id, modulo) qui serait utilisé (id est plus discriminant que  modulo). Avez-vous une idée de la raison de cela?

Merci d'avoir lu jusqu'au bout!

Flo

gleu

Administrateur

Re : Question sur les index multi-colonnes

Salut flo,

En effet, il y a du ménage à faire. Vire tout ce que tu peux et recrée seulement les index appropriés. D'après ce que tu dis, un index sur id, un autre sur modulo et un troisième sur id et modulo.

Quant à l'EXPLAIN de ta requête, un REINDEX pourrait faire du bien. Autre chose, tu es sûre de tes stats ? tu pourrais nous fournir le résultat complet du EXPLAIN ANALYZE ?

---

Guillaume.

Marc Cousin

Membre

Re : Question sur les index multi-colonnes

Si le modulo est calculé à partir de l'id, j'aurais aussi tendance à préconiser de ne JAMAIS le mettre dans une requête : ça veut dire qu'id et modulo sont liés, et c'est un coup à garantir des plans d'exécution faux :
Dans le cas simple de :

select id from latable where id='111111' and modulo = '4';

pour estimer le nombre d'enregistrements, postgresql va estimer le pourcentage d'enregistrements where id='111111', le multiplier par le nombre estimé d'enregistrements where modulo='4', et multiplier l'ensemble par le nombre estimé d'enregistrements dans la table (taille moyenne d'un enreg au dernier vacuum multipliée par taille de la table).
Estimation fausse garantie si modulo=f(id), puisque l'estimation part de l'hypothèse que les colonnes sont indépendantes.

Dernière modification par Marc Cousin (09/03/2010 15:14:05)

---

Marc.

flo

Membre

Re : Question sur les index multi-colonnes

l'explain analyze donne cela :
Index Scan using index_n_sre on latable  (cost=0.00..13.42 rows=3 width=12) (actual time=85.637..85.637 rows=0 loops=1)
  Index Cond: ((modulo = 4::numeric) AND (id = '111111'::bpchar))
Total runtime: 85.725 ms

Marc Cousin

Membre

Re : Question sur les index multi-colonnes

85 ms ça fait beaucoup pour ne rien ramener. Commence par faire un vacuum sur la table.

---

Marc.

flo

Membre

Re : Question sur les index multi-colonnes

Marc : après le vacuum, cela va un peu plus vite, mais il utilise toujours le même index.
Et pour les estimations, merci pour l'explication, je vais essayer de convaincre le chef pour la réécriture des requêtes, mais ce ne sera pas pour tout de suite.

Marc Cousin

Membre

Re : Question sur les index multi-colonnes

Passe aussi un analyze sur la table. Les stats ne sont peut être pas à jour… l'autovacuum, sur une 8.1, ça n'est pas par défaut.

---

Marc.

flo

Membre

Re : Question sur les index multi-colonnes

J'ai fait un reindex et un analyze, c'est un peu mieux (30 ms pour la même requête).

Marc Cousin

Membre

Re : Question sur les index multi-colonnes

Les performances sont elles meilleures si la requête n'a pas de critère sur modulo ?

---

Marc.

flo

Membre

Re : Question sur les index multi-colonnes

oui, 11 ms (il n'utilise pas le même index) :
explain analyze select id from service_rendu where id = '1000001'

"Index Scan using index_sre_bef on service_rendu  (cost=0.00..64.87 rows=16 width=12) (actual time=11.642..11.642 rows=0 loops=1)"
"  Index Cond: (id = '1000001'::bpchar)"
"Total runtime: 11.685 ms"

(je n'ai pas mis la même valeur, mais c'est comme tout à l'heure une valeur qui n'est pas présente dans la table)

flo

Membre

Re : Question sur les index multi-colonnes

Pour finir, merci gleu et Marc. J'ai gardé la clé primaire pour le moment telle qu'elle est, plus un index sur l'id. Pour le moment, pas de problème détecté.