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

ctepostgresql-12postgresql-performanceupgrade

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

Best Answer

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.

Related Solutions

PostgreSQL – WITH Clause Not Executing First

Since public.samples_exp_unit and public.sample_results are the views, you have added a level of indirection in your queries.

You can rewrite to be simpler:

WITH 
  eu AS (TABLE public.samples_exp_unit),
  sr AS (TABLE public.sample_results)
SELECT
  sr.sample_pk,
  eu.inventory_pk,
  .....
FROM sr INNER JOIN eu
  ON sr.sample_pk = eu.sample_pk ;

and test if this changes the plan and improves efficiency.

Another thing to test is to avoid CTEs. Especially because they are an optimization fence, they sometimes result in less efficient plans (and sometimes in more efficient plans!) Rewrite and test without a CTE:

SELECT
  sr.sample_pk,
  eu.inventory_pk,
  .....
FROM public.sample_results AS sr 
  INNER JOIN 
     public.samples_exp_unit AS eu
  ON sr.sample_pk = eu.sample_pk ;

Also try the queries through psql. It will show if PgAdmin is the issue.

It could be that pgAdmin is taking a long time to render the results of the query, or is talking over a slow network and so takes a long time to fetch the results. It is also possible pgAdmin is doing something that changes the execution plan, like silently setting some parameters to non-default values, or preparing the query into a cursor before execution.

Postgresql – Postgres 9.0 vs Postgres 10 & 11 Performance

The default postgresql.conf configuration is just for small footprint database and it will be slow if your database is large and is queried using complicated joins.

I saw from your postgresql10 configuration file that your shared memory is only set to 128MB (also many other settings are very small). You need to reconfigure this.

Tuning the PostgreSQL server is a big topic and different kinds of hardware would also need different settings, which also comes with trial and error while continuously improving the settings/configuration.

I am unable to discuss the whole topie here, I am just able to provide the tuned settings I used to use.

Target

Use no more than 4 GB memory in my server (as my server is not dedicated to run PostgreSQL DB)
Server has > 8 cores

max_connections : 800
shared_buffers : 1536MB
work_mem : 24MB
maintenance_work_mem : 480MB
vacuum_cost_delay : 20ms
synchronous_commit : local
wal_buffers : 8MB
max_wal_size : 1536GB
checkpoint_completion_target : 0.9
effective_cache_size : 4GB
deadlock_timeout : 3s
log_min_duration_statement : 5000
log_error_verbosity : verbose
log_autovacuum_min_duration : 10000
log_lock_waits : on

Related Question