具有许多常用表表达式的 Postgresql 视图很慢

Question

这是我查询的巨大简化，但本质上我有一系列相互构建的通用表表达式，我想将其转换为视图。问题是当我尝试使用视图时它非常慢，但在运行查询时非常快。

CREATE VIEW user_view AS
WITH cte AS(
SELECT first,middle,last FROM user
),
cte2 AS(
SELECT *,first + middle AS first_middle FROM cte
),
cte3 AS(
SELECT *,first_middle + last AS full_name FROM cte2
)
SELECT * from cte3;

快速查询

WITH cte AS(
SELECT first,middle,last FROM user WHERE user_id = 5
),
cte2 AS(
SELECT *,first + middle AS first_middle FROM cte
),
cte3 AS(
SELECT *,first_middle + last AS full_name FROM cte2
)
SELECT * from cte3;

使用视图的慢查询

SELECT * from user_view WHERE user_id = 5

Answer 1

Postgres为CTE实现了所谓的"优化围栏"。 这意味着 Postgres 会具体化每个 CTE 以进行后续处理。 一个很好的效果是可以多次引用 CTE，但代码只执行一次。 缺点是，在 CTE 实现后，索引等便利性被"遗忘"。

对于你的问题，这个观点实际上是无关紧要的(没有双关语(。 在此版本中：

WITH cte AS (
SELECT first, middle, last FROM user WHERE user_id = 5
),
cte2 AS (
SELECT *, first || middle AS first_middle FROM cte
),
cte3 AS (
SELECT *, first_middle || last AS full_name FROM cte2
)
SELECT * 
FROM cte3;

第一个 CTE 大概从表中提取了一条记录。 据推测，它在 id 上使用索引，甚至该操作也非常快。 该记录是其余 CTE 处理的唯一记录。

在此版本中：

WITH cte AS (
SELECT first, middle, last FROM user 
),
cte2 AS (
SELECT *, first || middle AS first_middle FROM cte
),
cte3 AS (
SELECT *, first_middle || last AS full_name FROM cte2
)
SELECT * 
FROM cte3
WHERE user_id = 5;

CTE 正在处理user表中的所有数据。 最后，需要找到满足WHERE条件的行。 具体化的 CTE 不再有索引。 .因此，数据是按顺序搜索的。

此行为不适用于子查询，因此您可以尝试使用子查询而不是 CTE 重写逻辑。

Postgres 优化 CTE 的方式与其他数据库不同。 例如，SQL Server从不具体化子查询;代码始终"插入"到查询中并作为一个整体进行优化。 事实上，SQL Server 论坛有相反的关注点 - 实现实现 CTE 的选项。 与其他数据库不同。 Oracle是一个似乎采用这两种方法的数据库。