PostgreSQL: 使用偏移量和限制会变得非常缓慢

Question

我有一个名为 tmp_drop_ids 的表格，其中只有一列 id，包含着330万条数据。我希望能遍历这个表，每次处理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)

一开始这个程序运行得很好（生成临时表大约需要0.15秒），但是偶尔会变慢，例如在处理300k张票时，生成临时表开始需要11-12秒，在处理400k张票时也是如此。它似乎不太可靠。

我会在其他查询中使用这些id，所以我认为最好的地方就是在一个临时表中保存它们。是否有更好的方法来迭代处理结果呢？

- Claudiu

---

你是否已经为tmp_drop_ids创建了索引？在tmp_drop_ids表上创建唯一索引tmp_drop_ids_id_uidx (id)； - filiprem

Answer 1

使用游标代替。使用 OFFSET 和 LIMIT 很昂贵，因为 pg 必须执行查询、处理并跳过 OFFSET 行。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中支持（在服务器端游标部分搜索）。

- Pavel Stehule

---

我最终使用Python（使用游标对象的fetchmany）完成了这个任务。 - Claudiu

Answer 2

如果您的id已被索引，您可以使用“limit”和“>”进行筛选，例如在类似Python的伪代码中：

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就可以使用索引来执行您的查询。

- Tometzky