Wie entscheidest du, welche Indizes eine Tabelle braucht?

Starte bei den Queries, nicht bei der Tabelle. Schau, was in WHERE, JOIN und ORDER BY auftaucht, und bau dann einen zusammengesetzten Index mit den Gleichheitsspalten zuerst und der Bereichs- oder Sortierspalte zuletzt. Bestätige mit EXPLAIN ANALYZE, dass ein Index Scan den Sequential Scan ersetzt hat. Jeder Index kostet Schreibdurchsatz und Speicher, wirf also ungenutzte weg und indiziere keine Spalte mit niedriger Kardinalität für sich allein.

Ich arbeite rückwärts vom Slow-Query-Log statt beim Entwurf zu raten. Nimm eine Query, die auf Tenant-Id und Status filtert und nach created_at sortiert. Der richtige Index ist dort auf tenant_id, status, created_at in genau dieser Reihenfolge, weil Gleichheitsprädikate zuerst kommen und die Sortierspalte zuletzt, damit der Index auch die Sortierung bedienen kann. Setz created_at nach vorn, und der Index ist für diese Query fast nutzlos. Dann führe ich tatsächlich EXPLAIN ANALYZE aus und prüfe, ob sich der Plan geändert hat und die Zeilenschätzungen plausibel sind, denn der Planner ignoriert einen Index, wenn er glaubt, ohnehin fast die ganze Tabelle zu treffen. Auf der Kostenseite habe ich eine schreiblastige Tabelle mit elf Indizes gesehen, bei der Inserts der Flaschenhals waren, und vier ungenutzte zu entfernen hat den Ingest-Durchsatz etwa verdoppelt. In Postgres schaue ich in pg_stat_user_indexes nach Indizes mit null Scans, bevor ich vorschlage, etwas zu löschen, und ich lege sie auf einer Live-Tabelle concurrently an.