postgresql - psycopg2:如果内存不足，PostgreSQL 会在磁盘上存储一个表的副本吗

Question

我在具有 2 GB 内存的计算机上对 4.89 亿行 (102 GB) 运行以下查询:

select * from table order by x, y, z, h, j, l;

我将 psycopg2 与服务器游标(“cursor_unique_name”)一起使用，一次获取 30000 行。

显然查询的结果不能留在内存中，但我的问题是以下一组查询是否同样快:

select * into temp_table from table order by x, y, z, h, j, l;
select * from temp_table

这意味着我将使用 temp_table 来存储有序结果并改为从该表中获取数据。

问这个问题的原因是，如果使用 psql 手动运行，只需要 36 分钟即可完成，但使用 psycopg2 执行查询时，需要 8 多个小时(从未完成)来获取前 30000 行。

Answer 1

1. 如果你想按 block 获取这个表并排序，那么你需要创建一个索引。如果没有这样的索引，每次提取都需要对整个表进行排序。您的游标可能会为获取的每一行对该表进行一次排序——等待红巨星可能会更快结束……
   在表名 (x, y, z, h, j, l) 上创建索引 tablename_order_idx;

2. 如果你的表数据相对稳定那么你应该cluster它通过这个索引。这样就可以获取表数据，而无需在磁盘上进行太多搜索。
   使用 tablename_order_idx 的集群表名；

3. 如果您想分块获取数据，则不应使用游标，因为它总是一次处理一行。你应该使用 limit and offset :
   按 x、y、z、h、j、l 从表名中选择 *
限制30000偏移量44*30000