В планах выполнения запросов PostgreSQL рассчитывается два числа, которые показывает команда EXPLAIN:cost=A..B Без использования курсора, планировщик выбирает план с наименьшим значением B, а при использовании курсора: A + cursor_tuple_fraction * (B − A), где cursor_tuple_fraction - параметр конфигурации, который можно установить на уровне сессии и других уровнях.

О параметре конфигурации cursor_tuple_fraction

Передача строк клиенту создаёт задержку, а обработка строк на стороне клиента занимает ещё больше времени. Если приложение обрабатывает результат выполнения запроса построчно с помощью курсора, то потребуется дополнительно время, прежде чем клиент получит последнюю строку результата выполнения запроса. Поэтому, при использовании курсоров, важно получить первые строки как можно быстрее, а общее время выполнения запроса менее важно. При использовании курсоров начинает играть роль параметр конфигурации cursor_tuple_fraction: для запросов, выполняемых с помощью курсора, параметр указывает планировщику предпочесть планы выполнения, которые быстро выдают первые строки запроса, а не общее время выполнения запроса.

Используя курсоры, можно не читать весь результирующий набор строк сразу, что снижает потребление памяти. В PostgreSQL можно использовать команду DECLARE для явного создания курсора, но обычно используются функции API клиентского интерфейса или версия синтаксиса цикла FOR record IN запрос LOOP языка PL/pgSQL, которая неявно декларирует и использует курсор.

В каком ещё случае настраивают значение параметра конфигурации cursor_tuple_fraction? Значение настраивают, если используют курсор, но хочется чтобы выбирался план с наименьшим общим временем выполнения запроса (B). Обычно, это происходит при объявлении курсора с опцией удержания курсора до конца сессии (WITH HOLD). По умолчанию, курсоры живут до конца транзакции. Набор строк курсора WITH HOLD сохраняется в памяти серверного процесса (материализуется) при фиксации транзакции, поэтому команда COMMIT ждёт, пока не будут получены все строки выборки такого курсора. Оптимизировать имеет смысл получение не первых строк, а всех строк такого курсора, то есть установить значение cursor_tuple_fraction=1, чтобы выбирался план с наименьшей общей стоимостью запроса: A + 1 * (B − A)= B

Пример использования cursor_tuple_fraction

Создадим таблицу:

-- создание таблицы и загрузка данных
CREATE UNLOGGED TABLE large (
   id bigint GENERATED ALWAYS AS IDENTITY,
   val double precision NOT NULL,
   payload text NOT NULL
);
INSERT INTO large (val, payload)
SELECT random(),
       'some longer string to take up space'
FROM generate_series(1, 1000000);
-- создание файлов vm, fsm и сбор статистики
VACUUM (ANALYZE) large;
-- ограничения целостности и индексы создаются после загрузки данных
ALTER TABLE large
ADD CONSTRAINT large_pkey PRIMARY KEY (id);
CREATE INDEX large_val_idx ON large (val);

Для выполнения обычного (не подготовленного) запроса используется сканирование битового индекса и сортировка, что является самым быстрым способом получения всех строк запроса:

EXPLAIN
SELECT * FROM large WHERE val < 0.01 ORDER BY id;

Sort Key: id
 ->  Bitmap Heap Scan on large  (cost=190.10..10953.58 rows=10022 width=52)
       Recheck Cond: (val < '0.01'::double precision)
       ->  Bitmap Index Scan on large_val_idx  (cost=0.00..187.59 rows=10022 width=0)
             Index Cond: (val < '0.01'::double precision)

Когда запрос используется в курсоре, то планировщик выбирает сканирование индекса (Index Scan), которое быстро выдаёт первые строки в нужном порядке (заданном ORDER BY id):

EXPLAIN
DECLARE c CURSOR FOR SELECT * FROM large WHERE val < 0.01 ORDER BY id;

 Index Scan using large_pkey on large  (cost=0.42..41293.43 rows=10022 width=52)
   Filter: (val < '0.01'::double precision)

Запрос тот же самый, а выбран другой план, у которого небольшая начальная стоимость (A = 0.42). При этом полная стоимость плана (B=41293.43) в несколько раз больше, чем у неподготовленного запроса (B=10953.59).

Курсор в JDBC

По умолчанию JDBC-драйвер PostgreSQL считывает сразу весь результат выполнения запроса за один раз. Это может вызвать проблемы, если результатирующая выборка большого объема. Чтобы сгладить проблему с обработкой и передачей больших объемов данных, можно использовать вызов метода java.sql.PreparedStatement.setFetchSize(int) так, чтобы JDBC-драйвер запрашивал строки у серверного процесса по частям. Кроме того, драйвер инициирует использование подготовленных запросов, если несколько раз выполнить подготовленный запрос, используя PreparedStatement. По умолчанию, это происходит, начиная с пятого выполнения команды (prepareThreshold=5), но вы можете изменить это пороговое значение (в примере ниже pgstmt.setPrepareThreshold(1)). https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements

Чтобы проверить работу JDBC-драйвера, я написал эту небольшую программу на Java:

