**Status**: Production Ready ✅
/plugin marketplace add secondsky/claude-skills/plugin install sql-query-optimization@claude-skillsThis skill inherits all available tools. When active, it can use any tool Claude has access to.
references/error-catalog.mdreferences/explain-analysis.mdreferences/index-strategies.mdreferences/optimization-workflow.mdreferences/performance-monitoring.mdreferences/query-rewrites.mdtemplates/explain-query.sqltemplates/index-examples.sqltemplates/monitoring-queries.sqltemplates/query-rewrites.sqlStatus: Production Ready ✅ Last Updated: 2025-12-15 Latest Versions: PostgreSQL 17, MySQL 8.4 Dependencies: None
-- PostgreSQL: Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find slowest queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;
-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem
-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);
-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x
| Rule | Why | Example |
|---|---|---|
| Index foreign keys | JOINs need indexed columns | CREATE INDEX idx_orders_user ON orders(user_id) |
| Use EXPLAIN ANALYZE before production | Verify query plan is optimal | EXPLAIN (ANALYZE, BUFFERS) <query> |
| Select specific columns | Reduces data transfer 90% | SELECT id, name FROM users not SELECT * |
| Add LIMIT to unbounded queries | Prevents memory exhaustion | SELECT * FROM logs ORDER BY id LIMIT 100 |
| Use prepared statements | Prevents SQL injection + faster | db.query('SELECT * FROM users WHERE id = $1', [id]) |
| Run ANALYZE after bulk operations | Updates query planner statistics | ANALYZE table_name |
| Monitor pg_stat_statements | Track query performance over time | Review daily for regressions |
| Use connection pooling | Reduces connection overhead 10x | new Pool({ max: 20 }) |
| Anti-Pattern | Problem | Fix |
|---|---|---|
| SELECT * in production | Fetches unnecessary columns | Select specific columns only |
| Leading wildcard LIKE '%term%' | Cannot use index | Use full-text search instead |
| String concatenation for SQL | SQL injection vulnerability | Use parameterized queries |
| No LIMIT on large results | Memory exhaustion | Always add LIMIT + pagination |
| N+1 queries in loops | Network latency × N | Use JOIN or batch loading |
| Ignoring EXPLAIN output | Deploy slow queries to production | Always EXPLAIN before deploy |
| Multiple INSERTs in loop | Slow bulk operations | Use batch INSERT with multiple VALUES |
| OFFSET for pagination | O(n) time, scans skipped rows | Use cursor-based pagination |
Symptom: Seq Scan on orders (cost=0.00..150000.00) on 1M+ rows
Cause: No index on filter column
Fix: CREATE INDEX idx_orders_column ON orders(column)
Impact: 10-100x faster
Symptom: Slow JOINs (5+ seconds)
Cause: Foreign key columns not indexed
Fix: CREATE INDEX idx_orders_user_id ON orders(user_id)
Impact: 50-500x faster JOINs
Symptom: 1 + N queries for N records
Cause: ORM lazy loading in loop
Fix: Use JOIN or eager loading: SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id
Impact: N queries → 1 query
Symptom: WHERE name LIKE '%search%' sequential scan
Cause: Index cannot match middle of string
Fix: Use full-text search (GIN index) or trigrams
Impact: 100-1000x faster
Symptom: High network traffic, slow responses
Cause: Fetches all 50 columns instead of needed 3
Fix: SELECT id, name, email (explicit column list)
Impact: 90% less data transfer
Symptom: Server out of memory, query timeout
Cause: Attempting to return 5M rows
Fix: SELECT * FROM logs WHERE ... LIMIT 100 + pagination
Impact: Constant memory usage
Symptom: Wrong query plan chosen despite index
Cause: PostgreSQL statistics outdated
Fix: ANALYZE table_name after bulk operations
Impact: Correct query plan selection
See references/error-catalog.md for all 12 errors with detailed solutions.
| Pattern | Use Case | Example | Performance |
|---|---|---|---|
| B-Tree Index | Equality, range, sort queries | CREATE INDEX idx ON t(col) | Default, best general purpose |
| Composite Index | Multi-column WHERE clauses | CREATE INDEX idx ON t(c1, c2) | 5-50x faster than single index |
| Covering Index | Include all query columns | CREATE INDEX idx ON t(c1) INCLUDE (c2) | 2-10x faster (no heap fetch) |
| Partial Index | Filter subset of rows | CREATE INDEX idx ON t(c) WHERE status='active' | 50-90% smaller index |
| JOIN Rewrite | Replace IN subquery | INNER JOIN users u ON o.user_id = u.id | 5-20x faster than subquery |
| Batch INSERT | Bulk data loading | INSERT INTO t VALUES (..),(..) | 10-100x faster than individual |
| Cursor Pagination | Large offset performance | WHERE id > last_id LIMIT 100 | Constant time vs O(n) |
-- Increase work_mem for complex queries (reloadable - no restart needed)
SET work_mem = '256MB';
-- Increase shared_buffers for better caching (25% of RAM)
ALTER SYSTEM SET shared_buffers = '8GB';
-- IMPORTANT: shared_buffers requires a full PostgreSQL server restart!
-- This setting is NOT reloadable via pg_reload_conf()
--
-- To apply shared_buffers change:
-- 1. Stop PostgreSQL: sudo systemctl stop postgresql
-- 2. Start PostgreSQL: sudo systemctl start postgresql
-- OR use: sudo systemctl restart postgresql
--
-- Verify the change took effect:
-- SHOW shared_buffers;
-- Enable auto-vacuum (reloadable - can use pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;
-- Reload config (ONLY works for parameters that don't require restart)
-- This will NOT reload shared_buffers - restart required for that!
SELECT pg_reload_conf();
# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G # 70% of RAM
max_connections = 500
slow_query_log = 1
long_query_time = 1
Performance Analysis:
references/explain-analysis.md when: Reading EXPLAIN output, understanding query plans, analyzing buffer statistics, comparing PostgreSQL vs MySQL EXPLAINreferences/performance-monitoring.md when: Setting up monitoring, tracking slow queries over time, monitoring cache hit ratios, identifying bloated tablesIndex Optimization:
references/index-strategies.md when: Choosing index type (B-Tree, GIN, GiST, Hash), creating composite indexes, determining column order, using covering indexes, implementing partial indexes, monitoring index usageQuery Optimization:
references/query-rewrites.md when: Rewriting slow queries, converting subqueries to JOINs, eliminating N+1 queries, implementing pagination, optimizing LIKE queries, batching operationsSystematic Process:
references/optimization-workflow.md when: Following step-by-step optimization process, creating optimization hypothesis, measuring improvements, monitoring long-term performanceError Resolution:
references/error-catalog.md when: Debugging specific errors (sequential scans, missing indexes, N+1 queries, etc.), understanding root causes, implementing verified solutions# EXPLAIN query templates
templates/explain-query.sql
# Index creation patterns
templates/index-examples.sql
# Query rewrite examples
templates/query-rewrites.sql
# Monitoring queries
templates/monitoring-queries.sql
# Comprehensive guides
references/error-catalog.md # All 12 errors + solutions
references/explain-analysis.md # Reading query plans
references/index-strategies.md # Index types & selection
references/query-rewrites.md # Before/after optimizations
references/performance-monitoring.md # Long-term monitoring
references/optimization-workflow.md # Systematic process
PostgreSQL Extensions:
pg_stat_statements - Query performance tracking (built-in)pg_trgm - Trigram similarity search (optional, for fuzzy matching)MySQL:
performance_schema - Performance monitoring (enabled by default in 8.0+)No additional dependencies required.
| Issue | Symptom | Prevention |
|---|---|---|
| Sequential scans | Seq Scan on 1M+ rows | Index filter columns before production |
| Missing FK indexes | Slow JOINs | Always index foreign keys |
| N+1 queries | 1+N database calls | Use JOIN or eager loading |
| Leading wildcards | LIKE '%x%' slow | Use full-text search (GIN) |
| SELECT * bloat | High network traffic | Select specific columns |
| No LIMIT | Memory exhaustion | Always LIMIT unbounded queries |
| Stale statistics | Wrong query plans | ANALYZE after bulk operations |
| Wrong index order | Index exists but not used | Match query pattern |
| Missing composite | Multiple WHERE slow | Create composite index |
| No connection pool | High latency | Implement pooling (20-50 connections) |
| SQL injection | Security vulnerability | Use prepared statements only |
| Temp spills | Disk I/O on sorts | Increase work_mem |
Production Deployment:
Before Optimization:
-- Query: Fetch user orders
SELECT * FROM orders WHERE user_id = 123;
-- Performance:
-- Execution time: 2500ms
-- Seq Scan on orders (1M rows scanned)
-- Network: 50MB transferred
-- No index on user_id
After Optimization:
-- Add index
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- Optimize query
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;
-- Performance:
-- Execution time: 12ms (208x faster!)
-- Index Scan using idx_orders_user_id (100 rows)
-- Network: 50KB transferred (1000x less!)
-- Covering index with INCLUDE
Result: 208x faster execution, 1000x less data transfer
For comprehensive optimization guidance, error resolution, and production patterns, load the appropriate reference files listed in "When to Load References" above.
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Applies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.