Query Performance Analyzer

Overview

Analyze slow database queries using execution plans, wait statistics, and I/O metrics across PostgreSQL, MySQL, and MongoDB. This skill captures EXPLAIN output, identifies sequential scans on large tables, detects missing indexes, measures buffer cache hit ratios, and produces actionable optimization recommendations ranked by expected performance impact.

Prerequisites

• Database credentials with permissions to run EXPLAIN ANALYZE (PostgreSQL), EXPLAIN FORMAT=JSON (MySQL), or explain() (MongoDB)
• pg_stat_statements extension enabled for PostgreSQL (provides aggregated query statistics)
• Access to slow query logs or performance_schema (MySQL)
• Baseline query execution times for comparison
• psql, mysql, or mongosh CLI tools installed

Instructions

1. Identify the slowest queries by examining pg_stat_statements (PostgreSQL): SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20. For MySQL, enable and query the slow query log or performance_schema.events_statements_summary_by_digest.

2. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on each slow query in PostgreSQL, or EXPLAIN ANALYZE FORMAT=JSON in MySQL. Capture the full execution plan including actual row counts, loop iterations, and buffer usage.