pg_index_advisor — Automatically Detect Missing Indexes — Blog

What Does This Tool Do?

pg_index_advisor analyzes the access patterns of all tables and identifies two core problems: missing indexes (tables with high sequential scan rates) and unused indexes (indexes that cost storage and write performance but are never read). The tool generates ready-to-execute CREATE INDEX statements, providing concrete action items.

How Is It Called?

Connecting First

Before calling any performance tool, you need to connect to the target database. mcp-postgres supports multiple database profiles — ideal for multi-cluster setups:

● ● ● Claude CLI — Connection

claude> Connect using profile "production-db"

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

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

Claude CLI (Natural Language)

● ● ● Claude CLI — Index Analysis

claude> Which indexes are missing in my database?

▶ Calling tools...
→ pg_index_advisor {}

Here are the recommendations:

⚠ Missing Indexes (high 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);

🗑 Unused Indexes:
1. app.users_legacy_idx — 0 scans, 24 MB

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Index Analysis

// 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."
}

What Happens Under the Hood?

The tool executes two separate queries — one for missing indexes and one for unused ones:

Missing Indexes

-- Tables with high sequential scan rate from 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;

Unused Indexes

-- Indexes with zero scans (excluding 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;

Note: The tool intentionally excludes primary key and unique indexes from the unused list — these have structural significance regardless of scan frequency.

Output in Detail

tables_analyzed — Number of tables analyzed
schema / table — Schema and table name
seq_scans / idx_scans — Number of sequential vs. index scans since the last statistics reset
ratio — Ratio of seq scans to index scans — the higher, the more urgent
row_count — Number of rows in the table
table_size — Table size (formatted, e.g., "33 MB")
suggested_indexes — Array of ready-to-execute CREATE INDEX statements
note — Reminder to review suggested indexes before creating

Pro Tip: A high seq_scan count alone is not a problem — for small tables (<1,000 rows), a sequential scan is often faster than an index lookup. That's why the tool filters by n_live_tup > 1000.

What Does the Test Suite Validate?

The test suite validates pg_index_advisor against a PostgreSQL 16 instance with 300,000 transactions (33 MB) in perf.transactions — intentionally without indexes on frequently filtered columns:

● ● ● 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

Test Data Scenario

The seed database contains:

perf.transactions — 300,000 rows (33 MB) without indexes on is_flagged, merchant_category, status, channel, currency

Tests validate that:

The result is an array of recommendation objects
Each recommendation includes a suggested_indexes array
The schema filter (schema: "perf") returns only recommendations from the specified schema

Summary

pg_stat_user_tables

Data Source

Output Fields

Tests

read-only

Type

pg_index_advisor is the counterpart to pg_slow_query_digest: while the digest shows which queries are slow, the advisor shows why they're slow and delivers the fix along with it.

Together with pg_slow_query_digest and pg_cache_hit_ratio, it forms the core of the /pg-perf workflow skill.

pg_index_advisor — Automatically Detect Missing Indexes