postgresql - 游标和提取在 PostgreSQL 中如何工作

Question

我想知道 CURSOR 和 FETCH 在 PostgreSQL 中是如何工作的。

一开始我以为

1. 当用select语句声明CURSOR时，DB会执行select语句，然后将结果存入DB内存。

2. 当在 CURSOR 上调用 FETCH 时，DB 将只读取在 CURSOR 上移动的结果。

3. 当 CURSOR 关闭时，存储的结果将从内存中移除。

如果我的假设是正确的，无论 select 语句多么复杂，FETCH 都应该有较短的响应时间。

但是，当我测试时，FETCH 显示的响应时间比我预期的要差，就像它做了一些我没有预料到的事情。

它们是如何工作的？

------------ 编辑------------

以下是我使用实际数据库表进行测试时得到的结果。(select 语句包含 3 个表的 join 子句，其中一个表有 300 万行)

(   8sec) DECLARE “123" NO SCROLL CURSOR WITH HOLD FOR SELECT .....
(0.04sec) FETCH FORWARD 2 FROM "123";
(   4sec) FETCH FORWARD 10000 FROM "123";

------------ 编辑------------

FETCH FORWARD 10000 FROM "123" 中的 4 秒响应时间似乎是因为我使用了 pgcli(PostgreSQL 客户端工具)。

我不知道为什么，但在更改客户端工具后，它显然已经快到了 0.04 秒。

Answer 1

SQL Commands: DECLARE :

In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.

这取决于您是使用单个事务中的游标还是使用“WITH HOLD”和多个事务。

如果您使用“WITH HOLD”，那么在名为“DECLARE”的事务的“COMMIT”上，将使用游标中的所有数据创建一个临时表。如果数据量很大，表会保存到磁盘中，因此获取速度会慢一些。但不会那么慢，因为这应该是对一些合理数量的行的顺序扫描。

tometzky=> begin;
BEGIN
Time: 0.301 ms
tometzky=> declare c no scroll cursor with hold for select pg_sleep(1) from generate_series(1,6);
DECLARE CURSOR
Time: 1.140 ms
tometzky=> commit;
COMMIT
Time: 6007.180 ms (00:06.007)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 0.384 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 0.336 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------
(0 rows)

Time: 0.338 ms

当您使用来自调用 DECLARE 的同一事务的游标时，每个 FETCH 将在请求的行数可用时立即返回:

tometzky=> begin;
BEGIN
Time: 0.301 ms
tometzky=> declare c no scroll cursor for select pg_sleep(1) from generate_series(1,6);
DECLARE CURSOR
Time: 1.225 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 3004.041 ms (00:03.004)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 3003.855 ms (00:03.004)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------
(0 rows)

Time: 0.229 ms
tometzky=> commit;
COMMIT
Time: 0.444 ms

但是，例如，如果您使用的查询需要将排序作为最后一步，则它必须首先获取所有行以对它们进行排序。