sql-optimization-patterns
Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database schemas, or optimizing application performance.
- Installs
- 19.9K
- Last 24h
- +69
- Trending rank
- #2712
- Security
- Pass
Install
Works with every agent the skills CLI supports.
npx skills add https://github.com/wshobson/agents --skill sql-optimization-patterns- 01
Install
Run the command in your project. The CLI asks which agents to install for.
- 02
Describe the task
Ask your agent for something this skill covers. It loads sql-optimization-patterns automatically when relevant.
- 03
Review the result
The agent follows the SKILL.md steps, scripts and references bundled with the skill.
Security audits
Independent scans of the skill's instructions and bundled files.
SKILL.md
The instructions your agent reads when the skill is triggered.
SQL Optimization Patterns
Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
When to Use This Skill
- Debugging slow-running queries
- Designing performant database schemas
- Optimizing application response times
- Reducing database load and costs
- Improving scalability for growing datasets
- Analyzing EXPLAIN query plans
- Implementing efficient indexes
- Resolving N+1 query problems
Core Concepts
1. Query Execution Plans (EXPLAIN)
Understanding EXPLAIN output is fundamental to optimization.
PostgreSQL EXPLAIN:
-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';
-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';
Key Metrics to Watch:
- Seq Scan: Full table scan (usually slow for large tables)
- Index Scan: Using index (good)
- Index Only Scan: Using index without touching table (best)
- Nested Loop: Join method (okay for small datasets)
- Hash Join: Join method (good for larger datasets)
- Merge Join: Join method (good for sorted data)
- Cost: Estimated query cost (lower is better)
- Rows: Estimated rows returned
- Actual Time: Real execution time
2. Index Strategies
Indexes are the most powerful optimization tool.
Index Types:
- B-Tree: Default, good for equality and range queries
- Hash: Only for equality (=) comparisons
- GIN: Full-text search, array queries, JSONB
- GiST: Geometric data, full-text search
- BRIN: Block Range INdex for very large tables with correlation
-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);
-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));
-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);
3. Query Optimization Patterns
Avoid SELECT *:
-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;
-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;
Use WHERE Clause Efficiently:
-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Or store normalized data
SELECT * FROM users WHERE email = 'user@example.com';
Optimize JOINs:
-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;
Detailed patterns and worked examples
Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.
Best Practices
- Index Selectively: Too many indexes slow down writes
- Monitor Query Performance: Use slow query logs
- Keep Statistics Updated: Run ANALYZE regularly
- Use Appropriate Data Types: Smaller types = better performance
- Normalize Thoughtfully: Balance normalization vs performance
- Cache Frequently Accessed Data: Use application-level caching
- Connection Pooling: Reuse database connections
- Regular Maintenance: VACUUM, ANALYZE, rebuild indexes
-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;
-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users; -- Reclaim space (locks table)
-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
Common Pitfalls
- Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
- Unused Indexes: Waste space and slow writes
- Missing Indexes: Slow queries, full table scans
- Implicit Type Conversion: Prevents index usage
- OR Conditions: Can't use indexes efficiently
- LIKE with Leading Wildcard:
LIKE '%abc'can't use index - Function in WHERE: Prevents index usage unless functional index exists
Monitoring Queries
-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- Find missing indexes (PostgreSQL)
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
-- Find unused indexes (PostgreSQL)
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
FAQ
What does the sql-optimization-patterns skill do?
Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database schemas, or optimizing application performance.
How do I install sql-optimization-patterns?
Run npx skills add https://github.com/wshobson/agents --skill sql-optimization-patterns in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/sql-optimization-patterns/ (personal) or .claude/skills/sql-optimization-patterns/ (project).
Which AI agents can use sql-optimization-patterns?
Any agent that supports the open Agent Skills format (a folder with a SKILL.md file), including Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Windsurf, Cline, OpenCode and more.
Is sql-optimization-patterns safe to install?
Security partners have reviewed it: Gen Agent Trust Hub passed; Socket passed; Snyk passed; Runlayer passed; ZeroLeaks passed. As with any skill, read the SKILL.md and bundled scripts before installing.
Who maintains sql-optimization-patterns?
It is published by wshobson in wshobson/agents. It has 19,869 recorded installs.
More from wshobson/agents
typescript-advanced-types
wshobson/agents
Master TypeScript's advanced type system including generics, conditional types, mapped types, template literals, and utility types for building type-safe applications. Use when implementing complex type logic, creating reusable type utilities, or ensuring compile-time type safety in TypeScript projects.
tailwind-design-system
wshobson/agents
Build scalable design systems with Tailwind CSS v4, design tokens, component libraries, and responsive patterns. Use when creating component libraries, implementing design systems, or standardizing UI patterns.
nodejs-backend-patterns
wshobson/agents
Build production-ready Node.js backend services with Express/Fastify, implementing middleware patterns, error handling, authentication, database integration, and API design best practices. Use when creating Node.js servers, REST APIs, GraphQL backends, or microservices architectures.
brand-landingpage
wshobson/agents
Brand-first landing page designer — runs a brand-identity interview (colors, typography, shape language), then generates and iterates on a polished landing page via Stitch with deployment-ready HTML. Use when the user asks to create, design, or build a landing page, homepage, or marketing page and has no established visual direction. Skip when they have a design mockup, need a dashboard or app UI, are working at component level, building a multi-page app, or restyling with known design tokens — use frontend-design instead.
python-testing-patterns
wshobson/agents
Implement comprehensive testing strategies with pytest, fixtures, mocking, and test-driven development. Use when writing Python tests, setting up test suites, or implementing testing best practices.
python-performance-optimization
wshobson/agents
Profile and optimize Python code using cProfile, memory profilers, and performance best practices. Use when debugging slow Python code, optimizing bottlenecks, or improving application performance.
Popular data & databases skills
supabase-postgres-best-practices
supabase/agent-skills
Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pgcron, pgmq), vector/semantic search (pgvector), and restoring dumps (pgrestore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.
prisma-client-api
prisma/skills
Prisma Client API reference covering model queries, filters, operators, and client methods. Use when writing database queries, using CRUD operations, filtering data, or configuring Prisma Client. Triggers on "prisma query", "findMany", "create", "update", "delete", "$transaction".
prisma-database-setup
prisma/skills
Deprecated compatibility name for prisma-orm-setup. Use only when a user or existing integration explicitly invokes prisma-database-setup; otherwise select prisma-orm-setup.
prisma-cli
prisma/skills
Prisma ORM 7 CLI reference for existing Prisma 7 applications or explicitly selected Prisma 7 workflows. Covers prisma init, generate, migrate, db, dev, studio, and mcp. For setup, connection repair, or Prisma 8, use prisma-orm-setup; not the Prisma Platform CLI.
prisma-postgres
prisma/skills
Deprecated compatibility name for prisma-postgres-setup. Use only when a user or existing integration explicitly invokes prisma-postgres; otherwise select prisma-postgres-setup.
prisma-driver-adapter-implementation
prisma/skills
Required reference for Prisma ORM 7 SQL driver adapter work. Use when implementing or modifying adapters, adding database drivers, or touching SqlDriverAdapter, Transaction, savepoint, result mapping, or DriverAdapterError behavior. Covers current transaction lifecycle, optional savepoint hooks, original database-error preservation, and verification.