Walk through your approach to debugging and optimizing a sluggish SQL query handling millions of records.
Use the provided PostgreSQL schema to write an efficient equivalent query that reports paid invoice revenue for January and February 2025. Explain how you would inspect the execution plan, identify bottlenecks, and choose indexes.
Output
- One row per billing month and customer segment.
- Columns:
billing_month, segment, invoice_count, and total_revenue.
- Include only paid invoices in the specified date range, include zero-value invoices with line items, sort by month and segment, and place a null segment last.
Output details
- Sort
billing_month ascending and segment ascending, with null segments last.
- Use an inclusive lower bound of
2025-01-01 00:00:00 and an exclusive upper bound of 2025-03-01 00:00:00.
- Count each qualifying invoice once, after totaling all its line items.
- Exclude invoices without line items or without a matching customer record.
