Блог

Я пытаюсь получить данные из таблицы PostgreSQL в отсортированном порядке, используя Java. Проблема заключается в планировании запросов 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 Вверху предотвращает потоковую передачу данных, потому что сначала они должны агрегироваться. Это проблематично для сортировок с большими объемами данных, например, 20 ГБ в моем случае, поскольку они должны быть сохранены на диске.

Сравните этот запрос:

 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...]
  

При этом увеличивается использование диска.

Это не должно быть проблемой. Используйте курсор, применяя setFetchSize к подготовленному оператору ненулевое значение.

Затем PostgreSQL выберет план, который быстро возвращает первые строки, то есть сканирование индекса.

Если PostgreSQL все еще выбирает сортировку, уменьшите cursor_tuple_fraction значение по умолчанию, равное 0.1 (10% от общего набора результатов).

Для записи: вот как это должно выглядеть в журнале:

 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 */