We have a query that powers a critical TaskRabbit dashboard but is timing out; walk me through the steps you would take to optimize its performance.
Use the supplied schema as a representative dashboard workload. Provide an optimized PostgreSQL query and explain how you would validate that it is faster while preserving the dashboard results.
Output
- One row per qualifying task category, with columns
category_name, completed_tasks, gross_revenue, avg_rating, and category_rank.
- Include completed Brooklyn tasks from January 1 through January 31, 2025, handled by active taskers.
- Sort by
category_rank, then category_name ascending.
Output details
- Treat
2025-01-01 as an inclusive lower bound and 2025-02-01 as an exclusive upper bound.
- Round
avg_rating to two decimal places and return gross_revenue with two decimal places.
- Set
gross_revenue to 0.00 when its summed value is NULL, and return avg_rating as NULL when no non-NULL ratings exist.
- Compute
category_rank by descending gross_revenue using competition ranking, so tied revenues share a rank and subsequent ranks skip values.
