Comment décidez-vous des index dont une table a besoin ?

Partez des requêtes, pas de la table. Regardez ce qui apparaît dans WHERE, JOIN et ORDER BY, puis construisez un index composite avec les colonnes d'égalité en premier et la colonne de plage ou de tri en dernier. Confirmez avec EXPLAIN ANALYZE qu'un index scan a remplacé un scan séquentiel. Chaque index coûte du débit en écriture et du stockage, donc supprimez ceux qui ne servent pas et évitez d'indexer seule une colonne à faible cardinalité.

Je travaille à rebours depuis le journal des requêtes lentes plutôt que de deviner au moment de la conception. Prenez une requête qui filtre sur tenant id et status et qui trie par created at. Le bon index là, c'est tenant id, status, created at dans cet ordre, parce que les prédicats d'égalité passent en premier et que la colonne de tri va en dernier pour que l'index puisse aussi satisfaire l'ordre. Mettez created at en premier et l'index est quasiment inutile pour cette requête. Ensuite je lance vraiment EXPLAIN ANALYZE et je vérifie que le plan a changé et que les estimations de lignes sont sensées, parce que le planificateur ignorera un index s'il pense qu'il va matcher la majeure partie de la table de toute façon. Côté coût, j'ai vu une table à forte écriture avec onze index où les inserts étaient le goulot, et en supprimer quatre inutilisés a à peu près doublé le débit d'ingestion. Dans Postgres, je regarde pg_stat_user_indexes pour repérer les index à zéro scan avant de proposer de supprimer quoi que ce soit, et je les crée en mode concurrent sur une table en production.