PostgreSQL Index Advisor

• Eine PostgreSQL-Erweiterung von Supabase, die Indizes zur Verbesserung der Query-Performance empfiehlt
• Übergibt man eine Query an die Funktion index_advisor(), gibt sie die Kosten vor/nach der Optimierung für den Start-up-/Gesamtaufwand sowie SQL-DDL zum Erstellen von Indizes zurück
  • Ausführung: select * from index_advisor('select book.id from book where title = $1');
  • Rückgabe: {"CREATE INDEX ON public.book USING btree (title)"}
• Bei komplexen Queries werden mitunter mehrere Anweisungen zum Erstellen von Indizes zurückgegeben
• Unterstützung für generische Parameter ($1, $2, ..)
• Unterstützung für materialisierte Views
• Kann durch Views verdeckte Tabellen/Spalten identifizieren

[savvykang]

In der aktuellen Version werden nur btree-Indizes für einzelne Spalten empfohlen. Wenn die Abfragebedingungen komplexer werden oder Sie eine Full-Text-Suche verwenden, kann es nicht genutzt werden: https://supabase.com/docs/guides/…

[savvykang]

Wenn die Abfragebedingungen komplex sind, heißt es zwar, dass statt eines Mehrspaltenindex mehrere Einzelspaltenindizes verwendet werden, aber es scheint nicht exakt dasselbe Verhalten zu sein. Oder es soll Situationen geben, in denen es am besten ist, einen Mehrspaltenindex und mehrere Einzelspaltenindizes gleichzeitig zu verwenden.

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

[xguru]

Hacker-News-Kommentare

• Es wäre gut, wenn es eine Funktion gäbe, die auf Basis der tatsächlich in der Tabelle gespeicherten Daten effizientere Datentypen empfiehlt
• Es wäre gut, wenn es eine Datenbank gäbe, die langsame Queries automatisch erkennt und die nötigen Indizes erstellt
  • Wenn man in der Anwendung Lasttests ausführt, die Datenbank aufruft und Queries sammelt, könnte sich die Datenbank danach automatisch anpassen
• Ich wusste nicht, dass HypoPG in RDS schon seit über einem Jahr verfügbar ist
• Bei mehr als drei Joins möchte ich, dass für eine Relation ein Index verwendet wird, aber wenn man kein LIMIT auf die CTE setzt, versucht Postgres, jeden Join parallel auszuführen und eine riesige Zahl von Zeilen zu joinen
  • In letzter Zeit fühlt es sich so an, als würde mich der Umgang mit dem Query Planner dazu bringen, mich von pg zu trennen
• CockroachDB hat eine ähnliche Funktion eingebaut
  • Es nimmt langsame bestehende Queries, analysiert virtuelle Indizes für bessere Query-Pläne und macht Vorschläge
  • Über die Konsolen-UI lässt sich das mit einem Klick hinzufügen
• In verteilten Query-Engines wie Presto oder Spark erledigt man Ähnliches statt mit Indizes mit Partitionen und Buckets
  • Das kann Rechenaufwand, Zeit und Kosten verringern
• Praktisch, dass es in Vanilla Pl/PgSQL geschrieben ist
  • Man kommt in Versuchung, die Funktion index_advisor(text) in die Session zu kopieren und mit Hardcoding und Heuristiken loszulegen
  • Die meisten sinnvollen Erweiterungen müssen kompiliert, installiert, erstellt und wieder gelöscht werden
• Ähnlich wie TiAdvisor von TiDB verwendet es einen virtuellen Ansatz
• Ich nutze pghero, das diese Funktion per GUI anbietet
• Es scheint keine Überlegungen oder Erkenntnisse zu den damit verbundenen Trade-offs zu liefern
  • Die zugrunde liegende Erweiterung HypoPG sammelt anscheinend keine Statistiken über Daten, die den Query Planner beeinflussen
• Ich frage mich, ob es vererbte Parent- und Child-Tabellen erkennt