如果我有一个非常大的表,这个查询会在过滤重置之前将整个表加载到内存中：
with parent as
(
select * from a101
)
select * from parent
where value1 = 159
如您所见,父查询引用整个表.这会加载到内存中.这是查询的一个非常简化的版本.真正的查询与其他表有一些连接.我正在评估sql server 2012和postgrsql.
解决方法
在Postgresql中(至少为9.4),CTEs act as optimisation fences.
查询优化器不会将CTE术语展平为外部查询,下推限定符或提取限定符,即使在普通情况下也是如此.因此,CTE术语中的非限定SELECT将始终执行全表扫描(或者如果存在合适的索引则执行仅索引扫描).
因此,在Postgresql中,这两件事确实非常不同,正如一个简单的EXPLAIN所示：
with parent as
(
select * from a101
)
select * from parent
where value1 = 159
和
SELECT * FROM ( SELECT * FROM a101 ) AS parent WHERE value1 = 159;
但是,“将扫描整个表”并不一定意味着“将整个表加载到内存中”. Postgresql将使用TupleStore,当它变大时,它将透明地溢出到磁盘上的临时文件.
最初的理由是计划(后来实施)CTE术语中的DML.如果在CTE术语中存在DML,那么其执行是可预测和完整的至关重要.如果CTE调用数据修改函数,这也可能是真的.
不幸的是,似乎没有人想过“……但如果它只是一个SELECT并且我们想要内联它呢？”.
社区中的许多人似乎将此视为一项功能,并定期将其作为优化问题的解决方案.我觉得这种态度完全令人困惑.因此,以后很难解决这个问题,因为人们在想要阻止优化器更改查询时故意使用CTE.
换句话说,Postgresql滥用CTE作为伪查询提示(以及OFFSET 0 hack),因为项目策略说不需要或不支持真正的查询提示.
AFAIK MS sql Server可以优化CTE障碍,但也可以选择实现结果集.
