Postgres - 分批运行查询？

Question

能否循环遍历查询结果？例如，当查询到500,000行时，返回前10,000行的结果，然后重新执行查询并返回接下来的10,000行结果。

我的需求是运行查询并构建一个数组，类似于这样：

$result = pg_query("SELECT * FROM myTable");

$i = 0;
while($row = pg_fetch_array($result) ) {
  $myArray[$i]['id'] = $row['id'];
  $myArray[$i]['name'] = $row['name'];
  $i++;
}

但是，我知道将有几十万行数据，因此我想分批处理，如每批处理10,000行... 1-9,999，然后10,000-10,999等等... 原因是因为我一直收到以下错误提示：

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 3 bytes)

顺便说一句，我不明白3个字节怎么可能用尽512兆字节...所以，如果这是我可以轻松更改的内容，那就太好了，虽然最好还是分批处理？

- CaffeineIV

---

你没有足够的内存在数组中同时存储所有结果（不使用CURSOR）。但是，你试图做什么？通过填充这个数组，你想要解决什么问题？ - Frank Heikens

Answer 1

最后的3个字节就是压垮骆驼的最后一根稻草了。很可能是在一系列内存分配中尝试分配过多导致失败。

不幸的是，libpq会在将结果集控制权交还给应用程序之前完全将结果集缓存到内存中。除了你在$myArray中使用的内存之外，这还会增加内存的使用量。

建议使用LIMIT ... OFFSET ...来减少内存的占用，但这种方法效率低下，因为每次重新发出查询以获取不同的偏移量（例如为了回答LIMIT 10 OFFSET 10000）会不必要地重复服务器端的排序工作，Postgres仍然需要对整个结果集进行排序，只返回10000..10010行。

相反，可以使用DECLARE ... CURSOR创建一个服务器端游标，然后使用FETCH FORWARD x获取下一个x行。如有必要，重复执行此操作，直到返回小于x的行数。即使在异常时，也不要忘记CLOSE游标。

此外，不要使用SELECT *；如果只需要id和name，则创建指向FOR SELECT id, name的游标（否则，libpq将不必要地检索和缓存你从未使用过的列，增加内存占用和总查询时间）。

如上所示，使用游标，libpq在任何时候最多只会在内存中保存x行数据。但是，请确保在FETCH之间清理你的$myArray，以避免由于$myArray而导致内存不足。

- vladr

---

LIMIT...OFFSET... 真的涉及排序吗？ - armandino

如果你想让 LIMIT ... OFFSET ... 成为确定性的，那么它们将会伴随着一个 ORDER BY，除非你很幸运需要一个简单的索引扫描，否则 必须 进行排序。即使你很幸运并且使用索引扫描可以避免排序，但这些重复的扫描可以通过使用游标来避免。 - vladr

Answer 2

你可以使用LIMIT (x)和OFFSET (y)。

- sriehl

---

我研究了一下，认为我可以得到总数然后进行for循环，但目前为止似乎不起作用... - CaffeineIV

@Caffeine，你完全不需要知道总行数，也不需要使用 for 循环。在 while 循环中执行语句，并在查询返回少于您请求的 x 行时立即跳出循环，表示您已经到达了末尾。 - vladr

@Caffeine，你明白使用LIMIT/OFFSET与游标相比的负面影响吗？ - vladr

Answer 3

PostgreSQL服务器会缓存查询结果，直到您实际检索它们，因此像循环中添加数组一样将导致内存耗尽。要么逐行处理结果，要么检查数组的长度，处理迄今为止提取的结果，然后清除数组。

- Ignacio Vazquez-Abrams

---

我有点困惑...这不是我正在做的吗？运行查询，然后处理结果？抱歉...我没听懂... - CaffeineIV

将行添加到数组中如何被视为处理它们有点难以理解。 - Ignacio Vazquez-Abrams

Answer 4

这个错误的意思是PHP尝试分配3个字节，但512MB可用部分小于3个字节。

即使您分批处理，根据结果数组的大小，仍然可能耗尽可用内存。

也许您并不真正需要获取所有记录？

- Alex Korban