Это продолжении серии статей о принятых изменениях в 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.sql
SELECT 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.
На этом пока всё. Впереди основные события январского коммитфеста.
ссылка на оригинал статьи https://habr.com/ru/articles/882578/
Добавить комментарий