pg_index_advisor — Fehlende Indizes automatisch erkennen — Blog

Was macht dieses Tool?

pg_index_advisor analysiert das Zugriffsverhalten aller Tabellen und identifiziert zwei zentrale Probleme: fehlende Indizes (Tabellen mit hoher Sequential-Scan-Rate) und ungenutzte Indizes (Indizes die Speicherplatz und Write-Performance kosten, aber nie gelesen werden). Das Tool generiert sofort einsetzbare CREATE INDEX-Statements und liefert damit eine konkrete Handlungsempfehlung.

Wie wird es aufgerufen?

Verbindung herstellen

Bevor ein Performance-Tool aufgerufen werden kann, muss eine Verbindung zur Zieldatenbank hergestellt werden. mcp-postgres unterstützt mehrere Datenbankprofile — ideal für Multi-Cluster-Setups:

● ● ● Claude CLI — Verbindung

claude> Verbinde mit Profil "production-db"

▶ Calling tools...
→ pg_connect { "profile": "production-db" }

{
"status": "connected",
"database": "mcp_test",
"version": "PostgreSQL 16.x",
"host": "db.example.com"
}

Claude CLI (natürliche Sprache)

● ● ● Claude CLI — Index-Analyse

claude> Welche Indizes fehlen in meiner Datenbank?

▶ Calling tools...
→ pg_index_advisor {}

Hier sind die Empfehlungen:

⚠ Fehlende Indizes (hohe Seq-Scan-Rate):
1. perf.transactions
   48.291 Seq Scans vs. 12 Index Scans
   → CREATE INDEX idx_transactions_status ON perf.transactions(status);

2. app.orders
   1.204 Seq Scans vs. 0 Index Scans
   → CREATE INDEX idx_orders_status ON app.orders(status);

🗑 Ungenutzte Indizes:
1. app.users_legacy_idx — 0 Scans, 24 MB

MCP Protocol (programmatisch)

● ● ● MCP Protocol — Index-Analyse

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_index_advisor", {});

// Response:
{
"tables_analyzed": 1,
"recommendations": [
    {
      "schema": "perf",
      "table": "transactions",
      "seq_scans": "183",
      "idx_scans": "0",
      "ratio": "183",
      "row_count": "300000",
      "table_size": "33 MB",
      "suggested_indexes": [
        "CREATE INDEX idx_transactions_is_flagged ON perf.transactions (is_flagged);",
        "CREATE INDEX idx_transactions_merchant_category ON perf.transactions (merchant_category);",
        "CREATE INDEX idx_transactions_status ON perf.transactions (status);",
        "CREATE INDEX idx_transactions_channel ON perf.transactions (channel);",
        "CREATE INDEX idx_transactions_currency ON perf.transactions (currency);"
      ]
    }
],
"note": "Review suggested indexes before creating. Consider query patterns and write frequency."
}

Was passiert unter der Haube?

Das Tool führt zwei separate Queries aus — eine für fehlende Indizes und eine für ungenutzte:

Fehlende Indizes

-- Tabellen mit hoher Sequential-Scan-Rate aus pg_stat_user_tables
SELECT schemaname, relname AS table_name, seq_scan, idx_scan, seq_tup_read, n_live_tup AS row_estimate FROM pg_stat_user_tables WHERE seq_scan > 100 AND n_live_tup > 1000 AND (idx_scan = 0 OR seq_scan::float / GREATEST(idx_scan, 1) > 10) ORDER BY seq_tup_read DESC;

Ungenutzte Indizes

-- Indizes mit null Scans (ohne PK/Unique-Constraints)
SELECT schemaname, indexrelname AS index_name, relname AS table_name, idx_scan, pg_relation_size(indexrelid) AS size_bytes FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%' AND indexrelname NOT LIKE '%unique%' ORDER BY pg_relation_size(indexrelid) DESC;

Hinweis: Das Tool schließt Primary-Key- und Unique-Indizes bewusst von der Unused-Liste aus — diese haben strukturelle Bedeutung unabhängig von der Scan-Häufigkeit.

Ausgabe im Detail

tables_analyzed — Anzahl der analysierten Tabellen
schema / table — Schema und Tabellenname
seq_scans / idx_scans — Anzahl Sequential vs. Index Scans seit dem letzten Statistik-Reset
ratio — Verhältnis Seq Scans zu Index Scans — je höher, desto dringender
row_count — Anzahl der Zeilen in der Tabelle
table_size — Tabellengröße (formatiert, z.B. "33 MB")
suggested_indexes — Array mit sofort ausführbaren CREATE INDEX Statements
note — Hinweis, die vorgeschlagenen Indizes vor Erstellung zu prüfen

Praxis-Tipp: Ein hoher seq_scan-Wert allein ist kein Problem — bei kleinen Tabellen (<1000 Zeilen) ist ein Seq Scan oft schneller als ein Index-Lookup. Das Tool filtert deshalb nach n_live_tup > 1000.

Was testet die Testsuite?

Die Testsuite validiert pg_index_advisor gegen eine PostgreSQL 16-Instanz mit 300.000 Transaktionen (33 MB) in perf.transactions — absichtlich ohne Indizes auf häufig gefilterten Spalten:

● ● ● vitest run — Testsuite

✓ pg_index_advisor > returns recommendations array
✓ pg_index_advisor > includes create_statement for each recommendation
✓ pg_index_advisor > filters by schema when specified

Test Files 1 passed (1)
Tests 3 passed (3)
Duration 287ms

Testdaten-Szenario

Die Seed-Datenbank enthält:

perf.transactions — 300.000 Zeilen (33 MB) ohne Indizes auf is_flagged, merchant_category, status, channel, currency

Die Tests validieren, dass:

Das Ergebnis ein Array mit Empfehlungs-Objekten ist
Jede Empfehlung ein suggested_indexes-Array enthält
Der Schema-Filter nur Empfehlungen aus dem angegebenen Schema zurückgibt

Zusammenfassung

pg_stat_user_tables

Datenquelle

Ausgabefelder

Tests

read-only

Typ

pg_index_advisor ist das Pendant zu pg_slow_query_digest: Während der Digest zeigt welche Queries langsam sind, zeigt der Advisor warum sie langsam sind und liefert die Lösung gleich mit.

Zusammen mit pg_slow_query_digest und pg_cache_hit_ratio bildet es den Kern des /pg-perf Workflow-Skills.

pg_index_advisor — Fehlende Indizes automatisch erkennen