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

> Analyze PostgreSQL index usage with MCP-PostgreSQL-Ops get_index_usage_stats. Identify unused and optimize high-usage indexes for better database performance via data-driven insights.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```sql
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:

```python
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:

```sql
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:

```sql
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:

```sql
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/prompt_template.md) – Documents the tool’s interface for MCP clients.
- [`src/mcp_postgresql_ops/__init__.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.