Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

Question

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

• Eugen Rieck: Have you tested 11.6 in a VM of the same configuration? Maybe your baseline is off!
• cis: @Eugen Rieck Yes, the VM is the same and the postgresql.conf is also the same, the data is the same and I have reindexed the DB in question. Do you recommend any general performance test in order to calibrate?
• user1822: Did you run analyze on all tables after you imported the data?
• cis: @a_horse_with_no_name Yes, id did. No noticeable effect. There is not much data in the database anyway, just a tiny test data set.
• Vérace: See my answer here on the side effects of CTE materialisation - particularly the last paragraph - The only small problem that I see is that testing will be required to see if NOT MATERIALIZED is an improvement or not? I can see circumstances where the balance will swing between the two depending on table size, fields selected and indexes on the fields and tables used in the CTE - in other words, there's no substitute for knowledge and experience. The DBA isn't dead and gone yet! :-).

Answer 1

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.