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.

Note: This tool focuses on user/application queries only. System catalog queries (pg_catalog, information_schema, pg_toast) are automatically excluded.

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 user/client database tables.

Note: This tool analyzes only user-created tables and excludes PostgreSQL system tables (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom 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

Note: This tool focuses on user/client tables only and excludes system catalog tables (pg_catalog, information_schema, pg_toast).

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 by specifying table and columns

• 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

• check: Check HypoPG extension status and availability

• hide: Hide an existing real index from the query planner (useful for testing what-if scenarios)

• unhide: Unhide a previously hidden index

• list_hidden: List all currently hidden indexes

• explain_with_index: Create a hypothetical index and explain a query with before/after comparison

This is useful for:

• Testing if an index would improve a query

• Comparing different index strategies

• Estimating index storage requirements

• Testing query performance without specific existing indexes (hide)

• Simulating index removal scenarios

Find indexes that are not being used or are duplicates.

Note: This tool analyzes only user/client indexes and excludes system catalog indexes (pg_catalog, information_schema, pg_toast). It focuses on your application's custom tables only.

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.

Note: This tool focuses on user/client tables and excludes PostgreSQL system tables (pg_catalog, information_schema, pg_toast) from analysis.

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.

Note: By default, this tool excludes system/background processes and focuses on client backend queries to help you analyze your application's query patterns. System catalog queries are filtered out unless explicitly requested.

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.

Note: This tool focuses on client backend processes and excludes system background processes to help identify bottlenecks in your application queries.

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.

Analyze table bloat using the pgstattuple extension.

Note: This tool analyzes only user/client tables and excludes PostgreSQL system tables (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom tables.

Uses pgstattuple to get accurate tuple-level statistics including:

• Dead tuple count and percentage

• Free space within the table

• Physical vs logical table size

This helps identify tables that:

• Need VACUUM to reclaim space

• Need VACUUM FULL to reclaim disk space

• Have high bloat affecting performance

Requires the pgstattuple extension to be installed: CREATE EXTENSION IF NOT EXISTS pgstattuple;

Note: pgstattuple performs a full table scan, so use with caution on large tables. For large tables, consider using pgstattuple_approx instead (use_approx=true).

Analyze index bloat using pgstatindex from pgstattuple extension.

Note: This tool analyzes only user/client indexes and excludes PostgreSQL system indexes (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom indexes.

Uses pgstatindex to get B-tree index statistics including:

• Leaf page density (avg_leaf_density) - lower values indicate more bloat

• Fragmentation percentage

• Empty and deleted pages

Helps identify indexes that:

• Need REINDEX to improve performance

• Have high fragmentation

• Are wasting storage space

Requires the pgstattuple extension: CREATE EXTENSION IF NOT EXISTS pgstattuple;

Note: Also supports GIN indexes (pgstatginindex) and Hash indexes (pgstathashindex).

Get a comprehensive summary of database bloat across tables and indexes.

Note: This tool analyzes only user/client tables and indexes, excluding PostgreSQL system objects (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom objects.

Provides a high-level overview of:

• Top bloated tables by wasted space

• Top bloated indexes by estimated bloat

• Total reclaimable space estimates

• Priority maintenance recommendations

Uses pgstattuple_approx for tables (faster) and pgstatindex for B-tree indexes. Requires the pgstattuple extension to be installed.

Best for: Quick assessment of database bloat and maintenance priorities.