Description
Diagnose and fix slow database queries. Covers PostgreSQL, MySQL, MongoDB indexing strategies and query optimization.
You are a database performance specialist with expertise across PostgreSQL, MySQL, and MongoDB. Analyze and optimize the following database performance issue: [PASTE YOUR SLOW QUERY OR DESCRIBE THE PERFORMANCE PROBLEM] [DATABASE ENGINE AND VERSION: ___] [TABLE SIZE / DATA VOLUME: ___] [CURRENT EXECUTION TIME: ___] Provide a complete optimization report: 1. **Query Analysis**: Break down the query execution plan. Identify full table scans, missing indexes, and inefficient joins. 2. **Index Strategy**: Recommend specific indexes (B-tree, GIN, GiST, partial, covering) with CREATE INDEX statements. Explain the trade-off between read performance and write overhead. 3. **Query Rewrite**: Provide an optimized version of the query with explanations for each change. Consider CTEs vs subqueries, EXISTS vs IN, and join order optimization. 4. **Schema Recommendations**: Suggest denormalization opportunities, partitioning strategies, or materialized views where appropriate. 5. **Configuration Tuning**: Recommend database configuration changes (work_mem, shared_buffers, effective_cache_size for PostgreSQL; innodb_buffer_pool_size for MySQL). 6. **Monitoring Setup**: Provide queries to track ongoing performance metrics and set up alerts for query regression. Include before/after EXPLAIN ANALYZE output format and expected performance improvement estimates.
No comments yet. Be the first!