public class Cursor {
 public static void main(String[] args) throws ClassNotFoundException, java.sql.SQLException {
  Class.forName("org.postgresql.Driver");
  java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:postgresql://dba1.ru:5432/tantor?user=Oleg");
  // по умолчанию, курсоры открываются в транзакции и существуют до её завершения, поэтому отключаем автокоммит
  conn.setAutoCommit(false);
  java.sql.PreparedStatement stmt = conn.prepareStatement("SELECT * FROM large WHERE val < 0.01 ORDER BY id");
  // получать результат по частям (чанкам размером 1000), вместо получения всех строк разом
  stmt.setFetchSize(1000);
  /* Подготовленные запросы на стороне серверного процесса это опция не JDBC, а серверной части PostgreSQL.
     По умолчанию, подготовленные запросы используются начиная с пятого выполнения.
     Для изменения с пятого на первое нужно получить ссылку на конкретный класс драйвера PostgreSQL.
     Альтернативно можно было бы указать параметр "prepareThreshold=1" передав его в свойствах при создании соединения, этот способ стандартен и переносим, чем использование классов драйвера. */
  org.postgresql.PGStatement pgstmt = stmt.unwrap(org.postgresql.PGStatement.class);
  // использовать подготовленные запросы с первого, а не пятого  раза
  pgstmt.setPrepareThreshold(1);
  java.sql.ResultSet rs = stmt.executeQuery();
  // выборка всех строк из ResultSet в цикле
  long counter = 0;
  while (rs.next()) ++counter;
  System.out.println("Got " + counter + " rows.");
  rs.close();
  stmt.close();
  conn.close();
 }
}

Почему auto_explain не всегда ловит запросы

Для просмотра реального плана выполнения, использованного при выполнении запроса, посылаемого через JDBC-драйвер воспользуемся библиотекой auto_explain. Библиотека логирует план выполнения всех команд, чьё время превышает установленный параметром расширения auto_explain.log_min_duration пороговое время. Я включил auto_explain со следующими параметрами:

# после изменении параметра нужно перезапустить экземпляр PostgreSQL
shared_preload_libraries = 'auto_explain'
# логировать запросы с любой длительностью (то есть все запросы)
auto_explain.log_min_duration = 0

Запросы java-программы выполнялись, но планы запросов не логировались. Планы запросов, послыаемые через psql, логировались. Мне потребовалось время, чтобы понять, в чём дело.

Причина нелогирования планов auto_explain

Курсоры в PostgreSQL реализуется с помощью структуры, называемой "порталом", которая хранит текущее состояние выполнения запроса. Триггером для срабатывания auto_explain является длительность выполнения запроса. Следовательно, auto_explain должен ждать, пока команда не будет выполнена, так как до этого момента длительность выполнения команды неизвестна. При использовании портала, выполнение запроса завершается при закрытии портала. Я предполагал, что портал закрывается, когда я закрываю ResultSet вызовом метода rs.close(), однако вызов этого метода не отправлял сообщение серверному процессу. Портал закрывался, когда закрывалась транзакция. Я отключил автокоммит, чтобы иметь возможность использовать курсор, но я забыл закрыть транзакцию, так как я ошибочно думал, что я не менял данные, а только читал их и фиксировать транзакцию не нужно.

Чтобы заставить библиотеку auto_explain работать как надо, мне пришлось добавить одну строку conn.commit(); в правильное место программы:

 rs.close();
 stmt.close();
 conn.commit();
 conn.close();

cursor_tuple_fraction не работает при использовании JDBC-драйвера

План, команды, передаваемой через JDBC-драйвер, был таким:

Query Text: SELECT * FROM large WHERE val < 0.01 ORDER BY id
Sort  (cost=11597.46..11622.55 rows=10034 width=52)
  Sort Key: id
  ->  Bitmap Heap Scan on large  (cost=190.19..10930.57 rows=10034 width=52)
        Recheck Cond: (val < '0.01'::double precision)
        ->  Bitmap Index Scan on large_val_idx  (cost=0.00..187.68 rows=10034 width=0)
              Index Cond: (val < '0.01'::double precision)

Почему PostgreSQL не использует план со сканированием по индексу (Index Scan), который быстро отдает первые строки? Причина в том, что JDBC-драйвер не использует команду DECLARE для создания курсора, вместо этого использует другой метод клиент-серверного протокола:

Скрытый текст

Весь цикл выполнения состоит из этапа разбора, который создает подготовленную команду из текстовой строки запроса; этапа связывания, который создает портал на основе подготовленного оператора и значений для всех необходимых параметров; и этапа выполнения, который выполняет запрос портала. В случае запроса, возвращающего строки (SELECT, SHOW и т. д.), этап выполнения может быть настроен на получение только ограниченного количества строк, поэтому для завершения выполнения команды может потребоваться несколько этапов выполнения

JDBC-драйвер реализует курсоры, многократно отправляя сообщение "выполнить" (execute) для одного и того же портала с ограничением числа строк. Это вполне допустимая техника, но серверный процесс на момент планирования не знает, что результат будет извлекаться (fetch) по частям. Следовательно, он не может знать, что следует планировать запрос с использованием cursor_tuple_fraction. Другими словами, JDBC-драйвер никогда не сможет воспользоваться преимуществами планов, быстро возвращающих первые строки, если только вы явно не укажете в команде фразу LIMIT (или FETCH FIRST).

Заключение

расширение PostgreSQL auto_explain логирует запросы только при закрытии портала
драйвер JDBC закрывает портал только после фиксации транзакции
cursor_tuple_fraction бесполезен для запросов через JDBC

cursor_tuple_fraction в pgJDBC