6

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

cis
  • 459
  • 5
  • 16
  • Have you tested 11.6 in a VM of the same configuration? Maybe your baseline is off! – Eugen Rieck Jan 13 '20 at 11:12
  • @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? – cis Jan 13 '20 at 11:15
  • Did you run analyze on all tables after you imported the data? –  Jan 13 '20 at 11:24
  • @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. – cis Jan 13 '20 at 11:58
  • Please [edit] your question and add the execution plans generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you prevent the indention of the plan. Paste the text, then put \``` on the line before the plan and on a line after the plan. It would be interesting to see the plans from 11 and 12 –  Jan 13 '20 at 12:01
  • @a_horse_with_no_name Thanks for suggesting that, but I cannot add the results of EXPLAIN ANALYZE as it is way too big (1500 lines) and contains secrets (names of tables, columns, CTEs). From what I have observed when comparing the results of 11 and 12, is what I mention in the question: The lack of index usage in CTEs done as MATERIALIZED. (The docs also mention that.) – cis Jan 13 '20 at 12:09
  • 1
    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! :-). – Vérace Jan 13 '20 at 12:10
  • You can upload them to https://explain.depesz.com/ and opt to anonymize the table names (although I have never understood how a table name can possibly be secret - but that's a different story) –  Jan 13 '20 at 12:12
  • Postgres did always use indexes for CTEs - the major difference between 11 and 12 is that in 12, the query is now optimized as a whole, whereas in 11 the CTEs were optimized separately so, e.g. conditions from the outer query were not pushed down to the CTE query. Here is a simplified example for 11 and the same for 12 –  Jan 13 '20 at 12:19
  • @a_horse_with_no_name Thanks for that link! Added links to both versions. – cis Jan 13 '20 at 14:25

1 Answers1

3

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.

cis
  • 459
  • 5
  • 16