postgresql - Postgres : The best way to optmize "greater than" query

Question

优化在子组的下一个 id 值上与同一张表连接的连接查询的最佳方法是什么？现在我有这样的东西:

CREATE OR REPLACE FUNCTION select_next_id(bigint, bigint) RETURNS bigint AS $body$
DECLARE
    _id bigint;
BEGIN
    SELECT id INTO _id FROM table WHERE id_group = $2 AND id > $1 ORDER BY id ASC LIMIT 1;
    RETURN _id;
END;
$body$ LANGUAGE plpgsql;

和 JOIN 查询:

SELECT * FROM table t1
JOIN table t2 ON t2.id = select_next_id(t1.id, t1.id_group)

该表有超过 2kk 行，并且需要非常非常长的时间。有没有更好的方法来快速做到这一点？我在列 id 上也有 UNIQUE INDEX。我猜不是很有帮助。

一些示例数据:

id | id_group=============1  | 12  | 13  | 14  | 25  | 26  | 220 | 425 | 437 | 440 | 155 | 2

我想收到这样的东西:

id | id_next1  | 22  | 33  | null4  | 5 5  | 66  | 55

等等。

Answer 1

对于函数中的查询，您需要(id_group, id) 上的索引，而不仅仅是(id)。

接下来，你不需要plpgsql在函数本身的开销，你可以通过使其稳定且具有较小的成本来给planner一些提示:

CREATE OR REPLACE FUNCTION select_next_id(bigint, bigint) RETURNS bigint AS $body$
    SELECT id FROM table WHERE id_group = $2 AND id > $1 ORDER BY id ASC LIMIT 1;
$body$ LANGUAGE sql STABLE COST 10;

在最后的查询中，根据您实际尝试执行的操作，您可以使用 lead() 摆脱连接和函数调用，如 horse 所强调的那样:

http://www.postgresql.org/docs/current/static/tutorial-window.html