02.09.2025 05:21
OlegIct 0 489 Источник

Изначально Лоренс хотел написать статью о параметре конфигурации PostgreSQL cursor_tuple_fraction, но обнаружил, что про это уже писали. Тогда он подумал, что мог бы изучить влияние этого параметра на драйвер JDBC. Это привело к борьбе с библиотекой auto_explain. В статье даны наблюдения о работе JDBC-драйвера PostgreSQL.

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

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

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

В планах выполнения запросов PostgreSQL рассчитывается два числа, которые показывает команда EXPLAIN:

cost=A..B

Без использования курсора, планировщик выбирает план с наименьшей полной стоимостью, а при использовании курсора планировщик выбирает план с минимальным значением формулы: A + cursor_tuple_fraction * (B − A).

В каком ещё случае настраивают значение параметра конфигурации 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 драйвера PostgreSQL

По умолчанию 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

Я перезапустил экземпляр PostgreSQL, чтобы библиотека, указанная в параметре shared_preload_libraries была загружена, и запустил вышеприведённую java-программу. Затем я посмотрел диагностический журнал кластера баз данных, но не увидел залогированных запросов, которые посылались java-программой. Планы выполнения других запросов, например, пслыаемых через psql логировались. Запросы же моей java-программы выполнялись, но не логировались. Это не могло быть ошибкой в java-программе, так как библиотека auto_explain работает в серверном процессе, который определённо выполнял SQL-команды.

Мне потребовалось время, чтобы понять, в чём дело. Если вы считаете, что хорошо знаете java и PostgreSQL, проверьте себя: найдите ошибку в коде java-программы. Ответ дан в следующей части статьи.

Исправление Java-программы, чтобы она регистрировала планы выполнения

Курсоры 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-драйвере, то, возможно, догадались, почему такое произошло. Причина в том, что JDBC-драйвер не использует команду DECLARE для создания курсора, вместо этого использует другой метод клиент-серверного протокола PostgreSQL:

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

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

Заключение

Было найдено,что:

  • библиотека auto_explain логирует запросы только при закрытии портала

  • драйвер JDBC PostgreSQL закрывает портал только после фиксации транзакции

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

Комментарии (0)