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

问题描述：

我有一个表tmp_drop_ids与一列，id，和330万条目。我想遍历表格，每200个条目做一些事情。我有这样的代码：postgresql：偏移+限制变得非常缓慢

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)

这运行正常，首先，（0.15秒〜生成TMP表），但它偶尔会慢下来，例如大约30万张门票开始花费11-12秒来生成这张tmp表格，并且再次大约40万张。它基本上看起来不可靠。

我会在其他查询中使用这些ID，所以我想到了让他们在tmp表中的最佳位置。有没有更好的方法来迭代这样的结果？

你有tmp_drop_ids索引吗？ CREATE UNIQUE INDEX tmp_drop_ids_id_uidx ON tmp_drop_ids（id）; – filiprem

@filiprem：我是的 – Claudiu

答

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

cursor documentation

光标允许在一个查询中的迭代。

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（有关服务器端游标搜索部分）的支持。

我最终从python做了这个（使用游标对象的'fetchmany'）。 – Claudiu

答

如果您的ID被编入索引，您可以使用“限制”和“>”，例如在蟒蛇般的伪代码：

limit=200 
max_processed_id=-1 
query ("create table tmp_cur_drop_ids(id int)") 
while true: 
    query("truncate tmp_cur_drop_ids") 
    query("insert into tmp_cur_drop_ids(id)" \ 
     + " select id from tmp_drop_ids" \ 
     + " where id>%d order by id limit %d" % (max_processed_id, limit)) 
    max_processed_id = query("select max(id) from tmp_cur_drop_ids") 
    if max_processed_id == None: 
    break 
    process_tmp_cur_drop_ids(); 
query("drop table tmp_cur_drop_ids")

这样的Postgres可以使用索引查询。

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

相关推荐