使用psycopg2的Python循环中的性能下降

Question

我使用的是python3.6，我的table1包含了大量的数据，我只需要在表“area”的区域中包含10%的数据。我认为首先创建一个视图并解析它而不是完整的table1可以极大地提高性能，但是相反，我发现大约在第200次迭代时，速度急剧下降到每秒一次（而最初是屏幕上连续不断的数字流）。代码如下：

import psycopg2

conn = psycopg2.connect("dbname=db_name user=postgres")
cur = conn.cursor()
cur.execute("create view temp as select st_setsrid(the_geom::geometry,2154), table1.gid from tout.area, public.table1 where (st_contains(area.geom,st_setsrid(table1.the_geom::geometry,2154)));")
cur.execute("select count(*) from temp")
nbtotal = cur.fetchone()[0]
print(nbtotal)
for i in range(nbtotal):
    print(i+1, " sur ", nbtotal)
    cur.execute ("insert into tout.table2 (geom) select st_setsrid FROM temp order by gid limit 1 offset "+str(i)+ ";")
    conn.commit()
cur.execute("drop view temp;")
conn.commit()

你知道为什么会发生这种情况吗？怎么解决？在此之前，我试图不创建一个视图（所以做了一个预选），循环在几千次迭代之后变慢了（我理解这是很正常的），但还远远没有达到那种节奏。解决方法是加载所有数据，然后过滤结果并将其写入新表中。但这看起来并不是一个合适的长期解决方案。在

谢谢

Answer 1

实际上，问题似乎来自于PostgreSQL中视图的存在方式。创建物化视图似乎解决了我的问题。如果有人能解释的话，那就太好了。在

如果这对任何人都有帮助，下面是我的新代码版本：

import os, psycopg2

conn = psycopg2.connect("dbname=db_name user=postgres")
cur = conn.cursor()
cur.execute("create materialized view temp as select st_setsrid(the_geom::geometry,2154), table1.gid from tout.area, public.table1 where (st_contains(area.geom,st_setsrid(table1.the_geom::geometry,2154))); select count(*) from temp")
nbtotal = cur.fetchone()[0]
print(nbtotal)
for i in range(nbtotal):
    print(i+1, " sur ", nbtotal)
    cur.execute ("insert into tout.table2 (geom) select st_setsrid FROM temp order by gid limit 1 offset "+str(i)+ ";")
cur.execute("drop materialized view temp;")
conn.commit()

Answer 2

你是不是想一下子就做完了？公司名称：

cur.execute ('''
    insert into tout.table2 (geom)
    select st_setsrid(the_geom::geometry, 2154)
    from
        tout.area
        inner join
        public.table1 on
            st_contains(area.geom, st_setsrid(table1.the_geom::geometry, 2154))
''')