Re: Default Inline CTE makes JOIN slower - Mailing list pgsql-bugs

Hi,

I have this example query in my code

create or replace function slow_int() returns integer language sql stable 

as $$

select pg_sleep(1);

select 1;

$$;

explain analyze WITH s AS (

SELECT * FROM (values (1), (2), (3)) s(a)

),

slow_int AS (

SELECT slow_int()

)

SELECT * FROM s

JOIN slow_int ON true;

and I got this explain analyze output which execute slow_int() three times, you can see the execution time is 3s, not 1s.

Values Scan on "*VALUES*"  (cost=0.00..0.79 rows=3 width=8) (actual time=1002.468..3005.399 rows=3 loops=1)
Planning Time: 0.326 ms
Execution Time: 3005.512 ms

It happens because PG12 automatically inline CTE, and if I force it to be materialized, I can get previous execution time.