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

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 (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 (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 (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 (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:

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:

{
  "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:

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:

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 to automate extension setup in Docker-based PostgreSQL instances. Alternatively, run:

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 eliminates manual SQL differences between PostgreSQL 12 and 13+.
• Extension safety checks in functions.py prevent errors on unconfigured databases.
• Data is returned as formatted markdown tables via 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 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 before any data retrieval, ensuring the tool fails gracefully with an informative message rather than a database error.

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