postgresql查询的大ResultSet [英] Large ResultSet on postgresql query

问题描述

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

I'm running a query against a table in a postgresql database. The database is on a remote machine. The table has around 30 sub-tables using postgresql partitioning capability.

查询将返回一个大的结果集，约为180万行。

The query will return a large result set, something around 1.8 million rows.

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

In my code I use spring jdbc support, method JdbcTemplate.query, but my RowCallbackHandler is not being called.

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

My best guess is that the postgresql jdbc driver (I use version 8.3-603.jdbc4) is accumulating the result in memory before calling my code. I thought the fetchSize configuration could control this, but I tried it and nothing changes. I did this as postgresql manual recomended.

当我使用Oracle XE时，此查询工作正常。但我正在尝试迁移到postgresql，因为分区功能在Oracle XE中不可用。

This query worked fine when I used Oracle XE. But I'm trying to migrate to postgresql because of the partitioning feature, which is not available in Oracle XE.

我的环境：

• Postgresql 8.3


• Windows Server 2008 Enterprise 64位


• JRE 1.6 64位


• Spring 2.5.6


• Postgresql JDBC Driver 8.3-603

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

推荐答案

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

In order to use a cursor to retrieve data you have to set the ResultSet type of ResultSet.TYPE_FORWARD_ONLY (the default) and autocommit to false in addition to setting a fetch size. That is referenced in the doc you linked to but you didn't explicitly mention that you did those steps.

小心PostgreSQL的分区方案。它确实与优化器一起发生了非常可怕的事情，并且可能导致大量性能问题，而不应该存在（取决于您的数据的具体情况）。无论如何，你的行只有1.8M行吗？没有理由需要根据大小进行分区，只要它被适当地编入索引。

Be careful with PostgreSQL's partitioning scheme. It really does very horrible things with the optimizer and can cause massive performance issues where there should not be (depending on specifics of your data). In any case, is your row only 1.8M rows? There is no reason that it would need to be partitioned based on size alone given that it is appropriately indexed.

这篇关于postgresql查询的大ResultSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！