Low performance of Common Table Expressions in PostgreSQL V14

Question

I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds.

The minimum query to reproduce this issue is the following :

  WITH MyTable (id) AS (
    SELECT 1
    UNION ALL SELECT 1
    ... repeat 2000 times
    UNION ALL SELECT 1
   )
  SELECT count(*) from MyTable;

And it takes about 3 seconds, VS 80 ms in PostgreSQL V11.

In the execution plan, I can see that the planning time takes many seconds, but I have no idea why it is so slow comparing to PostgreSQL V11

Here is the execution plan :

Aggregate  (cost=33.71..33.72 rows=1 width=8) (actual time=105.145..140.802 rows=1 loops=1)
  ->  Append  (cost=0.00..28.89 rows=1926 width=0) (actual time=0.024..123.153 rows=1926 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
....
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
Planning Time: 3066.151 ms
Execution Time: 148.261 ms

Do anyone know why this is happening, and if there is a way to fix that ?

Edit: using the MATERIALIZED keyword helps and reduces the time from 3 seconds to 1 second. Which is still far from the 80 ms in PostgreSQL V11. With MATERIALIZED, the execution plan becomes :

Aggregate  (cost=72.23..72.24 rows=1 width=8) (actual time=137.779..171.381 rows=1 loops=1)
  CTE mytable
    ->  Append  (cost=0.00..28.89 rows=1926 width=4) (actual time=0.025..119.213 rows=1926 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.009..0.024 rows=1 loops=1)
          ...
          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.008..0.023 rows=1 loops=1)
  ->  CTE Scan on mytable  (cost=0.00..38.52 rows=1926 width=0) (actual time=0.042..120.818 rows=1926 loops=1)
Planning Time: 1123.003 ms
Execution Time: 181.167 ms

3 seconds for planning seems excessive. But your CTE seems completely unnecessary. Why don't you just use `select 1 from generate_series(1,2000)`? instead? — , Oct 06 '22 at 16:19
@a_horse_with_no_name, this is just a dummy example to reproduce the slowness of a big CTE. In my real query, I have a real list of rows :) — Drico, Oct 06 '22 at 16:42

score 2 · Answer 1 · answered Oct 06 '22 at 16:45

2

In older releases the inner query was only materialized without any other optimizations. PostgreSQL 12 and higher try to do full optimization of CTE. You can try to use MATERIALIZE clause to force old behave.

WITH MyTable (id) AS MATERIALIZED (
    SELECT 1
    UNION ALL SELECT 1
    ... repeat 2000 times
    UNION ALL SELECT 1
   )
  SELECT count(*) from MyTable;

answered Oct 06 '22 at 16:45

Pavel Stehule

42,331
5
91
94

Thank you a lot, this reduces the time from 3 seconds to 1 second, which is a very good thing. But it is still far from the 80 ms of PostgreSQL V11. – Drico Oct 06 '22 at 16:52
@Drico - you can try to run `VACUUM ANALYZE` over database. But I don't expect miracles. PostgreSQL planner is more complex in every release, and usually little bit slower. The planning over long queries can be too slow. – Pavel Stehule Oct 06 '22 at 17:15

jjanes · Accepted Answer · 2022-10-06T19:29:37.950

In my hands, the materialized one only takes 250ms, so I don't know why you find it taking 1s. My hardware is far from spectacular.

In any event, in the newer versions PostgreSQL puts more effort into trying to optimize the query, and that takes more time. Not every improvement to the software comes for free, trade-offs have to be made.

You could try to rewrite your silly query into a slightly less-silly silly query:

explain (analyze)   WITH MyTable (id) AS (
    SELECT * from (values
     (1),
     (1),
     -- 2000 more
     (1)
    ) foo
   ) 
  SELECT count(*) from MyTable;

In my hands, this is far faster in v14 than what you are currently doing is even under v11.

You can make it cleaner yet by getting rid if the select:

explain (analyze)   WITH MyTable (id) AS (
    values 
     (1),
     (1),
     --
     (1)
     )
  SELECT count(*) from MyTable;

Low performance of Common Table Expressions in PostgreSQL V14

2 Answers2