postgresql：偏移量+限制变得非常慢

Question

我有一张表tmp_drop_ids，其中有一列，有id330万个条目。我想遍历表，每200个条目执行一次操作。我有以下代码：

LIMIT = 200
for offset in xrange(0, drop_count+LIMIT, LIMIT):
    print "Making tmp table with ids %s to %s/%s" % (offset, offset+LIMIT, drop_count)
    query = """DROP TABLE IF EXISTS tmp_cur_drop_ids; CREATE TABLE tmp_cur_drop_ids AS
    SELECT id FROM tmp_drop_ids ORDER BY id OFFSET %s LIMIT %s;""" % (offset, LIMIT)
    cursor.execute(query)

最初，此方法运行良好（生成tmp表的时间约为0.15秒），但偶尔会变慢，例如，大约有30万张票，它开始花费11-12秒的时间来生成此tmp表，然后大约有40万张。基本上看来是不可靠的。

我将在其他查询中使用这些ID，因此我认为在tmp表中放置ID的最佳位置。有没有更好的方法来遍历这样的结果？

Answer 1

请改用游标。使用OFFSET和LIMIT非常昂贵-因为pg必须执行查询，处理和跳过OFFSET行。偏移量就像“跳过行”，这很昂贵。

光标文档

游标允许对一个查询进行迭代。

BEGIN
DECLARE C CURSOR FOR SELECT * FROM big_table;
FETCH 300 FROM C; -- get 300 rows
FETCH 300 FROM C; -- get 300 rows
...
COMMIT;

也许可以使用服务器端游标，而无需显式使用DECLARE语句，而仅在psycopg（关于服务器端游标的搜索部分）中提供支持。