# How to Optimize Slow Queries Using pg_stat_statements with the MCP PostgreSQL Server

> Optimize slow PostgreSQL queries with pg_stat_statements on the MCP server. Learn how to use get_pg_stat_statements_top_queries to identify and resolve performance bottlenecks efficiently.

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

---

**The MCP PostgreSQL Operations server exposes a built-in `get_pg_stat_statements_top_queries` tool that automatically detects the PostgreSQL version and extension availability, returning a formatted markdown table of the most time-consuming queries without requiring manual SQL.**

The `call518/mcp-postgresql-ops` repository provides a Model Context Protocol (MCP) server that simplifies database performance tuning by wrapping `pg_stat_statements` in high-level tools. By leveraging this integration, developers can identify bottlenecks through automated version-aware queries and focus on optimization rather than writing boilerplate SQL.

## Architecture of the pg_stat_statements Integration

The MCP server abstracts the complexity of querying `pg_stat_statements` through a four-stage pipeline that handles extension detection, version compatibility, data retrieval, and formatting.

### Extension Detection and Validation

Before executing any query, the server verifies that `pg_stat_statements` is installed using `check_extension_exists()`. This function, located in [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py) (lines 81-87), prevents errors on databases where the extension hasn't been enabled.

### Version-Aware Query Generation

PostgreSQL changed its timing column names between versions 12 and 13. The `get_pg_stat_statements_query()` function in [`src/mcp_postgresql_ops/version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py) (lines 503-545) dynamically constructs the correct SELECT statement: using `total_time` and `mean_time` for PostgreSQL 12, and `total_exec_time` and `mean_exec_time` for PostgreSQL 13+. This ensures accurate statistics regardless of your database version.

### Data Retrieval and Formatting

The `get_pg_stat_statements_data()` function in [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py) (lines 91-100) executes the generated query and returns a list of dictionaries containing query statistics. These results are then processed by `format_table_data()` (lines 1-68 in the same file) to convert raw byte counts and durations into human-readable formats.

### MCP Tool Exposure

The `@mcp.tool()` decorator 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 48-90) exposes `get_pg_stat_statements_top_queries`, which orchestrates the entire workflow and delivers a markdown table ready for chat interfaces or direct API consumption.

## How to Query pg_stat_statements via the MCP Server

You can retrieve slow query statistics through multiple interfaces depending on your deployment.

### Python Client Integration

Import the tool directly from the MCP main module to fetch statistics programmatically:

```python
from mcp_postgresql_ops.mcp_main import get_pg_stat_statements_top_queries

# Retrieve the 20 slowest queries from the default database

result_md = await get_pg_stat_statements_top_queries()
print(result_md)

```

### HTTP and Chat Interfaces

When deployed with FastAPI, invoke the tool via JSON payload:

```json
{
  "tool": "get_pg_stat_statements_top_queries",
  "arguments": { "limit": 15, "database_name": "myapp" }
}

```

The response returns a markdown table ordered by `total_exec_time DESC`, showing columns including `queryid`, `query`, `calls`, `rows`, and timing metrics.

### Direct Low-Level Access

For custom processing, bypass the formatting layer and access raw data:

```python
from mcp_postgresql_ops.functions import get_pg_stat_statements_data

# Returns a list of dictionaries for programmatic analysis

data = await get_pg_stat_statements_data(limit=10, database="mydb")
for row in data:
    print(row["query"], row["total_exec_time"])

```

## Optimization Workflow for Slow Queries

Once you have extracted data using `pg_stat_statements`, follow this structured approach to improve performance:

1. **Identify high-impact statements** – Focus on queries with high `total_exec_time` or frequent `calls` that appear in the MCP tool output.
2. **Locate source code** – Use the `query` column text to find the corresponding SQL in your application codebase.
3. **Analyze execution plans** – Run `EXPLAIN (ANALYZE, BUFFERS)` on the identified queries to detect sequential scans or excessive sorts.
4. **Implement fixes** – Add missing indexes on WHERE clause columns, rewrite inefficient joins, or adjust PostgreSQL configuration parameters like `work_mem` based on plan evidence.
5. **Validate improvements** – Re-run `get_pg_stat_statements_top_queries` after changes to confirm reduced execution times for the targeted statements.

## Automating Performance Reports

Combine the MCP tool with custom logic to generate actionable reports:

```python
from mcp_postgresql_ops.mcp_main import get_pg_stat_statements_top_queries

async def generate_optimization_report(db: str = None):
    """
    Produce a markdown report highlighting queries with >100ms average execution time.
    """
    md_table = await get_pg_stat_statements_top_queries(limit=10, database_name=db)
    lines = md_table.splitlines()
    header, separator, *rows = lines

    report = ["# PostgreSQL Slow-Query Report", "", header, separator]

    for row in rows:
        cols = [c.strip() for c in row.split("|")]
        avg_ms = float(cols[5])  # mean_exec_time column

        if avg_ms > 100:
            report.append(row + "  **⚠️ High Avg Time**")
        else:
            report.append(row)

    report.append("\n**Suggested Action:** If filtering consistently uses a specific column (e.g., `user_id`), consider `CREATE INDEX ON <table> (user_id);`")
    return "\n".join(report)

```

## Enabling the Extension

Before using these features, ensure `pg_stat_statements` is installed. The repository includes [`scripts/enable-pgsql-extensions.sh`](https://github.com/call518/mcp-postgresql-ops/blob/main/scripts/enable-pgsql-extensions.sh) to automate extension setup in Docker-based PostgreSQL instances. Alternatively, run:

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

```

## Summary

- The MCP PostgreSQL server provides version-agnostic access to `pg_stat_statements` through the `get_pg_stat_statements_top_queries` tool.
- **Automatic compatibility handling** in [`version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/version_compat.py) eliminates manual SQL differences between PostgreSQL 12 and 13+.
- **Extension safety checks** in [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) prevent errors on unconfigured databases.
- Data is returned as formatted markdown tables via [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py), suitable for both human review and automated processing.
- Focus optimization efforts on queries with the highest `total_exec_time` for maximum performance ROI.

## Frequently Asked Questions

### What is the difference between total_time and total_exec_time in pg_stat_statements?

PostgreSQL 12 uses `total_time` and `mean_time` to track query execution duration, while PostgreSQL 13 and later versions renamed these columns to `total_exec_time` and `mean_exec_time` to clarify that they measure execution time only (excluding planning time). The MCP server automatically detects your version and queries the correct columns.

### How do I enable pg_stat_statements if the MCP tool reports the extension is missing?

Execute `CREATE EXTENSION IF NOT EXISTS pg_stat_statements;` as a superuser in your target database, or use the provided [`scripts/enable-pgsql-extensions.sh`](https://github.com/call518/mcp-postgresql-ops/blob/main/scripts/enable-pgsql-extensions.sh) script for Docker deployments. You must also ensure `shared_preload_libraries = 'pg_stat_statements'` is set in postgresql.conf and restart the server.

### Can I filter pg_stat_statements results by specific databases or time windows?

The `get_pg_stat_statements_top_queries` tool accepts a `database_name` parameter to restrict results to a specific database. For custom filtering (e.g., specific time windows or query patterns), use the low-level `get_pg_stat_statements_data()` function and apply additional Python filtering logic to the returned list of dictionaries.

### Why does the MCP server check for extension existence before querying?

The `check_extension_exists()` function prevents runtime errors and provides clear feedback when `pg_stat_statements` is not installed. This check occurs in [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py) before any data retrieval, ensuring the tool fails gracefully with an informative message rather than a database error.