PostgreSQL Index Advisor

• Extension PostgreSQL de Supabse qui recommande des index pour améliorer les performances des requêtes
• Si l’on passe une requête à la fonction index_advisor(), elle renvoie le coût avant/après pour le démarrage et le total, ainsi que le SQL DDL pour créer l’index
  • Exécution : select * from index_advisor('select book.id from book where title = $1');
  • Retour : {"CREATE INDEX ON public.book USING btree (title)"}
• Pour les requêtes complexes, elle peut aussi renvoyer plusieurs instructions de création d’index
• Prise en charge des paramètres génériques ($1, $2, ..)
• Prise en charge des vues matérialisées
• Capable d’identifier les tables/colonnes masquées par des vues

[savvykang]

Dans la version actuelle, seuls les index btree sur une seule colonne sont recommandés. Il ne peut pas être utilisé si les conditions de requête deviennent complexes ou si vous faites de la recherche full text https://supabase.com/docs/guides/…

[savvykang]

On dit que lorsque les conditions de recherche sont complexes, plusieurs index sur une seule colonne sont utilisés à la place d’un index multicolonnes, mais il semble qu’ils ne se comportent pas exactement de la même manière. On dit aussi qu’il existe des situations où la meilleure option est d’utiliser à la fois un index multicolonnes et plusieurs index sur une seule colonne

https://www.postgresql.org/docs/current/indexes-bitmap-scans.html

[xguru]

Avis Hacker News

• Ce serait bien d’avoir une fonctionnalité qui recommande des types de données plus efficaces à partir des données réellement stockées dans les tables
• Ce serait bien d’avoir une base de données qui détecte automatiquement les requêtes lentes et crée les index nécessaires
  • On lance des tests de charge depuis l’application, la base de données est appelée, les requêtes sont collectées, puis la base de données s’ajuste automatiquement
• Je ne savais pas que HypoPG était disponible sur RDS depuis plus d’un an
• Avec 3 jointures ou plus, je veux qu’un index soit utilisé sur une relation, mais si on ne met pas de limit sur la CTE, Postgres essaie d’exécuter chaque jointure en parallèle et tente de joindre un très grand nombre de lignes
  • En ce moment, gérer le query planner me donne envie de rompre avec pg
• CockroachDB intègre une fonctionnalité similaire
  • Elle récupère les requêtes lentes existantes, analyse des index virtuels et fait des recommandations pour obtenir un meilleur plan de requête
  • On peut les ajouter en un clic depuis l’interface de la console
• Dans des moteurs de requêtes distribués comme Presto ou Spark, on fait quelque chose de similaire avec des partitions et des buckets au lieu d’index
  • Cela peut réduire le calcul, le temps et le coût
• C’est pratique que ce soit écrit en Pl/PgSQL vanilla
  • Il y a une tentation de copier la fonction index_advisor(text) dans la session et de commencer à bricoler avec du code en dur et des heuristiques
  • La plupart des extensions vraiment utiles nécessitent d’être compilées, installées, créées, puis supprimées
• C’est similaire à TiAdvisor de TiDB et utilise une méthode virtuelle
• J’utilise pghero, qui fournit cette fonctionnalité via une interface graphique
• Il ne semble pas fournir de réflexion ni d’analyse sur les compromis associés
  • L’extension de base, HypoPG, ne semble pas collecter de statistiques sur les données qui influencent le query planner
• Je me demande s’il reconnaît les tables parentes et enfants héritées