Forums PostgreSQL.fr

guk92

Membre

L'ordre des indexes a-t-il une importance ? (cas multi index)

Bonjour,

Mon formateur Oracle nous a dit que l'ordre des indexes ont une importance. Par exemple, si j'ai un index avec deux colonnes, et que la colonne n°1 de l'index n'est pas utilisé, alors l'index n'agira pas.

Je suis donc allé dans la doc PostgreSQL pour voir le comportement de celui-ci (chapitres 11.3 et 11.5) :
http://docs.postgresqlfr.org/9.2/indexe … olumn.html
http://docs.postgresqlfr.org/9.2/indexe … scans.html

Mais je ne suis pas sur d'avoir tout à fait compris si c'était bien équivalent.

Donc considérons que j'ai une table avec trois colonnes : a, b, et c.
Si je crée l'index mon_index(a, c) alors l'index agira dans les cas suivants :

SELECT * FROM ma_table WHERE a = 1;
SELECT * FROM ma_table WHERE a = 1 AND b = 1 AND c = 1;
SELECT * FROM ma_table WHERE a = 1 OR b = 1 OR c = 1;

Mais pas dans ces cas là, parce que "a" n'est pas présent :

SELECT * FROM ma_table WHERE c = 1;
SELECT * FROM ma_table WHERE b = 1 AND c = 1;
SELECT * FROM ma_table WHERE b = 1 OR c = 1;

1. Est-ce que c'est bien le cas ? (pour les 2 exemples cités au-dessus).

2. Si je souhaite utiliser parmi les 3 cas :
* le cas où "a" seul est appelé
* "a" et "c" sont appelés
* le cas où seul "c" est appelé
... alors je devrais créer 2 indexes : mon_index1(a, c) et mon_index2(c) ?

3. Considérons que les colonnes "a" et "c" sont utilisés dans la requête. Si la colonne "a" contient 10 valeurs distinctes, et "c" 10000 valeurs distinctes, alors il faut de préférence placer "a" en premier, et "c" en seconde position dans l'index ?

4. La commande ANALYZE permet de remettre les index à jour si je comprend bien. Dans un cas général, est-t-il conseillé d'utiliser une tâche planifiée, pour exécuter ANALYZE de temps en temps (traitement de nuit) ?

5. Lorsqu'on crée une FOREIGN KEY (cas de jointure), PostgreSQL ne crée pas de jointure implicite (contrairement aux PRIMARY KEY et UNIQUE). La documentation dit :

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

... je suppose qu'il est préférable pour moi de créer explicitement un INDEX sur les colonnes sur lesquels j'effectue des jointures ?

Je vous remercie par avance, cordialement

gleu

Administrateur

Re : L'ordre des indexes a-t-il une importance ? (cas multi index)

Mon formateur Oracle nous a dit que l'ordre des indexes ont une importance. Par exemple, si j'ai un index avec deux colonnes, et que la colonne n°1 de l'index n'est pas utilisé, alors l'index n'agira pas.

Il a raison.

1. Oui.
2. Oui (ou alors un index sur a et un index sur c).
3. Je ne pense pas que cela a une grande importance.
4. ANALYZE ne remet pas les index à jour. Ils sont mis à jour à chaque opération DML (INSERT, UPDATE, DELETE and co). Par contre, il faut bien exécuter ANALYZE de manière périodique.
5. Je suppose que vous voulez dire d'index implicite (et non pas de jointure implicite). Dans ce cas, oui, PostgreSQL ne crée pas d'index lors de l'ajout d'une clé étrangère. Et donc oui, il est généralement bien de créer un index sur les colonnes impliquées par une clé étrangère.

---

Guillaume.

guk92

Membre

Re : L'ordre des indexes a-t-il une importance ? (cas multi index)

Bonsoir, merci pour vos réponses gleu.

Pour la 3. , sur stackoverflow, j'ai lu :

The column with the least distinct values ought to be first and the column with the most distinct values last

