Index Tuning Advisor PostgreSQL : détecter, prioriser et suivre les optimisations d'index par table et par requête

Pourquoi ce module existe

Même avec de bonnes pratiques SQL au départ, une base évolue : nouveaux usages, montée en volumétrie, régressions après livraison, changement des plans d'exécution. Sans outillage dédié, ces dérives ne sont détectées qu'une fois l'incident déjà visible en production.

L'Index Tuning Advisor de PWR transforme l'historique de statistiques déjà collecté par la plateforme (snapshots perfhist) en actions concrètes : il isole les tables et les requêtes où un problème d'index est probable, avant que la lenteur ne devienne critique.

• détecter les tables et requêtes pénalisées par des accès sous-optimaux (scans séquentiels, tris coûteux, débordements sur disque),
• prioriser objectivement grâce à un score plutôt qu'à une impression subjective de lenteur,
• éviter la sur-indexation "à l'aveugle" en distinguant les vrais signaux des faux positifs,
• suivre dans le temps si une table redevient saine après une action (VACUUM, index, réécriture de requête).

Ce que l'Index Tuning Advisor analyse

Le moteur s'appuie sur deux sources déjà historisées par le collector : pg_stat_user_tables (comportement d'accès et de maintenance de chaque table) pour le score par table, et pg_stat_statements (coût réel des requêtes) pour le panneau de détail par requête.

