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, int_avg into a MATERIALIZED VIEW
  • turning the intersection_days CTE into a sub-query in daily
  • moving elements of the daily WHERE clause into the join ON statements
  • moving 1-1 joins in the daily CTE into earlier CTEs

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.