如何在PostgreSQL中减少长时间运行的I/O密集型查询的影响？

Question

这篇文章建议我可以使用游标以受限速率获取查询结果。如何做到呢？

我的目的是减少此低优先级查询对其他更高优先级查询的影响。

- jl6

Answer 1

您可以通过使用DECLARE命令声明服务器端游标来实现此目的：

DECLARE my_cursor CURSOR FOR select * from foo;

然后通过反复使用FETCH命令来读取其结果：

FETCH 10 FROM my_cursor;

通过让FETCH命令之间等待一段时间，您有效地限制了查询执行的速度。

完成后，可以通过调用COMMIT、ROLLBACK或CLOSE my_cursor来摆脱光标。

请注意，某些类型的查询无法直接通过光标流式传输，而是会在产生第一行输出之前先运行到完成。具有哈希聚合和大型非索引排序的查询就是一个例子。您可以降低cursor_tuple_fraction设置（默认为0.1）以防止规划程序选择这些类型的计划，但这并不总是可能的。

- intgr

Answer 2

我知道限制游标速度的唯一方法是进行一些操作，然后休眠。

CREATE OR REPLACE FUNCTION test_cursor()
  RETURNS void AS
$BODY$

DECLARE
    curs1 CURSOR FOR SELECT select * from information_schema.tables limit 5;

BEGIN

    FOR example_variable IN curs1 LOOP
        -- Other pgsql statements

        -- sleep for one second
        perform pg_sleep(1);

    END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

pg_dump的源代码包括其“节流”算法的伪代码，但仅仅是固定时间睡眠可能已经足够了。

* If throttle is non-zero, then
*      See how long since the last sleep.
*      Work out how long to sleep (based on ratio).
*      If sleep is more than 100ms, then
*          sleep
*          reset timer
*      EndIf
* EndIf

- Mike Sherrill 'Cat Recall'