PostgreSQL查询结果集过大

Question

6

我正在针对一个postgresql数据库中的表运行查询。数据库位于远程机器上。该表使用了postgresql的分区能力，大约有30个子表。

查询将返回一个庞大的结果集，大约1.8百万行。

在我的代码中，我使用spring jdbc支持的方法JdbcTemplate.query，但我的RowCallbackHandler没有被调用。

我最好的猜测是，postgresql jdbc驱动程序（我使用版本8.3-603.jdbc4）在调用我的代码之前在内存中累积结果。我认为fetchSize配置可以控制此操作，但我尝试过了，没有任何改变。我按照postgresql手册推荐做了这个操作。

当我使用Oracle XE时，这个查询运行得很好。但我正在尝试迁移到postgresql，因为Oracle XE中没有可用的分区功能。

我的环境：

- Postgresql 8.3 - Windows Server 2008 Enterprise 64-bit - JRE 1.6 64-bit - Spring 2.5.6 - Postgresql JDBC Driver 8.3-603

- tuler

你的调用有返回吗？如果没有，你尝试过使用ctrl-\（Windows下为ctrl-break）、jstack、jconsole、visualvm或类似工具找出线程停止的位置了吗？ - Tom Hawtin - tackline

正在处理查询，在驱动程序内部。 - tuler

请参见 https://dev59.com/-XA65IYBdhLWcg3wsgyA#47517489 - rogerdpack

4个回答

3

我敢打赌，你的应用程序中没有一个客户需要同时获取1.8M行数据。你应该想一种合理的方法将结果分成较小的块，并让用户有机会逐个迭代查看。

这就是Google所做的。当你进行搜索时，可能会返回数百万条结果，但他们每次只返回25页，因为你很可能在第一页找到想要的内容。

如果不是客户端，并且结果正在以某种方式进行处理，我建议让数据库处理所有这些行并直接返回结果。毫无意义地返回1.8M行数据，仅在中间层进行计算。

如果以上两种情况都不适用，那么你真的有一个问题。是时候重新考虑了。

在阅读后续回复后，我认为这更像是一个报告解决方案，应该批量处理或实时计算并存储在不属于您事务系统的表中。将1.8M行数据带到中间层来计算移动平均值是不可扩展的。

我建议重新定位自己-开始将其视为报告解决方案。

- duffymo

数据库加载是在系统启动时完成的，目的是将数据“转换”为更适合客户端使用的格式。RowCallbackHandler本身没有任何配置内容。但连接、数据源、驱动或数据库设计方面可能需要进行大量配置。在生产服务器上进行调试有些困难，因为那里有大量数据，所以我只能依靠日志记录。 - tuler

2

fetchSize属性的作用与postgres手册描述的一样。

我的错误在于，我将自动提交设置为false，但是这个连接来自连接池，而不是被预处理语句使用的连接。

感谢所有的反馈。

- tuler

1

我已经完成上述所有步骤，但是我还需要最后一步：确保调用被包装在事务中，并将事务设置为只读，以便不需要回滚状态。

我添加了这个：@Transactional(readOnly = true) 干杯。

- Patrick

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Trey · Accepted Answer

为了使用游标检索数据，您必须将ResultSet类型设置为ResultSet.TYPE_FORWARD_ONLY（默认值），并在设置提取大小的同时将自动提交设置为false。这在您链接的文档中有提到，但是您没有明确提到您执行了这些步骤。

请注意PostgreSQL的分区方案。它会对优化器产生非常可怕的影响，并可能导致性能问题（具体取决于您的数据细节）。无论如何，您的行只有1.8M行吗？如果适当地建立索引，就没有理由仅基于大小进行分区。