Optimize Dashboard Query Performance

MediumSQL · PostgreSQL

Problem

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.

Schema

tasks

Column	Type	Description
task_idPK	INT	Unique task identifier
tasker_id	INT	Tasker assigned to the task
category_id	INT	Task category
city	VARCHAR(80)	Task location city
status	VARCHAR(30)	Task lifecycle status
completed_at	DATE	Date the task was completed
price	NUMERIC(10,2)	Task price in dollars

taskers

Column	Type	Description
tasker_idPK	INT	Unique tasker identifier
display_name	VARCHAR(120)	Tasker display name
is_active	BOOLEAN	Whether the tasker is currently active
signup_date	DATE	Tasker signup date

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(100)	Task category name

Column	Type	Description
review_idPK	INT	Unique review identifier
task_id	INT	Reviewed task
rating	INT	Rating from one to five