本文是有关PostgreSQL 12中新功能的故事的延续。我们已经在文章
“他们在功能冻结2019中冻结了什么。第一部分，JSONPath”中分析了SQL / JSON（JSONPath补丁），现在轮到CTE了。
CTE
CTE是公用表表达式-公用表表达式，它们也称为WITH构造。 实际上，这是创建临时表，但仅用于一个查询，而不用于会话。 可以在此请求中访问它们。 这样的请求很容易理解，可以理解，必要时很容易修改。 这是非常流行的事情，并且在PostgreSQL中已经存在了很长时间。
但是便利设施可能很昂贵。 问题与WITH ... AS（）构造内的AS之后的表达式的实现有关。 它也称为内部表达式，是在开始计算余数之前计算的；它不能嵌入顶级查询（无内联）。 规划此表达式不会考虑其余的查询。 这种行为称为优化或隔离的障碍。 另外，实现本身需要work_mem。 如果样本很大，那么问题就开始出现（例如，Ivan Frolkov在PGConf 2019上发表了
一份报告 ）。
我们将在下面讨论的优化程序的“捉迷藏”并不是一般的错误，而是一个功能。 当然，在某些情况下，对表达式的一部分进行初步计算会消除递归查询中不必要的重复操作。 另一方面，许多开发人员将CTE用作视图而没有考虑障碍，结果，CTE的请求不仅比带有子查询的等效（但更复杂）请求执行得更慢，而且执行速度也要慢几个数量级。 在权衡利弊之后，社区采取了决定性的步骤：它更改了默认行为。
我们将在这样的平台上观察CTE的工作：
CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);
Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)
让我们从一个简单的请求开始：
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
一切都被即时考虑，仅使用索引。
带有子查询的查询的计算结果相同，但是语法稍微复杂一些：
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
一切都井井有条，索引计算非常快。
现在还有一个逻辑上等效的请求，但带有CTE：
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
肉眼已经可以看到这样的延迟。 您不会喝咖啡，但是有足够的时间来查看邮件（当我们使用第11版或更早版本时）。
这就是发生的情况：在子查询的情况下，优化器立即意识到，条件x = 2和y> 1可以组合为一个过滤器并通过索引进行搜索。 对于CTE，优化器别无选择：它必须首先处理WITH ... AS构造中的条件，具体化结果，然后再进行处理。
这里的重点并不是实现将需要资源：如果条件y <3，则不必实现数百万条记录，而仅需要2条。简单查询的巨大时间用于顺序搜索，优化器无法使用索引搜索，因为因此复合索引建立在x上，然后才建立在y上，直到满足内部CTE条件，他对条件x = 2的查询一无所知。 它超越了障碍。
因此，在PostgreSQL 12之前，默认设置是实现，现在已不存在。 我们基于新版本启动相同的请求。 就像障碍一样，优化器会立即看到整个请求：
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)
优化程序立即学会了以最佳顺序组合条件-子查询就是这种情况。
但是默认值是默认值，并且为了完全掌握当前情况，在版本12中，CTE的实现是受控的，受控的：
WITH cte_name AS [NOT] MATERIALIZED
让我们实现：
EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)
一切都与11中的相同，在此之前，您可以在查询结果的备用模式下查看邮件。 我们禁止实现，消除障碍：
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
同样，没有喘息的机会：它立即生效。
剩余的细微差别。 但重要的细微差别。
如果多次访问CTE，则默认情况下会实现。
乍一看，在这种情况下实现是一个合理的决定：为什么要计算两次相同的事物。 在实践中，这通常会导致我们上面观察到的情况。 要强制拒绝实现，必须明确订购优化程序：NOT MATERIALIZED。
我们在没有NOT MATERIALIZED请求的情况下执行两次WHERE：
WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)
现在，我们将明确禁止物化：
WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)
编写CTE始终执行，从未引用的CTE永远不会执行。
从计划中可以明显看出：not_exected不在其中。 对于以前的版本，这是正确的，但值得记住的是，（NOT）MATERIALIZED构造适用于版本12中的可执行表达式。
EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;
QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)
还有一条规则：
WITH的递归查询始终实现。
始终如此，而不是默认情况下。 如果我们订购优化器：NOT MATERIALIZED，将没有错误，并且实现仍然存在。 这是一个社区意识的决定。
我们将以家庭作业为例进行说明。 今天就这些了。
专门针对CTE新内容的部分评估使用了报告“ Etudes中的Postgres 12”中的示例和片段，Oleg Bartunov于今年4月9日在圣彼得堡的Saint Highload ++上阅读了该报告。在下一个系列中
-KNN 。