sql-pro_skill

Overview

This skill is a senior-level SQL optimization and database design assistant focused on delivering efficient, scalable queries and schema guidance across PostgreSQL, MySQL, SQL Server, and Oracle. It targets complex query design, index strategy, and performance tuning to meet strict latency goals. Use it to translate slow, brittle SQL into maintainable, set-based solutions with documented performance gains.

How this skill works

I analyze schema, indexes, and query execution plans to identify bottlenecks and anti-patterns. I propose rewritten queries (CTEs, window functions, set-based logic), recommend covering and partitioning indexes, and outline testing steps against production-scale data. Deliverables include optimized SQL with inline comments, index DDL and rationale, execution-plan analysis, and before/after performance metrics.

When to use it

• Optimizing slow queries after reviewing EXPLAIN/ANALYZE output
• Designing complex reports using CTEs, window functions, or recursive queries
• Creating or refining index strategies and partitioning plans
• Migrating or adapting queries between database dialects
• Reviewing schema for OLAP patterns, star schemas, or ETL transformations

Best practices

• Always analyze execution plans before changing queries
• Prefer set-based operations; avoid row-by-row processing and cursors when possible
• Filter early and push predicates into indexes to reduce scanned rows
• Create covering indexes for frequently-run, selective queries and document the rationale
• Test changes with production-scale data and measure before/after latency and resource usage
• Handle NULLs explicitly and account for dialect-specific semantics

Example use cases

• Rewrite a multi-join report into CTEs and window functions to remove sorting bottlenecks
• Add covering indexes and tune statistics for a table causing frequent full table scans
• Convert cursor-based ETL to bulk set-based operations with chunking and proper indexing
• Analyze a slow query plan and recommend partitioning or materialized views for OLAP workloads
• Migrate SQL Server-specific queries to PostgreSQL, adjusting functions and optimizer hints