With the move to Postgres 12, one of my queries went from 6 seconds to 7.5 minutes. I was able to fix this by adding MATERIALIZED to the CTE expressions. What I don't understand is why the new NOT MATERIALIZED default is so much worse when it's supposed to improve things. My table has a zillion columns and the SQL is 230 lines, so here's a super slimmed down version of the concept.
6 second query
WITH A as MATERIALIZED (SELECT * from foo where <complicated stuff>),
B as MATERIALIZED (SELECT * from foo where <other complicated stuff>)
SELECT * from A JOIN B ON <complicated stuff>
order by a_thing
That query will do both A and B relatively quickly, the bulk of the time in the final merge sort.
If I remove the MATERIALIZED, it takes 7.5 minutes and the explain analyze says:
- A goes fairly quickly still
- Loops over every single A result as it generates B. Each loop is fast, but there are tens of thousands of them.
So, my question is, what factors are bad for NOT MATERIALIZED CTE's? I'm looking for concepts/ideas so I can dig into my SQL and see if there's a way to make NOT MATERIALIZED work better for me.
I understand this is a tough one without the SQL, but it's about 230 lines long. If it's felt this question isn't answerable, we can delete it.
Explain links for with and without MATERIALIZED. Not sure how long explain.depesz.com keeps these around though.
- NOT MATERIALIZED: https://explain.depesz.com/s/C4HY
- MATERIALIZED: https://explain.depesz.com/s/y6TO