You're tasked with optimizing a critical SQL query that processes petabytes of data daily, impacting real-time dashboards. Describe your systematic approach to identify performance bottlenecks, and the specific SQL and database-level optimizations you would implement to achieve significant latency reduction.

Employ a MECE framework: 1. Define Scope & Metrics: Establish baseline latency, identify affected dashboards, and define target latency reduction. 2. Analyze Execution Plan: Use EXPLAIN ANALYZE to pinpoint costly operations (full table scans, complex joins, sorting). 3. Identify Bottlenecks: Correlate execution plan findings with database logs (slow query logs, resource utilization) to isolate CPU, I/O, or memory constraints. 4. Formulate Hypotheses: Based on bottlenecks, propose specific SQL/DB optimizations. 5. Implement & Test: Apply changes incrementally, re-run EXPLAIN ANALYZE, and measure latency against baseline. 6. Monitor & Iterate: Continuously monitor performance post-deployment and refine as needed.

My systematic approach to optimizing a critical SQL query processing petabytes of data daily follows a structured MECE framework. First, I'd Define Scope & Metrics, establishing a baseline latency, identifying all dependent real-time dashboards, and setting a clear target for latency reduction. Next, I'd Analyze the Query Execution Plan using EXPLAIN ANALYZE or similar database-specific tools to precisely pinpoint costly operations like full table scans, complex nested loops, or excessive sorting. Concurrently, I'd Identify Bottlenecks by correlating execution plan insights with database performance metrics (CPU, I/O, memory utilization, lock contention) and slow query logs to determine if the issue is compute, storage, or network-bound.

Based on these findings, I'd Formulate Hypotheses for specific SQL and database-level optimizations. SQL optimizations would include rewriting complex subqueries into Common Table Expressions (CTEs), optimizing JOIN conditions, using WHERE clauses to filter early, and leveraging window functions efficiently. Database-level optimizations would involve creating appropriate composite and covering indexes, partitioning large tables, considering materialized views for pre-aggregation, and potentially adjusting database configuration parameters like buffer cache size or parallelism settings. Finally, I'd Implement & Test changes incrementally, re-evaluating the execution plan and measuring latency against the baseline, followed by continuous Monitoring & Iteration to ensure sustained performance.