sql - Postgres 在限制甚至发生之前限制行

Question

背景故事:

我正在处理一个有很多左连接的查询(旧项目上的原则连接继承)。

查询是针对 9.4 postgres 运行的，它的结构(已简化)如下所示:

SELECT *
FROM table1 a0_ 
LEFT JOIN table2 a1_ ON a0_.id = a1_.id 
LEFT JOIN table3 a2_ ON a0_.id = a2_.id 
WHERE a0_.created_at <= '2019-11-25 15:09:33' LIMIT 5

此查询因 limit(在原始巨大查询上)而变慢，因为根据我的理解，它在执行 limit 之前首先连接所有表。我已经找到了一种解决此问题的方法，方法是在充当源表的子查询中移动 where 和 limit，从而减少原始数据池(将性能提高大约 4)。

遇到的问题:

为了全面了解后面发生的事情，我分析了原始查询，它输出了这个(你也可以得到一个美化 View here ):

Limit  (cost=0.42..24.49 rows=1 width=10536) (actual time=0.129..0.546 rows=5 loops=1)
  ->  Nested Loop Left Join  (cost=0.42..24.49 rows=1 width=10536) (actual time=0.113..0.462 rows=5 loops=1)
        ->  Nested Loop Left Join  (cost=0.27..16.31 rows=1 width=9976) (actual time=0.071..0.285 rows=5 loops=1)
              ->  Index Scan using table1_pkey on table1 a0_  (cost=0.12..8.14 rows=1 width=9428) (actual time=0.022..0.063 rows=5 loops=1)
                    Filter: (created_at <= '2019-11-25 15:09:33'::timestamp without time zone)
              ->  Index Scan using table2_pkey on table2 a1_  (cost=0.14..8.16 rows=1 width=548) (actual time=0.012..0.015 rows=1 loops=5)
                    Index Cond: ((a0_.id)::text = (id)::text)
        ->  Index Scan using table3_pkey on table3 a2_  (cost=0.14..8.16 rows=1 width=560) (actual time=0.010..0.010 rows=0 loops=5)
              Index Cond: ((a0_.id)::text = (id)::text)

在新的优化查询中(在子查询中使用 where 和 limit)，解释显示 limit 是在 where 过滤器之后生成的.

我对这个原始查询的解释不明白的是，尽管 limit 最后被“运行”，但它似乎已经在第一个只输出 5 行的语句运行时处于事件状态(相对于 8没有限制)。

谁能解释一下为什么？

Answer 1

PostgreSQL 的执行器“按需”工作。当执行计划的顶层节点需要输出另一行时，它会向它下面的节点请求更多的行。

这向下传播到较低的节点。当“限制”节点达到其限制时，处理停止，因此较低的节点只需要生成它们可以生成的行的一小部分。

让我逐步解释当执行计划产生第一个结果行时会发生什么:

• 要生成第一个结果行，Limit 节点必须从外部Nested Loop Left Join 获得一个结果。

• 要生成第一行，外部嵌套循环必须从内部Nested Loop Left Join 获取第一个结果。

• 内部嵌套循环连接首先必须从 table1 的索引扫描中获取第一行。然后它从 table2 的索引扫描中获取一个匹配行(如果有的话)(这是第一个 loop)。连接这两行以生成内部 Nested Loop Left Join 的第一个结果行。

• 现在，外部 Nested Loop Left Join 从 table3 的索引扫描中获取第一个匹配行(如果有)。它发现很少(平均 rows=0)。这是第 1 个循环。即使没有找到匹配的行，它也会生成一个结果行，因为这是一个外部联接。

重复上述五次，直到完成 Limit，您将得到执行计划中看到的计数。