Optimize SQL Queries for Peak Performance

SQL Optimization Patterns is a skill for diagnosing slow SQL with query plans, preserving query results, and verifying indexing or query changes against representative data. Its guiding stance is that an index or a query rewrite is a hypothesis, not a universal speed improvement, so every change is checked against the observed plan and against correctness.

The skill starts from EXPLAIN. It shows the basic form, the form with actual execution statistics, and the verbose form with buffers, and it lists what to watch in the output: Seq Scan (which may be appropriate for small tables or queries returning much of a table), Index Scan (assess selectivity and heap reads, not just the node name), Index Only Scan (check heap fetches and visibility), Nested Loop, Hash Join and Merge Join as join methods, plus cost, estimated rows and actual time.

It then covers index strategy. The listed index types are B-Tree (the default, for equality and range queries), Hash (equality only), GIN (full-text search, array queries and JSONB), GiST (geometric data and full-text search) and BRIN (very large tables with correlation). Examples cover composite indexes where column order matters, partial indexes, expression indexes, covering indexes with INCLUDE, full-text indexes and JSONB indexes.

Query patterns include avoiding SELECT *, keeping functions out of WHERE clauses unless a functional index exists, and comparing equivalent JOIN spellings. Five named optimization patterns follow: eliminating N+1 queries with a JOIN or a batched IN query, cursor-based pagination instead of large OFFSET values, efficient aggregation (COUNT, GROUP BY), subquery rewrites including CTEs, and batch INSERT and UPDATE operations with COPY or temporary tables for bulk work. Advanced techniques include materialized views (a concurrent refresh needs an already populated view and a non-partial unique index on plain columns), range partitioning by date so a query touches only the matching partitions, and query hints: a MySQL USE INDEX restricts candidate indexes without forcing an index scan, and turning off enable_nestloop discourages nested loops for diagnosis rather than banning them. The playbook also lists common pitfalls (over-indexing, unused indexes, implicit type conversion, leading-wildcard LIKE) and monitoring queries built on pg_stat_statements and pg_stat_user_tables.

For tie-safe pagination, the skill gives this form, which needs a supporting index:

SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Use it when 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, or resolving N+1 query problems.

Do not use it when the task is unrelated to SQL optimization patterns or needs a different domain or tool outside this scope. The skill is cautious about safety: start read-only and obtain authorization before DDL, data changes, configuration changes or maintenance. Because EXPLAIN ANALYZE executes the statement, use approved representative data, account for functions and triggers, and do not assume a transaction rollback undoes every side effect. SQLite correctness checks do not establish production performance or safety.

The playbook closes with best practices: index selectively because too many indexes slow writes, monitor with slow query logs, keep statistics updated by running ANALYZE regularly, use appropriate data types (smaller types perform better), normalize thoughtfully, cache frequently accessed data at the application level, pool connections, and run regular maintenance with VACUUM, ANALYZE and index rebuilds (VACUUM FULL reclaims space but locks the table). Its common pitfalls are over-indexing, unused indexes that waste space and slow writes, missing indexes that cause full table scans, implicit type conversion that prevents index use, OR conditions (which may combine indexes, so inspect the plan), a LIKE with a leading wildcard that ordinary B-tree indexes often cannot serve, and functions in WHERE unless a functional index exists.

Monitoring queries are included. The pg_stat_statements query lists the slowest statements by mean execution time and needs the extension already configured, suitable privileges, and care because query text can contain sensitive values. A pg_stat_user_tables query finds tables with heavy sequential scans, and a pg_stat_user_indexes query lists indexes with zero scans ordered by size, where zero scans are observations since statistics were last reset, not permission to drop an index.

Before starting, confirm the database engine and version, the query parameters, the expected rows, the data distribution and the permitted environment. The skill asks you to compare result sets before comparing performance, and to record the observed plan, the timing conditions and the correctness checks. The outputs are a diagnosis grounded in the query plan, proposed indexes or rewrites, and verification that results were preserved. PostgreSQL-specific examples target PostgreSQL 18 unless marked otherwise, and they assume an authorized test database with matching tables.

The playbook also includes an executable SQLite batch-loading example with bound values and an empty-input case, with a batch limit of 500 IDs. Values are bound, never interpolated, and parents with no orders should be preserved when reconstructing parent and child results, because a JOIN duplicates parent rows when multiple children match.

The examples cover PostgreSQL (including pg_class, materialized views and partitioning), MySQL index hints, and SQLite through Python. Detailed patterns and examples live in resources/implementation-playbook.md, and the playbook cites the PostgreSQL 18 documentation for EXPLAIN, materialized view refresh and statement statistics.

Developers and database engineers who need to investigate slow queries, design indexes or reduce database load, and who want each change verified against correctness and against a real plan rather than assumed to be faster.