Verified:GitHub

engineering-database-optimizer

Diagnose slow queries and tune PostgreSQL and MySQL performance through execution plans, indexes, and configuration.

Updated Jan 28, 2026

Search Tags:#postgresql #query performance #performance tuning #mysql #database optimization #index design #explain analyze

Ready to run

npx skills add https://github.com/scanady/nexus-skills --skill engineering-database-optimizer-scanady

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: engineering-database-optimizer
Source: https://github.com/scanady/nexus-skills/tree/main/skills/engineering-database-optimizer
Command: npx skills add https://github.com/scanady/nexus-skills --skill engineering-database-optimizer-scanady

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Slow queries, missing indexes, lock contention, and misconfigured database settings degrade application performance, and diagnosing root causes requires deep expertise in execution plans and engine internals. ## Core Features & Use Cases - Query Optimization: Analyze EXPLAIN ANALYZE output, rewrite subqueries, replace IN with EXISTS, and implement keyset pagination. - Index Strategy Design: Create B-tree, covering, partial, expression, GIN, and full-text indexes while avoiding over-indexing and write amplification. - Configuration Tuning: Tune PostgreSQL shared_buffers, work_mem, autovacuum, and WAL settings, plus MySQL InnoDB buffer pool, I/O capacity, and replication parameters. - Use Case: A PostgreSQL endpoint takes 8 seconds. Use this Skill to inspect pg_stat_statements, identify a sequential scan on a 10M-row table, design a partial index, and validate the improvement with before/after EXPLAIN ANALYZE. ## Quick Start Ask the agent to analyze why a specific query is slow and recommend indexes or configuration changes for your PostgreSQL or MySQL database.

Frequently Asked Questions about engineering-database-optimizer

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I find slow queries in PostgreSQL?▼

Enable the pg_stat_statements extension and query it ordered by total_exec_time to find the slowest queries. Combine this with EXPLAIN (ANALYZE, BUFFERS) on individual statements to see actual row counts, buffer hits, and scan types.

How do I analyze a MySQL slow query log?▼

Enable slow_query_log with long_query_time set to your threshold, then analyze the log with pt-query-digest. Use Performance Schema's events_statements_summary_by_digest table for live query latency statistics without parsing log files.

When should I use a partial index instead of a full index?▼

Use a partial index when queries consistently filter on a small subset of rows, such as active users or pending orders. Partial indexes are smaller, faster to scan, and reduce write amplification compared to indexing the entire table.

Does adding more indexes always improve query performance?▼

No. Every index slows down INSERT and UPDATE operations and consumes storage. Create indexes only after confirming the access path via EXPLAIN ANALYZE, and remove unused indexes identified through pg_stat_user_indexes or Performance Schema.

Why is my query doing a sequential scan instead of using an index?▼

Sequential scans occur when the planner estimates the index is not cheaper, often due to stale statistics, low selectivity, or a function wrapping the indexed column. Run ANALYZE to refresh statistics and rewrite expressions like DATE(column) into range comparisons.

What are the limitations of database configuration tuning?▼

Configuration tuning cannot fix fundamentally bad queries or missing indexes; it only optimizes resource usage. Changes like shared_buffers or innodb_buffer_pool_size require restarts or reloads, and aggressive settings like synchronous_commit off trade durability for speed.