The below query runs normally in about 1 minute. I made some changes to it for reasons and suddenly performance was ~90x worse. When I run the query with every CTE instead inserting into a temporary table, performance improves to 1-3 minutes. I would like to learn what specifically has caused this drop in performance (and how it appears to have confused the query planner).
The main problem appears to be the most complex piece: the last CTE aliased
daily, what I've tried:
- turning one of the CTEs,
- turning the
intersection_daysCTE into a sub-query in
- moving elements of the
WHEREclause into the join
- moving 1-1 joins in the
dailyCTE into earlier
The query is pretty big, so here's a gist with both queries and their respective query plans (note: the above attempts to optimize don't necessarily appear in the "Updated query"). Sorry this question is somewhat a code review, happy to edit it with more meaningful details.