Postgres 12 MATERIALIZED CTE much faster

Question

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

Answer 1

Without having a copy of the table structure/data, it is quite difficult to understand the underlying problems. Still, when looking at the query plans, I see some general issues, that are relevant in both cases (with and without materialized).

Minor Issues

Your work_mem seems to be quite low for such a dataset. This leads to some external sort/merge on disk instead of in memory. Try raising your work_mem in a session and retry the queries. E.g.

SET work_mem TO '1 GB';

The number of sequential scans suggests that your random_page_cost is probably too high. Assuming your data is on SSD, you could try to set random_page_cost to 1.1 (assuming your seq_page_cost is 1):

set random_page_cost =1.1;

Major Issue

The biggest problem seems to be the selectivity of your where clauses (AKA complicated stuff). The difference between the expected number of rows vs the actual number of rows (column rows x on explain.depesz.com) is a clear symptom.

This leads the query planner to make poor strategy choices.

At first, you may try to increase the default_statistics_target:

 SET default_statistics_target = 1000;
 VACUUM ANALYZE;

Next, you should check if your knowledge of the data domain matches the PostgreSQL estimations.

If you have a table called bs and a column called v, you should know what kind of distribution to expect for v. Something like : "v is a unique value for each row." or "There are at most 5 different values for v" or "The number of possible values of v is the number of rows divided by 1000". You can check if your knowledge matches the PostgreSQL estimation by using the following query:

 SELECT n_distinct FROM pg_stats WHERE tablename = 'bs' AND attname = 'v';

You can see the definition of n_distinct here.

If there is a mismatch between your knowledge and the PostgreSQL estimation, you can then fix this mismatch by using the create statistics command.