query-optimization-catalog: Скилл оптимизации SQL запросов

--- name: query-optimization-catalog description: "SQL query optimization catalog. An extension skill for performance-analyst that provides index strategies (B-Tree/Hash/GIN/GiST), execution plan analysis, N+1 problem resolution, partitioning strategies, and per-pattern optimization techniques for slow queries. Use when performing DB performance analysis involving 'query optimization', 'index design', 'execution plans', 'N+1 problems', 'partitioning', 'slow queries', etc. Note: data modeling and security configuration are outside the scope of this skill." --- # Query Optimization Catalog — SQL Query Optimization Catalog A reference of index strategies, execution plan analysis, and query anti-pattern resolution used by the performance-analyst agent during performance optimization. ## Target Agent `performance-analyst` — Directly applies the optimization techniques from this skill to performance analysis and index design. ## Index Strategies ### Index Type Selection Guide | Index Type | Suitable Queries | DBMS | Characteristics | |-----------|-----------------|------|-----------------| | **B-Tree** | `=`, `<`, `>`, `BETWEEN`, `ORDER BY` | All | General purpose, default | | **Hash** | `=` equality only | PostgreSQL, MySQL | No range searches | | **GIN** | Arrays, JSONB, full-text search | PostgreSQL | Multi-value indexing | | **GiST** | Spatial (geometry), ranges | PostgreSQL | PostGIS, range types | | **BRIN** | Time-series, naturally sorted data | PostgreSQL | Very small size | | **Fulltext** | Full-text search | MySQL, PostgreSQL | Replaces LIKE '%word%' | ### Composite Index Design Principles #### Leftmost Prefix Rule ```sql INDEX idx_abc ON table(a, b, c) -- Usable: WHERE a = 1 -- O (a only) WHERE a = 1 AND b = 2 -- O (a, b) WHERE a = 1 AND b = 2 AND c = 3 -- O (all columns) WHERE a = 1 AND c = 3 -- Partial (a only, c skipped) -- Not usable: WHERE b = 2 -- X (a missing) WHERE c = 3 -- X (a, b missing) ``` #### Column Order Decision Rules 1. **WHERE equality (=) condition** columns first 2. **Sort (ORDER BY)** columns next 3. **Range (<, >, BETWEEN)** columns last 4. **Higher cardinality** first (but rules 1-3 take priority) ### Covering Indexes Return query results using only the index (no table access needed). ```sql -- Covering index CREATE INDEX idx_covering ON orders(user_id, status, created_at); -- This query responds from the index only (Index Only Scan) SELECT status, created_at FROM orders WHERE user_id = 123; ``` ### Index Add/Remove Decision Guide | Scenario | Add Index? | Reason | |----------|-----------|--------| | Column frequently used in WHERE clause | Yes | Improves search speed | | FK column in JOIN ON clause | Yes | JOIN performance | | Column frequently used in ORDER BY | Yes | Avoids sorting | | Very low cardinality (boolean, etc.) | No | Minimal benefit | | Frequently UPDATEd column | Carefully | Write performance degradation | | Small table (under 10K rows) | No | Full scan is faster | ## Slow Query Anti-Patterns & Solutions ### 1. N+1 Problem ```sql -- Anti-pattern: Individual queries in a loop SELECT * FROM users; -- For each user: SELECT * FROM orders WHERE user_id = ?; -- Repeated N times! -- Solution: JOIN or IN SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id; -- Or batch loading SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...); ``` ### 2. SELECT * ```sql -- Anti-pattern SELECT * FROM products WHERE category = 'electronics'; -- Solution: Only needed columns SELECT id, name, price FROM products WHERE category = 'electronics'; -- Enables covering index usage ``` ### 3. Function Invalidating Index ```sql -- Anti-pattern: Applying function to indexed column WHERE YEAR(created_at) = 2025 -- Solution: Convert to range condition WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' ``` ### 4. OR Condition Invalidating Index ```sql -- Anti-pattern WHERE status = 'active' OR category = 'books' -- Solution: UNION ALL SELECT * FROM products WHERE status = 'active' UNION ALL SELECT * FROM products WHERE category = 'books' AND status != 'active' ``` ### 5. Subquery vs JOIN ```sql -- Anti-pattern: Correlated subquery SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id); -- Solution: JOIN + aggregate SELECT o.* FROM orders o JOIN (SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id) a ON o.user_id = a.user_id WHERE o.total > a.avg_total; ``` ### 6. OFFSET Pagination ```sql -- Anti-pattern: Deep OFFSET SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000; -- Solution: Cursor-based (Keyset) SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20; ``` ### 7. Large IN Clause ```sql -- Anti-pattern: Thousands of IDs WHERE id IN (1, 2, 3, ..., 10000) -- Solution: Temporary table or JOIN -- PostgreSQL: VALUES or ANY(ARRAY[...]) -- General: Batch processing (500 at a time) ``` ## EXPLAIN Analysis Guide (PostgreSQL) ### Reading Execution Plans ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123 AND status = 'completed'; ``` ### Key Node Types | Node | Meaning | Performance | |------|---------|-------------| | Seq Scan | Full table scan | Slow (large data) | | Index Scan | Index + table access | Moderate | | Index Only Scan | Response from index only | Fast | | Bitmap Index Scan | Bitmap-based index scan | Moderate | | Nested Loop | Nested loop join | Suitable for small datasets | | Hash Join | Hash table join | Large equality joins | | Merge Join | Sort-merge join | Large sorted data | | Sort | Sort operation | Watch memory/disk | ### Warning Signs - `Seq Scan` on a large table -> Index needed - `Sort` with `external merge Disk` -> Insufficient work_mem - `actual rows` >> `estimated rows` -> Stale statistics (ANALYZE needed) - `Nested Loop` with a large table -> Encourage Hash Join ## Partitioning Strategy ### Signs Partitioning Is Needed - Table size > hundreds of millions of rows - Time-series data (logs, events, metrics) - Periodic deletion/archiving of old data - Most queries target specific time ranges ### Partitioning Types | Type | Split Criterion | Suitable For | Example | |------|----------------|-------------|---------| | **Range** | Value range | Time-series | Monthly/yearly partitions | | **List** | Value list | Categories | By region, by status | | **Hash** | Hash value | Even distribution | user_id % N | ### Range Partitioning Example (PostgreSQL) ```sql CREATE TABLE events ( id BIGINT, event_time TIMESTAMPTZ, data JSONB ) PARTITION BY RANGE (event_time); CREATE TABLE events_2025_q1 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2025-04-01'); ``` ## Caching Strategy | Level | Tool | Suitable Data | TTL | |-------|------|--------------|-----| | **Query cache** | Redis/Memcached | Frequently read query results | 30s-5min | | **ORM cache** | Prisma/TypeORM cache | Entity-level | 1-5min | | **Aggregation cache** | Materialized View | Statistics/dashboards | 1hr+ | | **CDN cache** | CloudFront/CloudFlare | Static API responses | 5-60min | ### Cache Invalidation Strategies - **TTL-based**: Auto-refresh after expiration time - **Event-based**: Immediate deletion on data change - **Write-Through**: Update cache on writes - **Cache-Aside**: On read miss, query DB and store in cache