PostgreSQL-Performance-Tuner-Mcp

Retrieve slow queries from PostgreSQL using pg_stat_statements.

Returns the top N slowest queries ordered by total execution time. Requires the pg_stat_statements extension to be enabled.

The results include:

• Query text (normalized)

• Total execution time

• Number of calls

• Mean execution time

• Rows returned

• Shared buffer hits/reads for cache analysis

Analyze a SQL query's execution plan and performance characteristics.

Uses EXPLAIN ANALYZE to execute the query and capture detailed timing information. Provides analysis of:

• Execution plan with actual vs estimated rows

• Timing breakdown by operation

• Buffer usage and I/O statistics

• Potential performance issues and recommendations

WARNING: This actually executes the query! For SELECT queries this is safe, but be careful with INSERT/UPDATE/DELETE - use analyze_only=false for those.

Get detailed statistics for database tables.

Returns information about:

• Table size (data, indexes, total)

• Row counts and dead tuple ratio

• Last vacuum and analyze times

• Sequential vs index scan ratios

• Cache hit ratios

This helps identify tables that may need maintenance (VACUUM, ANALYZE) or have performance issues.

Get AI-powered index recommendations for your database.

Analyzes your query workload (from pg_stat_statements) and recommends indexes that would improve performance. Uses a sophisticated analysis algorithm that:

1. Identifies slow queries and their access patterns

2. Extracts columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses

3. Generates candidate indexes (single-column and composite)

4. If HypoPG is available, tests indexes without creating them

5. Uses a greedy optimization algorithm to select the best index set

The recommendations consider:

• Query frequency and total execution time

• Estimated improvement from each index

• Index size and maintenance overhead

• Avoiding redundant indexes

Run EXPLAIN on a query, optionally with hypothetical indexes.

This tool allows you to see how a query would perform with proposed indexes WITHOUT actually creating them. Requires HypoPG extension for hypothetical testing.

Use this to:

• Compare execution plans with and without specific indexes

• Test if a proposed index would be used

• Estimate the performance impact of new indexes

Returns both the original and hypothetical execution plans for comparison.

Manage HypoPG hypothetical indexes for testing.

HypoPG allows you to create "hypothetical" indexes that exist only in memory and can be used to test query plans without the overhead of creating real indexes.

Actions:

• create: Create a new hypothetical index

• list: List all current hypothetical indexes

• drop: Drop a specific hypothetical index

• reset: Drop all hypothetical indexes

• estimate_size: Estimate the size of a hypothetical index

This is useful for:

• Testing if an index would improve a query

• Comparing different index strategies

• Estimating index storage requirements

Find indexes that are not being used or are duplicates.

Identifies:

• Indexes with zero or very few scans since last stats reset

• Duplicate indexes (same columns in same order)

• Overlapping indexes (one index is a prefix of another)

Removing unused indexes can:

• Reduce storage space

• Speed up INSERT/UPDATE/DELETE operations

• Reduce vacuum and maintenance overhead

Perform a comprehensive database health check.

Analyzes multiple aspects of PostgreSQL health:

• Connection statistics and pool usage

• Cache hit ratios (buffer and index)

• Lock contention and blocking queries

• Replication status (if configured)

• Transaction wraparound risk

• Disk space usage

• Background writer statistics

• Checkpoint frequency

Returns a health score with detailed breakdown and recommendations.

Get information about currently active queries and connections.

Shows:

• All active queries and their duration

• Idle transactions that may be holding locks

• Blocked queries waiting for locks

• Connection state breakdown

Useful for:

• Identifying long-running queries

• Finding queries that might need optimization

• Detecting stuck transactions

• Troubleshooting lock contention

Analyze PostgreSQL wait events to identify bottlenecks.

Wait events indicate what processes are waiting for:

• Lock: Waiting for locks on tables/rows

• IO: Waiting for disk I/O

• CPU: Waiting for CPU time

• Client: Waiting for client communication

• Extension: Waiting in extension code

This helps identify:

• I/O bottlenecks

• Lock contention patterns

• Resource saturation

Review PostgreSQL configuration settings and get recommendations.

Analyzes key performance-related settings:

• Memory settings (shared_buffers, work_mem, etc.)

• Checkpoint settings

• WAL settings

• Autovacuum settings

• Connection settings

Compares against best practices and system resources.