Pour chaque table du dernier snapshot analysé (ou d'un snapshot antérieur sélectionné), PWR calcule cinq facteurs de risque indépendants, chacun plafonné, puis les additionne pour obtenir un score global sur 100 :

• seq_scan / idx_scan — nombre de scans séquentiels vs scans par index depuis le dernier reset des statistiques,
• n_live_tup / n_dead_tup — volumétrie effective et tuples morts en attente de nettoyage,
• n_tup_ins / n_tup_upd / n_tup_del — intensité des écritures (impacte le coût de maintenance des index),
• last_vacuum / last_autovacuum — fraîcheur du dernier entretien de la table,
• Objectif : cibler les tables qui cumulent plusieurs signaux (volume + scans + activité), pas seulement la table la plus grosse ou la requête la plus lente unitairement.

f1_scan_ratio    = min(40, (seq_scan / (idx_scan + 1)) x 10)   -- pression des scans sequentiels
f2_volume        = min(20, n_live_tup / 100 000)                -- volumetrie de la table
f3_activity       = min(20, (n_tup_ins + n_tup_upd + n_tup_del) / 50 000)  -- activite DML
f4_dead_tuples    = min(10, n_dead_tup / 50 000)                -- tuples morts non nettoyes
f5_maintenance    = (5 si last_vacuum absent) + (5 si last_autovacuum absent)

score = min(100, f1 + f2 + f3 + f4 + f5)
dominant_factor = facteur ayant la plus forte contribution au score

Score de risque et niveaux de priorité

Le score global (0 à 100) est ensuite converti en un niveau de priorité lisible pour l'équipe, affiché sous forme de badge coloré dans l'interface.

• Le tableau de bord affiche également, par schéma, le score moyen et le score maximal des tables qu'il contient, pour repérer rapidement les schémas les plus à risque.
• Chaque table conserve son ratio scan_ratio (seq_scan / idx_scan) affiché tel quel, en complément du score global, pour visualiser directement la pression de scans séquentiels.

score <= 30            -> low       (risque faible)
30 < score <= 60        -> medium    (risque moyen)
60 < score <= 80        -> high      (risque eleve)
score > 80              -> critical  (risque critique)

Recommandations automatiques par table

Selon les facteurs déclenchés (au-delà d'un seuil propre à chacun), PWR attache à chaque table une ou plusieurs étiquettes de recommandation, avec une explication en clair :

• missing_index — « Manque d'index probable : examiner les filtres et jointures fréquents » (f1_scan_ratio ≥ 20),
• large_table — « Table volumineuse : prioriser les requêtes qui parcourent beaucoup de lignes » (f2_volume ≥ 10),
• composite_index — « Activité élevée : un index composite peut réduire les lectures et écritures inutiles » (f3_activity ≥ 10),
• vacuum_required — « Tuples morts élevés : planifier un VACUUM et vérifier l'autovacuum » (f4_dead_tuples ≥ 5),
• maintenance_missing — « Entretien absent : contrôler VACUUM et autovacuum pour cette table » (f5_maintenance ≥ 5),
• no_index_usage — « Aucun index utilisé sur ce snapshot malgré des scans séquentiels » (idx_scan = 0 et seq_scan > 0).

Analyse complémentaire par requête (pg_stat_statements)

En complément du score par table, chaque table du tableau peut être ouverte pour afficher les requêtes pg_stat_statements qui la concernent, avec leurs propres signaux d'alerte, indépendants du score de la table :

• slow_query — temps moyen d'exécution ≥ 100 ms : « optimiser la requête et vérifier son plan d'exécution »,
• temp_spill — écritures temporaires détectées (temp_blks_written > 0) : « évaluer work_mem pour cette charge »,
• physical_reads — lectures physiques élevées (shared_blks_read ≥ 1000) : « vérifier les index et la sélectivité »,
• wal_volume — volume WAL élevé (≥ 100 Mo cumulés) : « réduire la taille des batchs ou fractionner les transactions ».

Exemple de lecture d'une recommandation

Table public.orders, activité e-commerce classique : recherches fréquentes par customer_id triées par created_at.

• Constat : forte pression de scans séquentiels sur une table volumineuse, autovacuum jamais exécuté.
• Proposition : créer un index composite (customer_id, created_at DESC) pour couvrir le filtre et le tri en un seul accès, puis vérifier la planification de l'autovacuum sur cette table.
• Vigilance : surveiller le coût d'écriture supplémentaire sur orders (n_tup_ins/upd/del déjà notable) avant de multiplier les index.

seq_scan = 42 000   idx_scan = 1 200   -> f1_scan_ratio = min(40, (42000/1201)x10) = 40 (plafond)
n_live_tup = 2 400 000               -> f2_volume     = min(20, 2400000/100000) = 20 (plafond)
n_tup_ins+upd+del = 180 000          -> f3_activity   = min(20, 180000/50000)   = 3.6
n_dead_tup = 65 000                  -> f4_dead_tuples = min(10, 65000/50000)   = 1.3
last_autovacuum = NULL               -> f5_maintenance = 5

score = 40 + 20 + 3.6 + 1.3 + 5 = 69.9  -> niveau HIGH
dominant_factor = f1_scan_ratio
recommandations = [missing_index, large_table, maintenance_missing]

Workflow conseillé en production

L'Index Tuning Advisor est une aide à la priorisation, pas un pilote automatique : le workflow reste piloté par l'équipe.

• Analyser — ouvrir /index-advisor, trier par score décroissant et filtrer par schéma ou niveau de risque pour repérer le top impact.
• Valider côté DBA — confronter la recommandation au modèle de données réel (cardinalité des colonnes, contraintes déjà en place, index existants).
• Tester en préproduction — vérifier avec EXPLAIN ANALYZE que le nouveau plan d'exécution utilise bien l'index proposé, sous une charge représentative.
• Déployer de façon contrôlée — privilégier CREATE INDEX CONCURRENTLY pour éviter de verrouiller la table en production, dans une fenêtre adaptée.
• Mesurer — reprendre un nouveau snapshot après quelques jours et comparer le score, le scan_ratio et les recommandations de la même table.
• Itérer — conserver, ajuster ou supprimer les index qui n'améliorent pas le score ou qui ne sont plus utilisés (idx_scan proche de zéro).

Bonnes pratiques

Le score et les étiquettes de recommandation sont un point de départ objectif ; la décision finale reste contextuelle.

• Toujours corréler la recommandation avec le contexte applicatif (une table volumineuse peu interrogée n'est pas prioritaire, même avec un score élevé de f2_volume).
• Éviter la sur-indexation : chaque index supplémentaire ralentit les INSERT/UPDATE/DELETE et occupe de l'espace disque.
• Préférer des index alignés avec les patterns de filtres et de tri réellement exécutés, visibles dans le panneau requêtes de la table.
• Revoir périodiquement les tables signalées no_index_usage : un index inutilisé mérite d'être supprimé, pas seulement ignoré.
• Traiter en priorité les recommandations vacuum_required / maintenance_missing avant d'ajouter des index : des tuples morts en excès faussent aussi bien le score que les plans d'exécution.

Limites à connaître

L'Index Tuning Advisor est une aide à la décision, pas un remplacement de l'expertise DBA :

• certaines lenteurs relèvent d'une réécriture de la requête (SQL rewrite), pas d'un index supplémentaire,
• certaines lenteurs viennent de la configuration PostgreSQL (voir le Tuning Advisor) ou de l'infrastructure sous-jacente,
• un index pertinent aujourd'hui peut devenir inutile après une évolution fonctionnelle ou un changement de patterns de requêtes,
• le score par table dépend de la fenêtre de statistiques disponible : un stats reset récent (pg_stat_reset) fausse temporairement seq_scan/idx_scan.

Positionnement dans la solution SaaS

Dans la plateforme, l'Index Tuning Advisor s'inscrit entre l'observation (collecte et historisation des statistiques via les snapshots perfhist), la décision (score et priorisation par table et par requête) et l'amélioration continue (comparaison du score entre deux snapshots après action).

Il complète le Tuning Advisor : ce dernier ajuste la configuration PostgreSQL (postgresql.conf), l'Index Tuning Advisor ajuste la structure des tables et des index — les deux s'appuient sur le même historique de charge réelle.

Mesurer (snapshots perfhist) -> Recommander (score + tags par table/requete) -> Deployer (CREATE INDEX CONCURRENTLY, VACUUM) -> Verifier (nouveau snapshot) -> Ameliorer (iterer)

Résultat attendu pour les équipes

L'objectif n'est pas de remplacer la revue technique, mais de la rendre plus rapide et mieux ciblée.

• moins de temps perdu en diagnostic manuel sur des vues système brutes,
• des optimisations priorisées selon un score objectif plutôt qu'une impression de lenteur,
• une meilleure collaboration DBA / Dev / Support autour d'une même liste de tables à risque,
• des performances plus stables et prévisibles en production, avec un suivi mesurable avant/après.