Slow Query Diagnosis for PostgreSQL and MySQL
Reads your EXPLAIN plan, finds the node eating the time, and gives the index, rewrite, or setting that fixes it, plus how to verify.
Before & after
Same prompt, same agent — the only difference is the skill.
That's a slow query. There are several things that could be causing that. You might be missing an index, or the query itself might not be written efficiently. I'd want to see the execution plan to understand where the time is being spent. Does the query have a lot of joins? Are you using any complex functions? It's hard to say without more details. If you can share the query and table structures, I can look at what might be slowing it down. Have you tried analyzing the execution plan already?
SLOW QUERY ANALYSIS Original Query: 45.2s execution time EXECUTION PLAN BREAKDOWN Full table scan on orders (50M rows): 38.4s ← BOTTLENECK Hash join on customers: 4.1s Sort and filter: 2.7s ROOT CAUSE No index on orders(status, created_at) Query filtering on status='active' AND created_at > date Database must scan all 50M rows OPTIMIZED QUERY ADD INDEX: CREATE INDEX idx_orders_status_created ON orders(status, created_at); Rewritten query uses index for both status and date filtering: SELECT o.id, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE o.status = 'active' AND o.created_at > NOW() - INTERVAL 30 days ORDER BY o.created_at DESC; RESULT: 1.3s execution time (34x improvement) BREAKDOWN Index seek: 0.8s Join: 0.4s Sort: 0.1s PARTITIONING STRATEGY (If index not enough) Partition orders table by month on created_at Prunes 97% of partitions for 30-day queries Combined with index: 200ms execution time MONITOR Add query to slow-query log alerts Recheck index usage monthly
About this skill
Your query took 40ms last month and takes 6 seconds now. Generic advice says "add an index," so you add one, and nothing changes. Sometimes it gets worse. This skill reads the actual execution plan the way a database specialist would. It finds the node that is really eating the time, explains why the planner chose it, and gives you the exact fix along with the plan you should see afterward.
What it does
- Reads PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)and MySQLEXPLAIN ANALYZE/FORMAT=JSONoutput, including per-loop timing math, estimate-vs-actual gaps, disk spills, and heap fetches - Designs composite, covering, partial, and expression indexes with correct column order, and checks them against overlapping indexes and write cost
- Spots query shapes that block index use: functions on columns, implicit casts, OFFSET pagination,
NOT INwith NULLs, OR across columns - Separates bad-statistics problems from missing-index problems, and plan flips from lock waits
- Diagnoses lock contention and gives safe rollout steps (
CREATE INDEX CONCURRENTLY, online DDL,lock_timeout) - Pushes back on fixes that won't help, such as partitioning for point lookups or raising
work_memglobally
What you get
A short diagnosis that names the bottleneck node. Then the exact SQL fix, the plan change you should expect, rollout risks, and a verification step you can run yourself.
Best for
Backend developers, on-call engineers, and small teams without a dedicated DBA who run PostgreSQL or MySQL in production and need to fix a specific slow query or endpoint today.
SKILL.md preview
---
name: slow-query-diagnosis-postgres-mysql
description: Diagnoses slow PostgreSQL and MySQL queries by reading the real execution plan. It finds the node that dominates the time, separates stale-statistics, missing-index, query-shape, and locking causes, and delivers an exact fix with rollout risks and a verification step. Use when the user says "this query is slow", "here's my EXPLAIN ANALYZE", "what index should I add", "my endpoint got slow after the table grew", "I added an index and Postgres still won't use it", "queries randomly hang", "MySQL says Using filesort / Using temporary", or "should I partition this table".
---
# Slow Query Diagnosis for PostgreSQL and MySQL
… (sign up to view the full skill)More development skills
View all Development skills →Production Go: Concurrency, Services & Profiling
Production Go code with leak-free goroutines, context-correct services, wrapped errors, and pprof-driven performance fixes.
Backend Feature Build Plan
Builds one backend feature through gates: confirmed requirements, one-screen design, small tested slices, and a deploy/rollback handoff.
Consistent, Safe Application Logging
Adds structured, correlated logs with useful severity, safe fields, bounded volume, and targeted verification.
Reliable WebSocket Messaging and Scaling
Build WebSocket and Socket.IO systems with authorized rooms, reconnect recovery, bounded queues, and multi-node presence.