postgresql: offset + limit llega a ser muy lento

Tengo una tabla tmp_drop_ids con una columna, id y 3,3 millones de inputs. Quiero iterar sobre la table, haciendo algo con cada 200 inputs. Tengo este código:

 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) 

Esto funciona bien, al principio, (~ 0.15s para generar la tabla tmp), pero se ralentizará ocasionalmente, por ejemplo, alnetworkingedor de 300k tickets comenzó a tomar 11-12 segundos para generar esta tabla tmp, y de nuevo alnetworkingedor de 400k. Básicamente parece poco confiable.

Usaré esos identificadores en otras consultas, así que pensé que el mejor lugar para tenerlos era en una tabla tmp. ¿Hay alguna forma mejor de iterar a través de resultados como este?

Use un cursor en su lugar. Usar un DESPLAZAMIENTO y LÍMITE es bastante caro, porque pg tiene que ejecutar la consulta, procesar y omitir las filas de DESPLAZAMIENTO. OFFSET es como "omitir filas", que es caro.

documentation del cursor

Cursor permite una iteración sobre una consulta.

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

Probablemente puede usar un cursor del lado del server sin usar explícitamente la instrucción DECLARE, solo con soporte en psycopg (sección de búsqueda sobre los cursores del lado del server).

Si sus identificadores están indexados, puede usar "limit" con ">", por ejemplo, en un pseudocódigo tipo 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") 

De esta forma, Postgres puede usar el índice para su consulta.