Hello,
I'm trying to understand this behaviour and the limits of CTE, when they reach the limits, when they cannot receive
parametersfrom a caller, ... I'm running a query on PostgreSQL 10 with a cte. the query runs in ~ 10 seconds. If I add
asecond CTE with the same query as the previous one and select * from second_cte as query, it now runs in ~ 10 minutes.
oversimplified example:
10 seconds version:
| WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z FROM cte1 WHERE x=32;
10 minutes version:
| WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number() over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE
x=32;
The real examples, with query plans:
https://explain.dalibo.com/plan/98A
https://explain.dalibo.com/plan/o6X4
Thanks for your time,
Nicolas Seinlet.