当前位置：文江博客话题详情

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

发布于 2024-10-22 22:21:45 字数 215 浏览 1 评论 0原文

这篇文章建议我可以使用游标以限制的速率从查询中获取数据。我该怎么做？

我的目标是减少这个低优先级查询对其他高优先级查询的影响。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪我终i 2024-10-29 22:21:45

您可以通过使用 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）来阻止规划器选择此类计划，但这并不总是可行。

You can do this by declaring server-side cursors, with the DECLARE command:

DECLARE my_cursor CURSOR FOR select * from foo;

And then read its results using the FETCH command repeatedly:

FETCH 10 FROM my_cursor;

By sleeping between the FETCH command, you're effectively limiting how fast the query can execute.

After you're done with it, you can get rid of the cursor by calling COMMIT, ROLLBACK, or CLOSE my_cursor

Do note that some kinds of queries cannot be directly streamed via a cursor, but will be ran to completion before they produce the first row of output. Queries with hash aggregates and large non-indexed sorts are an example. You can lower the cursor_tuple_fraction setting (default 0.1) to discourage the planner to choose these sorts of plans, but it's not always possible.

回复收藏 0 原文

蓝海 2024-10-29 22:21:45

我知道限制光标的唯一方法是做一些工作，然后睡觉。

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

The only way I know to throttle a cursor is to do some work, then sleep.

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;

The source code for pg_dump includes pseudo-code for its "throttle" algorithm, but just sleeping for a fixed period is probably good enough.

* 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

回复收藏 0 原文

~没有更多了~