Low performance of Common Table Expressions in PostgreSQL V14-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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;