如何在PostgreSQL中流式传输从表中排序的数据?

Question

我正在尝试使用Java按排序顺序从PostgreSQL表中获取数据。问题在于PostgreSQL的查询规划 - 看看这些查询：

select *
from the_table
order by the_indexed_column asc
;

此查询计划为：

Gather Merge  (cost=16673025.39..28912422.53 rows=104901794 width=64)
Workers Planned: 2
->  Sort  (cost=16672025.36..16803152.60 rows=52450897 width=64)
Sort Key: "time"
->  Parallel Seq Scan on raw  (cost=0.00..4030550.63 rows=52450897 width=64)

顶部的Sort阻止数据流，因为它必须首先聚合数据。这对于具有大量数据的排序来说是有问题的，例如在我的情况下为 20GB，因为它们必须保存到磁盘。

比较此查询：

select *
from raw
order by the_index_column asc
limit 10000000
;

计划：

Limit  (cost=0.57..9871396.70 rows=10000000 width=64)
->  Index Scan using raw_time_idx on raw  (cost=0.57..124263259.38 rows=125882152 width=64)

这些数据可以轻松流式传输。

我认为PostgreSQL在这里只优化了总查询速度，而不是针对磁盘使用和流媒体功能等附加功能进行了优化。有没有办法调整PostgreSQL以选择第二个计划来支持第一个计划？

编辑：这是执行查询的代码。不打印末尾的字符串。

Connection database = DriverManager.getConnection(DatabaseConstants.DATABASE_URL, DatabaseConstants.USER, DatabaseConstants.PASSWORD);
String sql = "select " +
"column_a, column_b, some_expression, morestuff " +
"from the_table " +
"order by the_indexed_column asc " +
";";
database.setAutoCommit(false);
PreparedStatement statement = database.prepareStatement(sql);
statement.setFetchSize(1024);
ResultSet set = statement.executeQuery();
System.out.println("Got first results...");

cursor_tuple_fraction的值分别降低到 0.05、0.01 和 0.0，但没有任何效果。

PostgreSQL 版本： 10.7， 驱动程序版本：42.2.5.jre7(Maven中最新的(现在是真正的))， 操作系统：Fedora 29(最小，KDE在上面)

这是日志上的输出，带有log_min_duration_statement = 0：

2019-03-29 17:11:52.532 CET [15068] LOG:  database system is ready to accept connections
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.397 ms  parse <unnamed>: SET extra_float_digits = 3
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.008 ms  bind <unnamed>: SET extra_float_digits = 3
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.046 ms  execute <unnamed>: SET extra_float_digits = 3
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.024 ms  parse <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.006 ms  bind <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2019-03-29 17:12:04.615 CET [15119] LOG:  duration: 0.026 ms  execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2019-03-29 17:12:04.662 CET [15119] LOG:  duration: 0.023 ms  parse <unnamed>: BEGIN
2019-03-29 17:12:04.662 CET [15119] LOG:  duration: 0.006 ms  bind <unnamed>: BEGIN
2019-03-29 17:12:04.662 CET [15119] LOG:  duration: 0.004 ms  execute <unnamed>: BEGIN
2019-03-29 17:12:04.940 CET [15119] LOG:  duration: 277.705 ms  parse <unnamed>: [the query...] 
2019-03-29 17:12:05.162 CET [15119] LOG:  duration: 222.742 ms  bind <unnamed>/C_1: [the query...]

在此期间，磁盘使用率会增加。

Answer 1

这应该不是问题。通过将具有非零值的setFetchSize应用于预准备语句来使用游标。

然后PostgreSQL将选择一个快速返回第一行的计划，即索引扫描。

如果 PostgreSQL 仍然选择排序，则从其默认值 0.1(总结果集的 10%)降低cursor_tuple_fraction。

记录：这是它在日志中的样子：

duration: 0.126 ms  parse S_1: BEGIN
duration: 0.015 ms  bind S_1: BEGIN
duration: 0.034 ms  execute S_1: BEGIN
duration: 0.998 ms  parse S_2: SELECT /* the query */
duration: 1.752 ms  bind S_2/C_3: SELECT /* the query */
duration: 0.081 ms  execute S_2/C_3: SELECT /* the query */
duration: 0.060 ms  execute fetch from S_2/C_3: SELECT /* the query */
duration: 0.065 ms  execute fetch from S_2/C_3: SELECT /* the query */
duration: 0.070 ms  execute fetch from S_2/C_3: SELECT /* the query */
duration: 0.078 ms  execute fetch from S_2/C_3: SELECT /* the query */