Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking:
One important query which takes 100ms on version 11 (same VM) now takes about 36s on Postgres 12. That's more than 300 times slower.

My suspicion is, that the new handling of CTEs, namely the MATERIALIZED, NOT MATERIALIZED thing is responsible for that.

If I change every CTE to MATERIALIZED, the query goes down from 36s to 6s. Significantly better, but still more than 50 times slower than in version 11.

If I get it right, in PostgreSQL 12 you have two alternative options:


with MATERIALIZED the CTE is just executed once but you lose the benefit of indexes
with NOT MATERIALIZED you get the benefit of indexes, but your CTE gets executed each time its results are accessed.


Is that correct?

Is there any trick, e.g. a special setting to go back to the Postgres 11 behavior?
Or is the only way to handle this manually evaluating each and every CTE if MATERIALIZED or NOT MATERIALIZED is better?

Quite often, I guess, it is not clear which way is better. My application contains hundreds of CTEs, many of which do both table queries and expensive function calls (the example in the docs where they say that NOT MATERIALIZED is better).

Edit:
What I have checked for making results comparable:


Same Virtual Machine
Same and Very small dataset
Same postgresql.conf
Re-indexed
vacuum analyze


Results of EXPLAIN ANALYZE:
Postgres 11
Postgres 12
As pointed out in the answer to my other, more specific question, the reason is Just-in-time compilation.

SET jit = false; solves all performance problems in my case.