这个查询会把整个表加载到内存中吗？

Question

如果我的表非常大，那么这个查询在过滤结果之前是否会将整个表加载到内存中：

with parent as
(
    select * from a101
)
select * from parent 
where value1 = 159

正如您所看到的，父查询引用了整个表。这会在内存中加载吗？这只是查询的一个非常简化的版本。真正的查询涉及到其他几个表的连接。我正在评估SQL Server 2012和PostgreSQL。

- Luke101

---

如果这是针对SQL Server、PostgreSQL还是两者都适用的？如果只是针对PostgreSQL，请移除SQL Server标签。 - Nick.McDermaid

Answer 1

在PostgreSQL中（至少在9.4版本中）通用表达式（CTEs）作为“优化栅栏”。

查询优化器不会将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，随着TupleStore变得更大，它会透明地溢出到磁盘上的tempfile中。

最初的正当理由是计划（后来实现）CTE术语中的DML。如果CTE术语中有DML，则其执行非常重要且必须完整。如果CTE调用数据修改函数，这也可能是正确的。

不幸的是，似乎没有人想过“...但如果它只是一个SELECT，我们想要将其内联呢？”

社区中的许多人似乎将此视为一种特性，并经常将其作为优化器问题的解决方法进行传播。我发现这种态度非常令人困惑。因此，以后修复这个问题将非常困难，因为人们有意使用CTE来防止优化器改变查询。

换句话说，PostgreSQL滥用CTE作为伪查询提示（连同OFFSET 0 hack一起），因为项目政策表示不需要或不支持真正的查询提示。

据我所知，MS SQL Server可以优化CTE屏障，但也可以选择将结果集材料化。

- Craig Ringer

Answer 2

我刚在PostgreSQL中针对这个查询做了EXPLAIN，令人惊讶的是它使用序列扫描而不是索引查找：

 CTE Scan on parent  (cost=123.30..132.97 rows=2 width=1711)
   Filter: (value1 = 159)
   CTE parent
     ->  Seq Scan on a101  (cost=0.00..123.30 rows=430 width=2060)

我在value1上建立了一个主键索引，这个索引被用于简单的select * from a101 where value1 = 159查询。

所以，答案是它将扫描整个表。我很惊讶，我以为它会像视图或子查询一样工作，但实际上并不是这样。你可以使用以下方法来使用索引：

select * from (select * from a101) parent 
where value1 = 159`

- Suor

Answer 3

查询将作为整体进行评估。如果您查看执行计划，您将看到过滤谓词将应用于内部搜索。鉴于外部搜索的琐碎性，我相信它会被优化掉。

检查执行计划--这是基础知识，您最好快速学会如何做到这一点。一旦遇到真正的性能问题，您将需要找出问题所在，而执行计划就是解决问题的关键。

- TomTom

---

在CTE的情况下，PostgreSQL没有遵循这一点（不幸的是）。 - Craig Ringer

Answer 4

CTE只是语言语法，使代码更易读，对查询执行性能没有影响。

当查询被执行时，它将按照预定义的SQL Server查询执行阶段执行。

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

因此，WHERE 过滤器将首先应用，然后选择记录，因此完整的表不会在内存中获取。

- Mudassir Hasan

---

这个订单有微软的链接吗？谢谢。 - Erran Morad

在SQL Server的情况下，这是不正确的。例如，WHERE子句中的特定谓词可能会被推送到索引查找中，这意味着您提出的顺序是不正确的。它按照查询计划告诉它的任何顺序执行操作。在极端情况下，它可能会根据where子句进行查找，进行排序，然后分组，然后合并连接等操作。内存中的数据量是可用内存，缓存等的函数。如果没有任何空闲物理内存，则不管查询如何，都不会将其加载到内存中，而是将其发送到页面文件中。 - Nick.McDermaid

在PostgreSQL中，这也是完全错误的。 - Craig Ringer