How to Analyze Index Usage Statistics in PostgreSQL with MCP‑PostgreSQL‑Ops

MCP‑PostgreSQL‑Ops provides the get_index_usage_stats tool that queries the pg_stat_user_indexes system view to identify unused, low‑usage, and high‑usage indexes, enabling data‑driven decisions to drop dead indexes or optimize hot ones.

The call518/mcp-postgresql-ops repository exposes a Model Context Protocol (MCP) toolset for database operations, including deep visibility into index utilization. By analyzing these statistics, you can reduce write overhead, reclaim storage, and ensure that your most frequent queries are properly supported.

Understanding the get_index_usage_stats Tool

The tool is implemented in src/mcp_postgresql_ops/mcp_main.py (lines 1870‑1925). It wraps a SQL query against pg_stat_user_indexes, which PostgreSQL maintains automatically to track every index scan since the last statistics reset.

How It Works

When invoked, the tool executes the following SQL via the internal execute_query helper:

SELECT 
    schemaname      AS schema_name,
    relname         AS table_name,
    indexrelname    AS index_name,
    idx_scan        AS scans,
    idx_tup_read    AS tuples_read,
    idx_tup_fetch   AS tuples_fetched,
    CASE 
        WHEN idx_scan = 0      THEN 'Never used'
        WHEN idx_scan < 100    THEN 'Low usage'
        WHEN idx_scan < 1000   THEN 'Medium usage'
        ELSE                       'High usage'
    END AS usage_level
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC, schemaname, relname, indexrelname;

The results are formatted into a markdown table by format_table_data, making the output readable in chat interfaces or logs.

Interpreting Usage Levels

The usage_level column categorizes indexes into four tiers:

• Never used (idx_scan = 0): The index has not been scanned since statistics were last reset. These are prime candidates for removal after verifying with EXPLAIN.
• Low usage (idx_scan < 100): Rarely accessed indexes. Investigate whether they support infrequent reports or are simply redundant.
• Medium usage (idx_scan < 1000): Moderately active indexes. Retain them, but monitor growth and bloat.
• High usage (idx_scan ≥ 1000): Heavily utilized indexes. Ensure they are well‑maintained and consider reindexing if they become bloated.

Step‑by‑Step Workflow to Analyze Index Usage Statistics

Follow this workflow to turn raw statistics into performance gains.

1. Run the Tool

Invoke get_index_usage_stats via the MCP tool interface or programmatically:

from mcp_postgresql_ops import mcp

async def check_indexes():
    stats = await mcp.get_index_usage_stats(database_name="production")
    print(stats)

If no database_name is provided, the tool defaults to the database specified in the environment configuration.

2. Review the Output

Scan the markdown table for indexes marked Never used or Low usage. Note the schema_name and index_name for the next step.

3. Validate with the Query Planner

Before dropping any index, confirm it is truly unnecessary:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table WHERE indexed_column = 'value';

If the planner consistently chooses a sequential scan or an alternative index, the candidate index is safe to remove.

4. Remove Unused Indexes

Drop confirmed dead indexes using CONCURRENTLY to avoid locking:

DROP INDEX CONCURRENTLY schema_name.unused_index_name;

This reduces write‑time overhead and frees storage immediately.

5. Reindex High‑Usage Indexes

For indexes marked High usage that have grown large or fragmented, rebuild them online:

REINDEX INDEX CONCURRENTLY schema_name.heavily_used_index;

This improves scan speed without blocking concurrent queries.

6. Monitor Over Time

Repeat the get_index_usage_stats call weekly or after major schema changes. Store historical results to detect trends, such as an index transitioning from High usage to Low usage after application refactoring.

Implementation Details and Source Code

The core logic resides in src/mcp_postgresql_ops/mcp_main.py between lines 1870 and 1925. The function get_index_usage_stats accepts an optional database_name parameter and delegates query execution to execute_query, then formats the result via format_table_data.

Key supporting files include:

• src/mcp_postgresql_ops/prompt_template.md – Documents the tool’s interface for MCP clients.
• src/mcp_postgresql_ops/__init__.py – Registers the tool with the MCP framework, enabling CLI access via mcp-tool.

The SQL query leverages pg_stat_user_indexes, a system view maintained by the PostgreSQL statistics collector. This view is updated continuously and persists until pg_stat_reset() is called or the server restarts.

Summary

• MCP‑PostgreSQL‑Ops exposes get_index_usage_stats to surface index utilization from pg_stat_user_indexes.
• The tool categorizes indexes into Never used, Low usage, Medium usage, and High usage tiers based on idx_scan counts.
• Never used and Low usage indexes are candidates for removal after validation with EXPLAIN.
• High usage indexes may benefit from REINDEX CONCURRENTLY to reduce bloat.
• Always use CONCURRENTLY when dropping or reindexing to prevent table locks in production environments.

Frequently Asked Questions

How often should I run get_index_usage_stats to analyze index usage statistics?

Run the tool weekly in production environments, or immediately after deploying significant schema changes, query optimizations, or application updates. This cadence captures usage pattern shifts without overwhelming monitoring logs.

Can I analyze index usage statistics for a specific database only?

Yes. Pass the database_name parameter to get_index_usage_stats. If omitted, the tool defaults to the database configured in your environment variables, as handled by the execute_query helper in src/mcp_postgresql_ops/mcp_main.py.

What is the difference between idx_tup_read and idx_tup_fetch in the output?

idx_tup_read counts index entries examined during scans, while idx_tup_fetch counts live table rows actually retrieved. A large gap between these values indicates the index has poor selectivity—many index entries are scanned but few rows satisfy the query conditions.

Is it safe to drop an index marked as "Never used" immediately?

No. Always validate with EXPLAIN (ANALYZE, BUFFERS) on representative queries first. An index might appear unused because statistics were recently reset, the application has seasonal traffic patterns, or it supports a rare but critical administrative query. Once confirmed unnecessary, drop it with DROP INDEX CONCURRENTLY to avoid locking.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}