Pour la 4. , notre formateur Oracle nous a dit que les index ne servaient à rien, si de temps en temps on n'effectuait pas la commande ANALYZE. Sur le coup, j'ai pensé que c'était l'équivalent d'un VACUUM PostgreSQL mais pour "nettoyer" l'index.
6. Lorsque je lis la doc, cela dit en gros que ANALYZE sert à choisir le meilleur algo de recherche (le terme "plan d'exécution" correspond mieux peut-être) ... mais je suppose que 90% du temps c'est du B-tree et hash ?

Pour la 5., en effet j'ai voulu dire "index implicite".

Postgres.0

Membre

Re : L'ordre des indexes a-t-il une importance ? (cas multi index)

Bonjour,

dans postgres, l'index sera bien utilisé pour les cas:

 
SELECT * FROM ma_table WHERE c = 1;
SELECT * FROM ma_table WHERE b = 1 AND c = 1;

Ce pendant, il sera moins efficace que si il y avait une condition a=1.

Par contre je ne suis pas sûr pour la troisième, à tester.

 
SELECT * FROM ma_table WHERE b = 1 OR c = 1;

Pour le 3
tu as bien raison, il faut mettre la colonne qui a le plus petit nombre de valeurs en premier.
Pour le ANALYZE, comme a bien dit gleu, il faut exécuter un analyze périodique pour mettre  à jour test stats, si ma mémoire est bonne c'est le même mécanisme chez oracle.

Dernière modification par Postgres.0 (28/04/2014 15:07:22)

gleu

Administrateur

Re : L'ordre des indexes a-t-il une importance ? (cas multi index)

Pour la 4, je ne dis pas le contraire. Il faut exécuter ANALYZE de temps en temps. Cela permet au planificateur d'avoir une estimation récente des données présentes dans la table. Maintenant, si le dernier ANALYZE est suffisamment récent, que vous ajoutez un index *après*, et que vous exécutez votre requête immédiatement après, il pourra être utilisé par le planificateur sans avoir à refaire d'ANALYZE.

Pour le 6, ANALYZE calcule des statistiques sur les données présentes en base. Plus exactement sur un échantillon. Le planificateur utilise ces statistiques pour calculer le coût de chaque plan d'exécution, ce qui lui permettra de sélectionner le moins cher. Plus les statistiques sont proches de la réalité, meilleur sera le plan. Pour être proche de la réalité, elles doivent être calculées assez fréquemment. Tout du moins avec une fréquence en accord avec celle des INSERT/UPDATE/DELETE. ANALYZE ne sert pas à choisir entre btree et hash. De toute façon, je ne connais pas un seul cas où un index hash a été sélectionné au détriment d'un index btree.

J'ai oublié récemment un article dans GLMF sur le planificateur de requêtes de PostgreSQL. Voir http://boutique.ed-diamond.com/gnulinux … e-170.html pour les détails. Et trois documents de conférences : http://www.dalibo.org/comprendre_explain, http://www.dalibo.org/plans_d_execution_et_explain, http://www.dalibo.org/understanding_explain (la deuxième a été donnée par Mac Cousin, qui a une façon différente mais très intéressante d'aborder le même problème). J'espère que cela vous intéressera et je suis preneur de tout retour sur ces documents.

---

Guillaume.

alassanediakite

Membre

Re : L'ordre des indexes a-t-il une importance ? (cas multi index)

Salut

Donc considérons que j'ai une table avec trois colonnes : a, b, et c.
Si je crée l'index mon_index(a, c) alors l'index agira dans les cas suivants :

SELECT * FROM ma_table WHERE a = 1;
SELECT * FROM ma_table WHERE a = 1 AND b = 1 AND c = 1;
SELECT * FROM ma_table WHERE a = 1 OR b = 1 OR c = 1;

...

Je ne pense que l'index agira dans le cas

SELECT * FROM ma_table WHERE a = 1 OR b = 1 OR c = 1;

Le OR n'est pas sargable.
@+

Dernière modification par alassanediakite (29/04/2014 13:07:28)