Forums PostgreSQL.fr

nathanpas

Membre

PostgreSQL - Les index

Bonjour !

J'ai une question concernant les index.

J'utilise une bdd Postgresql que j'exploite via Doctrine. J'ai une table User de 4571 entrées contenant notamment les champs 'id', 'name'.
Le champ 'id' étant ma clé primaire, j'ai un index unique dessus.
Je fais une requête qui va chercher tous les users qui commence par la lettre 'b', 'j', 'l', etc... pour essayer de reproduire une requête lente...
Ma requête fait 18ms.
Je rajoute un index 'name_idx' sur mon champ 'name' et ma requête fait toujours 18ms.
Par contre si j'effectue un Cluster sur mon index 'name_idx', (quand je parcours ma table, les occurences s'affichent par ordre de "name"...), ma requête passe de 18ms à 12ms,
mais j'imagine que maintenant, si j'effectue une recherche par id, celle-çi en payera les frais...

Ma question est donc la suivante :
Dans ma situation, comment puis-je améliorer la recherche par nom sans détériorer la recherche par id ?
Si cela n'est pas possible, quel est l'intérêt d'avoir plusieurs index sur une table ?

gleu

Administrateur

Re : PostgreSQL - Les index

Je rajoute un index 'name_idx' sur mon champ 'name' et ma requête fait toujours 18ms.

Rien ne dit qu'il utilise l'index. Il faudrait utiliser EXPLAIN pour le savoir.

Par contre si j'effectue un Cluster sur mon index 'name_idx', (quand je parcours ma table, les occurences s'affichent par ordre de "name"...), ma requête passe de 18ms à 12ms,

Certainement parce que, cette fois, il utilise l'index. Avoir une table clusterisé rend l'utilisation de l'index plus intéressant.

Dans ma situation, comment puis-je améliorer la recherche par nom sans détériorer la recherche par id ?

Qui dit que la recherche par id est détérioré par la présence de l'index sur name ?

Si cela n'est pas possible, quel est l'intérêt d'avoir plusieurs index sur une table ?

Si on reprend votre exemple, une recherche sur id pourra utiliser l'index sur id et pas l'index sur name, et une recherche sur name pourra utiliser l'index sur name et pas l'index sur id.

---

Guillaume.

nathanpas

Membre

Re : PostgreSQL - Les index

Merci pour votre réponse.
J'ai juste une dernière petite question, imaginons que mon index se porte sur mon champ "id". Ma table se présente ainsi :
'id' - 'name'
1 - 'xavier'
2 - 'jeremy '
3 - 'adele'
4 - 'nathan'
5 - 'antoine'
6 - 'theo'
7 - 'daniel'

Si j'ajoute un index sur "name", visuellement, la recherche par "name" ne peut être améliorée, puis qu’aucune occurrence ne peut avoir le même id. Je voulais savoir si Postgres, créait ainsi un 2ème index en arrière plan, ainsi clusterisé par "name"... Sinon comment peut-il améliorer la recherche par "name" ?

Encore merci pour la réponse, je me prends un peu la tête mais je souhaiterai utiliser les index judicieusement. Mes précédentes recherches ne m'ont pas permis de répondre à cette interrogation.

rjuju

Administrateur

Re : PostgreSQL - Les index

À moins d'avoir créé une contrainte unique sur name (auquel cas un index sera déjà créé), le champ name peut contenir des doublons.

Créer un index sur cette colonne pourra aider lors d'une recherche sur ce champ, que le champ contienne des doublons ou non.

Concernant CLUSTER, il s'agit d'une opération qui réécrit physiquement la table entièrement afin d'ordonner les lignes par rapport à l'ordre d'un index spécifique. Vous ne pouvez donc pas faire de CLUSTER sur 2 index différents.

---

Julien.
https://rjuju.github.io/