psycopg2：如果表的内存不足，PostgreSQL将在磁盘上存储表的副本吗 [英] psycopg2: Will PostgreSQL store a copy of a table on disk if it has run out of memory

问题描述

我正在具有2 gb内存的计算机上的4.89亿行（102 gb）上运行以下查询：

I am running the following query on 489 million rows (102 gb) on a computer with 2 gb of memory:

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

我正在将psycopg2与服务器游标一起使用（ cursor_unique_name），一次获取30000行。

I am using psycopg2 with a server cursor ("cursor_unique_name") and fetch 30000 rows at a time.

显然查询的结果无法保留在内存中，但是我的问题是以下查询集是否会同样快：

Obviously the result of the query cannot stay in memory, but my question is whether the following set of queries would be just as fast:

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

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

This means that I would use a temp_table to store the ordered result and fetch data from that table instead.

问这个问题的原因是，如果使用psql手动运行，只需36分钟即可完成，但是花费了8个以上小时（从未完成）以使用psycopg2执行查询时获取前30000行。

The reason for asking this question is that the takes only 36 minutes to complete if run manually using psql, but it took more than 8 hours (never finished) to fetch the first 30000 rows when the query was executed using psycopg2.

推荐答案

1. 如果要按块获取此表并进行排序，则需要创建一个索引。如果没有这样的索引，则每次获取都需要对整个表进行排序。您的光标可能会对获取的每一行对该表进行一次排序—
   
   在表名（x，y，z，h，j，l）上创建索引tablename_order_idx;

如果表数据相对稳定，则应 集群 通过此索引。 $ br
使用tablename_order_idx的群集表名；

If your table data is relatively stable then you should cluster it by this index. This way table data will be fetched without too much seeking on disk.
cluster tablename using tablename_order_idx;

如果要分块获取数据，则不要使用游标，因为游标每次总是可以工作一行。您应该使用 limit 和偏移量 ：

从表名中选择*按x，y，z，h，j，l

限制30000偏移44 * 30000

If you want to get data in chunks the you should not use cursor, as it will always work one row at a time. You should use limit and offset:
select * from tablename order by x, y, z, h, j, l
limit 30000 offset 44*30000

这篇关于psycopg2：如果表的内存不足，PostgreSQL将在磁盘上存储表的副本吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！