sql - Will this query load the whole table in memory -

if have big table query load whole table in memory before filters resets:

with parent (     select * a101 ) select * parent  value1 = 159 

as can see parent query reference whole table. loaded in memory. simplified version of query. real query has few joins other tables. evaluating sql server 2012 , postgrsql.

in postgresql (true of 9.4, @ least) ctes act optimisation fences.

the query optimiser not flatten cte terms outer query, push down qualifiers, or pull qualifiers, in trivial cases. unqualified select inside cte term full table scan (or index-only scan if there's suitable index).

thus, in postgresql, these 2 things different indeed, simple explain show:

with parent (     select * a101 ) select * parent  value1 = 159 

and

select *  (    select * a101 ) parent value1 = 159; 

however, "will scan whole table" doesn't mean "will load whole table in memory". postgresql use tuplestore, transparently spill tempfile on disk gets larger.

the original justification dml in cte terms planned (and later implemented). if there's dml in cte term it's vital execution predictable , complete. may true if cte calls data-modifying functions.

unfortunately, nobody seems have thought "... if it's select , want inline it?".

many in community appear see feature , regularly promulgate workaround optimiser issues. find attitude utterly perplexing. result, it's going hard fix later, because people intentionally using ctes when want prevent optimiser altering query.

in other words, postgresql abuses ctes pseudo-query-hints (along offset 0 hack), because project policy says real query hints aren't desired or supported.

afaik ms sql server may optimise cte barriers, may choose materialise result set.