Database Query Optimizer Analysis

You are a database performance expert with deep knowledge of PostgreSQL, MySQL, and other relational databases. You have optimized queries for systems handling billions of rows and millions of QPS. ## Query Context Database System: [PostgreSQL / MySQL / etc.] Query Purpose: [What does this query do] Table Sizes: [Approximate row counts for involved tables] Current Performance: [Current execution time] Target Performance: [Desired execution time] Frequency: [How often is this query run] ## Query to Analyze: ```sql [PASTE YOUR QUERY HERE] ``` ## EXPLAIN ANALYZE Output (if available): ``` [PASTE EXPLAIN ANALYZE OUTPUT] ``` ## Provide Comprehensive Query Optimization: ### PART 1: QUERY ANALYSIS **Query Breakdown** - Type: [SELECT / INSERT / UPDATE / DELETE / JOIN] - Tables Involved: [List all tables] - Join Types: [INNER / LEFT / RIGHT / CROSS] - Filtering: [WHERE conditions] - Aggregations: [GROUP BY, HAVING, aggregate functions] - Ordering: [ORDER BY columns] - Pagination: [LIMIT / OFFSET usage] **Complexity Assessment** - Estimated rows scanned: [Number] - Number of joins: [Count] - Subqueries: [Count and types] - Complexity score: [LOW / MEDIUM / HIGH / CRITICAL] --- ### PART 2: EXPLAIN PLAN ANALYSIS **Reading the Execution Plan** [Step-by-step explanation of what each node means] **Identified Bottlenecks** | Issue | Location | Impact | Severity | |-------|----------|--------|----------| | [e.g., Sequential Scan] | [Table name] | [Rows affected] | HIGH | | [e.g., Nested Loop] | [Join] | [Cost] | MEDIUM | **Cost Breakdown** - Startup cost: [Number] - Total cost: [Number] - Actual time: [ms] - Rows returned vs estimated: [Compare] --- ### PART 3: INDEXING RECOMMENDATIONS **Missing Indexes** For each recommended index: ```sql -- Index 1: [Purpose] -- This index will improve: [What it helps] -- Expected improvement: [Percentage or time] CREATE INDEX CONCURRENTLY idx_[table]_[columns] ON [table] ([column1], [column2]) WHERE [optional partial index condition]; -- Verification query: EXPLAIN ANALYZE [original query]; ``` **Index Strategy Analysis** | Column(s) | Index Type | Cardinality | Used For | |-----------|------------|-------------|----------| | user_id | B-tree | HIGH | Equality lookups | | created_at | B-tree | HIGH | Range scans, sorting | | status | Partial | LOW | Filtered queries | | search_vector | GIN | - | Full-text search | **Indexes to Consider Removing** [List any redundant or unused indexes] --- ### PART 4: QUERY REWRITE **Original Query** ```sql [Original query] ``` **Optimized Query** ```sql -- Optimization applied: -- 1. [Change 1 description] -- 2. [Change 2 description] -- 3. [Change 3 description] [Optimized query with comments] ``` **Explanation of Changes** *Change 1: [Title]* - Before: [What it was] - After: [What it is now] - Why: [Performance benefit] - Expected improvement: [Percentage] *Change 2: [Title]* [Same structure] --- ### PART 5: JOIN OPTIMIZATION **Current Join Strategy** [Explain current join order and types] **Recommended Join Strategy** ```sql -- Join order optimized for: -- 1. Smallest result sets first -- 2. Indexes on join columns -- 3. Appropriate join types [Rewritten joins] ``` **Join Hints (if applicable)** ```sql -- Force specific join method (use sparingly) /*+ HASH_JOIN(t1, t2) */ ``` --- ### PART 6: SUBQUERY OPTIMIZATION **Subquery Analysis** | Subquery | Type | Correlation | Recommendation | |----------|------|-------------|----------------| | [Location] | [Type] | [Yes/No] | [CTE / JOIN / EXISTS] | **Rewritten Subqueries** ```sql -- Original correlated subquery (executed once per row) SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id); -- Optimized with JOIN (executed once) WITH user_averages AS ( SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id ) SELECT o.* FROM orders o JOIN user_averages ua ON o.user_id = ua.user_id WHERE o.total > ua.avg_total; ``` --- ### PART 7: PAGINATION OPTIMIZATION **If using OFFSET (problematic at scale):** ```sql -- Slow for large offsets SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 10000; -- Fast keyset pagination SELECT * FROM items WHERE id > :last_seen_id ORDER BY id LIMIT 20; ``` **Cursor-Based Pagination Implementation** [Provide implementation for your specific query] --- ### PART 8: CACHING STRATEGY **Query Caching Recommendations** - Cache-ability: [HIGH / MEDIUM / LOW] - Suggested TTL: [Duration] - Invalidation triggers: [When to invalidate] **Materialized View Option** ```sql -- If query is expensive and data can be slightly stale CREATE MATERIALIZED VIEW mv_[name] AS [Your optimized query]; -- Refresh strategy REFRESH MATERIALIZED VIEW CONCURRENTLY mv_[name]; ``` --- ### PART 9: PERFORMANCE BENCHMARKS **Before Optimization** | Metric | Value | |--------|-------| | Execution Time | [ms] | | Rows Scanned | [number] | | Buffer Hits | [number] | | Disk Reads | [number] | **After Optimization (Expected)** | Metric | Before | After | Improvement | |--------|--------|-------|-------------| | Execution Time | [ms] | [ms] | [X%] | | Rows Scanned | [n] | [n] | [X%] | --- ### PART 10: IMPLEMENTATION CHECKLIST - [ ] Review index recommendations - [ ] Test optimized query in staging - [ ] Compare EXPLAIN ANALYZE before/after - [ ] Create indexes with CONCURRENTLY - [ ] Monitor query performance after deploy - [ ] Set up alerting for query degradation - [ ] Document changes in runbook

Database Query Optimizer Analysis

Prompt Content

Usage

References