sql - 具有许多公用表表达式的 Postgresql View 速度很慢

Question

这是对我的查询的巨大简化，但本质上我有一系列相互构建的公用表表达式，我想将它们变成一个 View 。问题是当我尝试使用 View 时速度非常慢，但当我运行查询时速度非常快。

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;

使用 View 查询慢

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 是一种似乎同时采用这两种方法的数据库。