sql - 在 PostgreSQL 中懒惰地使用函数的结果？

Question

我的数据库中有一个函数返回很多行:

CREATE FUNCTION lots_of_rows(n integer) RETURNS SETOF integer
STABLE LANGUAGE plpgsql AS $$ BEGIN
  FOR i IN 1..10000000 LOOP
    RETURN NEXT i * n;
  END LOOP;
END $$;

不出所料，使用这个函数的查询速度不是很快:

=# EXPLAIN ANALYZE SELECT n FROM lots_of_rows(4) as n;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Function Scan on lots_of_rows n  (cost=0.25..10.25 rows=1000 width=4) (actual time=1867.135..2900.167 rows=10000000 loops=1)
 Planning Time: 0.026 ms
 Execution Time: 3494.365 ms
(3 rows)

这是意料之中的。但让我感到沮丧的是，即使我只使用结果行的一小部分，我也要支付这个函数的全部成本:

=# EXPLAIN ANALYZE SELECT n FROM lots_of_rows(4) as n LIMIT 10;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..0.35 rows=10 width=4) (actual time=1863.679..1863.682 rows=10 loops=1)
   ->  Function Scan on lots_of_rows n  (cost=0.25..10.25 rows=1000 width=4) (actual time=1863.675..1863.676 rows=10 loops=1)
 Planning Time: 0.044 ms
 Execution Time: 1872.395 ms
(4 rows)

显然，这是非常浪费的。相比之下，如果我用递归 View 做同样的事情，它基本上需要零时间:

CREATE RECURSIVE VIEW lots_of_rows (n) AS
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM lots_of_rows WHERE n < 10000000;

=# EXPLAIN ANALYZE SELECT n * 4 FROM lots_of_rows LIMIT 10;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.95..3.28 rows=10 width=4) (actual time=0.005..0.027 rows=10 loops=1)
   ->  Subquery Scan on lots_of_rows  (cost=2.95..3.96 rows=31 width=4) (actual time=0.005..0.023 rows=10 loops=1)
         ->  CTE Scan on lots_of_rows lots_of_rows_1  (cost=2.95..3.57 rows=31 width=4) (actual time=0.003..0.020 rows=10 loops=1)
               CTE lots_of_rows
                 ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.002..0.015 rows=10 loops=1)
                       ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                       ->  WorkTable Scan on lots_of_rows lots_of_rows_2  (cost=0.00..0.23 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=9)
                             Filter: (n < 10000000)
 Planning Time: 0.213 ms
 Execution Time: 0.089 ms
(10 rows)

当然，我的函数接受一个参数，n，但是 View 不能接受参数，所以一些实现细节必须泄露到我的个人查询中。

当然，这个 lots_of_rows 函数非常愚蠢，我实际上并没有在任何地方真正使用它。我的实际功能更复杂:它接受几个不同的参数并使用它们构造一个 SELECT 查询，使用 FOR 遍历结果，并且对于某些行，使用返回记录返回下一个。用 View 替换该特定功能远没有那么简单。

此外，将限制逻辑从我的封闭查询移动到函数中并不简单，因为封闭查询有时会向结果添加各种 WHERE 条件:

SELECT r.id FROM complicated_function($1, $2, $3, $4) as r
WHERE r.is_public AND r.score > 0 LIMIT 20;

我想我总是可以为我需要的所有不同条件向函数添加大量不同的参数，但理想情况下，我希望喜欢能够保持我的函数原样(因为它精确地封装了我想要的抽象)，只是以某种方式按需将结果流式传输给调用者，以便它的行为更像一个 View (尽管对查询规划器来说或多或少是不透明的)。这完全可能吗，或者必须一个函数的结果在返回之前完全在内存中具体化？

Answer 1

我相信您可以通过让函数返回 cursor 来实现您正在寻找的目标。 .

游标 应该允许函数调用者分批获取行而不是一次获取所有行，从而允许在客户端和服务器上同时更快地向调用者提供结果并减少内存.

注意:服务器在维护游标方面存在开销。调用者应在完成后显式关闭游标(否则它将在事务结束时关闭)。

请特别注意上面链接中标题为 43.7.3.5 的部分。返回游标。