Это продолжении серии статей о принятых изменениях в PostgreSQL 18. На этот раз речь пойдет о завершившемся в ноябре коммитфесте.
А самое интересное из предшествующих коммитфестов можно прочитать здесь: 2024-07, 2024-09.
initdb: подсчет контрольных сумм включен по умолчанию
Планировщик: замена однотипных условий OR на поиск в массиве
Планировщик: перестановка выражений в DISTINCT
GROUPING SETS: HAVING -> WHERE
Очистка кеша типов данных и временные таблицы
Планировщик: инкрементальная сортировка при соединении слиянием
Новая функция array_reverse
Функции min и max для типа bytea
Статистика использования параллельных рабочих процессов
Новая функция pg_ls_summariesdir
pg_logicalsnapinspect: новый модуль contrib
Уточненные сообщения об ошибках при установке расширений
Ограничения NOT NULL в системном каталоге
Таблица TOAST для pg_index
COPY… FROM и file_fdw: ограничение на количество ошибочных строк
Недетерминированные правила сортировки: поддержка LIKE
TLS v1.3: настройка наборов шифров
initdb: подсчет контрольных сумм включен по умолчанию
commit: 983a588e0, 04bec894a
Простое, но важное изменение. При инициализации кластера утилитой initdb можно больше не указывать параметр --data-checksums или -k. Подсчет контрольных сумм теперь включен по умолчанию.
Если всё-таки контрольные суммы не нужны, то пригодится новый параметр --no-data-checksums.
Планировщик: замена однотипных условий OR на поиск в массиве
commit: d4378c000, ae4569161
План запроса в 17-й версии трижды сканирует индекс для каждого из однотипных условий:
17=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = 1 OR flight_id = 2 OR flight_id = 3; QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on flights
Recheck Cond: ((flight_id = 1) OR (flight_id = 2) OR (flight_id = 3))
-> BitmapOr
-> Bitmap Index Scan on flights_pkey
Index Cond: (flight_id = 1)
-> Bitmap Index Scan on flights_pkey
Index Cond: (flight_id = 2)
-> Bitmap Index Scan on flights_pkey
Index Cond: (flight_id = 3)
(9 rows)Запрос лучше переписать так, чтобы было достаточно сходить в индекс один раз:
17=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = ANY (ARRAY[1,2,3]); QUERY PLAN
--------------------------------------------------------
Index Scan using flights_pkey on flights
Index Cond: (flight_id = ANY ('{1,2,3}'::integer[]))
(2 rows)Такая оптимизация требует переписывания запроса, что не всегда возможно. Особенно если запросы формирует ORM. В 18-й версии планировщик сам догадается о возможности преобразования условия первоначального запроса:
18=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = 1 OR flight_id = 2 OR flight_id = 3; QUERY PLAN
--------------------------------------------------------
Index Scan using flights_pkey on flights
Index Cond: (flight_id = ANY ('{1,2,3}'::integer[]))
(2 rows)Планировщик: перестановка выражений в DISTINCT
commit: a8ccf4e93
Еще один пример оптимизации, дающий свободу выбора при написании запроса.
В 17-й версии планировщик научился переставлять местами выражения в GROUP BY в соответствии с положением выражений в индексе. В таблице ticket_flights есть индекс по составному первичному ключу, нас интересует порядок столбцов в индексе.
\d ticket_flights_pkey Index "bookings.ticket_flights_pkey"
Column | Type | Key? | Definition
-----------+---------------+------+------------
ticket_no | character(13) | yes | ticket_no
flight_id | integer | yes | flight_id
primary key, btree, for table "bookings.ticket_flights"В следующем запросе индекс будет выбран вместо полного сканирования таблицы, как было в предыдущих версиях, несмотря на то, что столбцы в GROUP BY перечислены в другом порядке:
17=# EXPLAIN (costs off)
SELECT flight_id,ticket_no
FROM ticket_flights
GROUP BY flight_id,ticket_no; –- столбцы в индексе QUERY PLAN
-------------------------------------------------------------------
Group
Group Key: ticket_no, flight_id
-> Index Only Scan using ticket_flights_pkey on ticket_flights
(3 rows)Продолжение этой оптимизации ― научить планировщик выполнять подобную перестановку для DISTINCT, включая DISTINCT ON. Что и сделано для 18-й версии:
18=# EXPLAIN (costs off)
SELECT DISTINCT flight_id,ticket_no
FROM ticket_flights; QUERY PLAN
-------------------------------------------------------------------
Unique
-> Index Only Scan using ticket_flights_pkey on ticket_flights
(2 rows)Обе оптимизации управляются конфигурационными параметрами и по умолчанию включены:
\dconfig enable_*_reordering List of configuration parameters
Parameter | Value
----------------------------+-------
enable_distinct_reordering | on
enable_group_by_reordering | on
(2 rows)GROUPING SETS: HAVING -> WHERE
commit: 67a54b9e8
В плане следующего запроса важно обратить внимание на строку Filter. Она находится под узлом Seq Scan, а не HashAggregate, как было раньше. Это значит что условие из HAVING теперь проверяется на этапе получения строк из таблицы, т. е. до агрегации.
EXPLAIN (costs off)
SELECT aircraft_code, status, COUNT(*)
FROM flights
GROUP BY GROUPING SETS ((aircraft_code, status), (status))
HAVING status = 'Arrived'; QUERY PLAN
----------------------------------------------------
HashAggregate
Hash Key: status, aircraft_code
Hash Key: status
-> Seq Scan on flights
Filter: ((status)::text = 'Arrived'::text)
(5 rows)Конечно, можно было бы переписать запрос и перенести условие из HAVING в WHERE. Но хорошо что планировщик учится сам находить более эффективные методы выполнения.
Очистка кеша типов данных и временные таблицы
commit: cc5ef90ed, d0f020037, b85a9d046
Временные таблицы стоит использовать очень осторожно. Создание каждой временной таблицы сопровождается записью информации о ней и связанных объектах, например типах, в системный каталог. И это не только потенциальное распухание системных таблиц, но и необходимость поддерживать в актуальном состоянии кеш системного каталога в каждом клиентском процессе.
В следующем примере два анонимных блока последовательно запускаются в одном сеансе. В первом блоке создается 20 тысяч временных таблиц. Хитрая конструкция значения во фразе VALUES нужна для того, чтобы обратиться к только что созданному типу для временной таблицы и поместить его в кеш. Во втором блоке временные таблицы удаляются. Вместе с таблицами удаляются и связанные с ними типы, а также очищается кеш системного каталога. Именно очистка кеша типов была оптимизирована в этом патче.
\timing on
DO $$BEGIN
FOR i IN 1 .. 20_000 LOOP
EXECUTE format('CREATE TEMP TABLE t%s (id int)', i);
EXECUTE format('INSERT INTO t%s VALUES ( (ROW(0)::t%s).id)', i, i);
END LOOP;
END; $$;
DO $$BEGIN
FOR i IN 1 .. 20_000 LOOP
EXECUTE format('DROP TABLE t%s', i);
END LOOP;
END; $$;Время выполнения каждого из блоков в 17-й версии:
Time: 12251,384 ms (00:12,251)
Time: 22494,163 ms (00:22,494)Тоже самое в 18-й версии:
Time: 2872,074 ms (00:02,872)
Time: 1495,051 ms (00:01,495)Ускорение более чем очевидно, особенно для второго анонимного блока.
Планировщик: инкрементальная сортировка при соединении слиянием
commit: 828e94c9d
Как удачно пошутил Томаш Вондра, инкрементальная сортировка добавляется инкрементально.
Теперь в соединении слиянием для внешнего набора строк (в плане расположен выше) может использоваться инкрементальная сортировка, если набор уже частично сортирован.
В описании патча есть такой пример:
CREATE TABLE t (a int, b int);
INSERT INTO t SELECT random(1,100), random(1,100) FROM generate_series(1,100000);
CREATE INDEX ON t (a);
ANALYZE t;
EXPLAIN (costs off)
SELECT *
FROM (SELECT * FROM t ORDER BY a) t1
JOIN t t2 ON t1.a = t2.a AND t1.b = t2.b
ORDER BY t1.a, t1.b; QUERY PLAN
-----------------------------------------------
Merge Join
Merge Cond: ((t.a = t2.a) AND (t.b = t2.b))
-> Incremental Sort
Sort Key: t.a, t.b
Presorted Key: t.a
-> Index Scan using t_a_idx on t
-> Sort
Sort Key: t2.a, t2.b
-> Seq Scan on t t2
(9 rows)В предыдущих версиях для внешнего набора используется узел Sort с полной сортировкой, что может быть менее эффективно:
QUERY PLAN
-----------------------------------------------
Merge Join
Merge Cond: ((t.a = t2.a) AND (t.b = t2.b))
-> Sort
Sort Key: t.a, t.b
-> Index Scan using t_a_idx on t
-> Materialize
-> Sort
Sort Key: t2.a, t2.b
-> Seq Scan on t t2
(9 rows)Новая функция array_reverse
commit: 49d6c7d8d
Назначение функции следует из названия. Функция возвращает элементы входного массива в обратном порядке:
SELECT array_reverse(string_to_array('PostgreSQL',NULL)); array_reverse
-----------------------
{L,Q,S,e,r,g,t,s,o,P}
(1 row)См. также
Waiting for PostgreSQL 18 – Add SQL function array_reverse() (Hubert 'depesz' Lubaczewski)
Функции min и max для типа bytea
commit: 2d24fd942
Как и следует из названия, для сортируемого типа данных bytea добавили отсутствующие функции min и max:
WITH t AS (
SELECT book_ref::bytea AS c FROM bookings
)
SELECT min(c), max(c) FROM t; min | max
----------------+----------------
\x303030303034 | \x464646464639
(1 row)Статистика использования параллельных рабочих процессов
commit: cf54a2c00, e7a9496de
В статистические представления pg_stat_statements (первый коммит) и pg_stat_database (второй коммит) добавлены два столбца: parallel_workers_to_launch и parallel_workers_launched. Они показывают количество запланированных параллельных рабочих процессов и сколько из них было реально запущено.
Посмотрим на столбцы в деле. Настройки параллельного выполнения не менялись:
\dconfig max_parallel_workers*|max_worker* List of configuration parameters
Parameter | Value
---------------------------------+-------
max_parallel_workers | 8
max_parallel_workers_per_gather | 2
max_worker_processes | 8
(3 rows)Сбросим статистику перед тестом:
SELECT pg_stat_statements_reset();
SELECT pg_stat_reset();Запустим через pgbench 10 процессов, каждый из которых будет выполнять запросы с параллельным планом:
$ pgbench --client=10 --time=5 --file=test.sqlФайл test.sql содержит два запроса на агрегирование таблиц достаточно большого размера, чтобы был выбран параллельный план:
$ cat test.sqlSELECT count(*) FROM bookings;
SELECT count(*) FROM tickets;Но всем 10 клиентам не должно хватить запланированного числа рабочих процессов. В этом можно убедиться, посмотрев статистику на уровне отдельных запросов:
SELECT query,
parallel_workers_to_launch AS plan,
parallel_workers_launched AS fact
FROM pg_stat_statements
WHERE query ~ 'tickets|bookings'; query | plan | fact
-------------------------------+------+------
SELECT count(*) FROM bookings | 84 | 42
SELECT count(*) FROM tickets | 84 | 40
(2 rows)А также более общую статистику базы данных:
SELECT parallel_workers_to_launch AS plan,
parallel_workers_launched AS fact
FROM pg_stat_database
WHERE datname = current_database(); plan | fact
------+------
168 | 82
(1 row)Если плановые значения стабильно превышают фактические, возможно это повод пересмотреть настройки параллельного выполнения, конечно при наличии аппаратных возможностей.
Новая функция pg_ls_summariesdir
commit: 4e1fad378
Для мониторинга работы инкрементального резервного копирования пригодится новая функция pg_ls_summariesdir, показывающая содержимое каталога pg_wal/summaries, включая размер файлов и дату последнего изменения.
SELECT * FROM pg_ls_summariesdir(); name | size | modification
--------------------------------------------------+-------+------------------------
0000000100000001412CF0980000000162404D08.summary | 17164 | 2025-02-03 15:22:33+03
(1 row)Именно показ информации о размере файлов и дате последнего изменения отличают эту функцию от вызова pg_ls_dir('pg_wal/summaries'), которая выдает только имена файлов.
И на всякий случай можно напомнить о том, что семейство функций для просмотра содержимого служебных каталогов достаточно внушительное:
\df pg_ls_*dir List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------------------+------------------+-------------------------------------------------------------------------------------------+------
pg_catalog | pg_ls_archive_statusdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_dir | SETOF text | text | func
pg_catalog | pg_ls_dir | SETOF text | text, boolean, boolean | func
pg_catalog | pg_ls_logdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_logicalmapdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_logicalsnapdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_replslotdir | SETOF record | slot_name text, OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_summariesdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_tmpdir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_tmpdir | SETOF record | tablespace oid, OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
pg_catalog | pg_ls_waldir | SETOF record | OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
(11 rows)pg_logicalsnapinspect: новый модуль contrib
commit: 7cdfeee32
Новый модуль pg_logicalsnapinspect предназначен для проверки содержимого компонентов логического декодирования. Полезен для целей отладки или изучения работы логической репликации.
В настоящий момент включает две функции pg_get_logical_snapshot_meta и pg_get_logical_snapshot_info. Функции выдают информацию о снимках данных из файлов, расположенных в $PGDATA/pg_logical/snapshots.
Уточненные сообщения об ошибках при установке расширений
commit: 14e5680ee, 774171c4f, 924e03917
Для демонстрации возьмем расширение uom, для которого доступно три версии:
SELECT name, version
FROM pg_available_extension_versions
WHERE name = 'uom'; name | version
------+---------
uom | 1.0
uom | 1.1
uom | 1.2
(3 rows)В один из установочных скриптов SQL намеренно внесена ошибочная команда. Попробуем установить последнюю версию расширения:
17=# CREATE EXTENSION uom VERSION '1.2';ERROR: division by zeroОшибка! Но в каком файле?
В 18-й версии сообщение и контекст ошибок при установке расширений значительно улучшены:
18=# CREATE EXTENSION uom VERSION '1.2';ERROR: division by zero
CONTEXT: SQL statement "SELECT 1/0"
extension script file "uom--1.1--1.2.sql", near line 9Ограничения NOT NULL в системном каталоге
commit: 14e87ffa5
Это вторая попытка записывать ограничения NOT NULL в системный каталог pg_constraint. Первая попытка была в 17-й версии, однако патч был отменен. Но описание патча осталось актуальным.
Таблица TOAST для pg_index
commit: b52c4fc3c
Для таблицы системного каталога pg_index не было таблицы TOAST. Значения столбцов не были слишком длинными. Теперь у pg_index есть TOAST:
SELECT reltoastrelid::regclass
FROM pg_class
WHERE oid = 'pg_index'::regclass; reltoastrelid
------------------------
pg_toast.pg_toast_2610
(1 row)Изменение понадобится в первую очередь для индексов по выражениям, где выражение может быть достаточно длинным.
COPY… FROM и file_fdw: ограничение на количество ошибочных строк
commit: 4ac2a9bec, a39297ec0, 6c8f67032
Теперь можно не просто игнорировать ошибки преобразования форматов, но и указать в новом параметре REJECT_LIMIT, сколько ошибочных строк допускается при загрузке данных командой COPY.
CREATE TABLE t (id int PRIMARY KEY);
COPY t FROM STDIN (on_error 'ignore', log_verbosity verbose, reject_limit 1);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> один
>> два
>> 3
>> \.NOTICE: skipping row due to data type incompatibility at line 2 for column "id": "один"
NOTICE: skipping row due to data type incompatibility at line 3 for column "id": "два"
ERROR: skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
CONTEXT: COPY t, line 3, column id: "два"Превышение ограничения приводит к ошибке.
Аналогичные изменения сделаны для расширения file_fdw (третий коммит). В предыдущей статье рассказывалось о том, что расширение научилось обрабатывать ошибки преобразования форматов. Теперь, вслед за COPY, можно установить предельное количество ошибок при помощи параметра REJECT_LIMIT.
Недетерминированные правила сортировки: поддержка LIKE
commit: 85b7efa1c
С недетерминированными правилами сортировки строки, состоящие из разных байтов, могут быть равными. В следующем примере определяется регистронезависимое правило сортировки. Для такого правила строки в разных регистрах равны:
CREATE COLLATION ignore_case
(provider = icu, locale = 'und-u-ks-level2', deterministic = false);
SELECT 'postgres' = 'POSTGRES' COLLATE "ignore_case" AS "ignore_case"; ignore_case
-------------
t
(1 row)Но вот операция LIKE для таких строк раньше не поддерживалась. Исправлено:
SELECT 'PostgreSQL' LIKE 'post%' COLLATE "ignore_case"; ?column?
----------
t
(1 row)См. также
Waiting for PostgreSQL 18 – Support LIKE with nondeterministic collations (Hubert 'depesz' Lubaczewski)
TLS v1.3: настройка наборов шифров
commit: 45188c2ea, 3d1ef3a15, 6c66b7443
Добавлен новый параметр ssl_tls13_ciphers для указания списка набора шифров, когда используется TLS версии 1.3. Для предыдущих версий используется прежний параметр ssl_ciphers.
На этом пока всё. Впереди основные события январского коммитфеста.