Nicolas Seinlet <nico...@seinlet.com> writes:
> I'm trying to understand this behaviour and the limits of CTE, when they
> reach the limits, when they cannot receive parameters from a caller, ... I'm
> running a query on PostgreSQL 10 with a cte. the query runs in ~ 10 seconds.
> If I add a second 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;
[ shrug... ] You are asking for two different computations, and the
second one is far more expensive.
In the first case, the WHERE x=32 clause is applied before the window
function, so we can (indeed must) filter out all rows not having x=32
before doing the window function.
In the second case, WHERE x=32 is applied above/after the window
function. We cannot push down the WHERE to before the window function.
(In this case, filtering beforehand would obviously change the results
of row_number, but in general we don't know enough about window function
behavior to risk such changes.) So row_number has to be computed over
the entire contents of the "table", and that's not cheap.
It does surprise me a bit that row_number is quite *that* expensive,
but if you are expecting equivalent results from these two queries,
you're simply wrong.
regards, tom lane
