Эта статья открывает цикл о новостях будущей, 18-ой, версии PostgreSQL. Рассмотрим следующие возможности попавшие в июльский коммитфест.
Планировщик: поддержка правого полусоединения хешированием
Планировщик: материализация внутреннего набора строк для соединения вложенными циклами в параллельном плане
Вспомогательные функции планировщика для generate_series
EXPLAIN (analyze): статистика рабочих процессов узла Parallel Bitmap Heap Scan
Функции min и max для составных типов
Имена параметров для функций regexp*
Режим отладки в pgbench
pg_get_backend_memory_contexts: столбец path вместо parent и новый столбец type
Функция pg_get_acl
pg_upgrade: оптимизация работы pg_dump
Предопределенная роль pg_signal_autovacuum_worker
Планировщик: поддержка правого полусоединения хешированием
commit: aa86129e1
В плане следующего запроса в 17-й версии используется соединение хешированием и последовательное сканирование обеих таблиц:
17=# EXPLAIN (costs off, analyze) SELECT * FROM flights WHERE flight_id IN (SELECT flight_id FROM ticket_flights);
QUERY PLAN ------------------------------------------------------------------------------------------------ Hash Join (actual time=2133.122..2195.619 rows=150588 loops=1) Hash Cond: (flights.flight_id = ticket_flights.flight_id) -> Seq Scan on flights (actual time=0.018..10.301 rows=214867 loops=1) -> Hash (actual time=2132.969..2132.970 rows=150588 loops=1) Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 7343kB -> HashAggregate (actual time=1821.476..2114.218 rows=150588 loops=1) Group Key: ticket_flights.flight_id Batches: 5 Memory Usage: 10289kB Disk Usage: 69384kB -> Seq Scan on ticket_flights (actual time=7.200..655.356 rows=8391852 loops=1) Planning Time: 0.325 ms Execution Time: 2258.237 ms (11 rows)
Но хеш строится по таблице большего размера ticket_flights, что очевидно требует больше ресурсов, чем строить хеш по небольшой таблице flights.
В 18-й версии замена таблицы для хеширования станет возможной, а в плане запроса на 4-й строке мы увидим Hash Right Semi Join, в этом случае еще и в параллельном режиме. Как следствие, время выполнения заметно сокращается:
QUERY PLAN --------------------------------------------------------------------------------------------------- Gather (actual time=56.771..943.233 rows=150590 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Right Semi Join (actual time=41.754..909.894 rows=50197 loops=3) Hash Cond: (ticket_flights.flight_id = flights.flight_id) -> Parallel Seq Scan on ticket_flights (actual time=0.047..221.511 rows=2797284 loops=3) -> Parallel Hash (actual time=40.309..40.309 rows=71622 loops=3) Buckets: 262144 Batches: 1 Memory Usage: 23808kB -> Parallel Seq Scan on flights (actual time=0.008..6.631 rows=71622 loops=3) Planning Time: 0.555 ms Execution Time: 949.831 ms (11 rows)Планировщик: материализация внутреннего набора строк для соединения вложенными циклами в параллельном плане
commit: 22d946b0f
До 18-й версии планировщик не рассматривал возможность материализации внутреннего набора строк для соединения вложенными циклами в параллельном режиме.
17=# EXPLAIN (costs off) SELECT * FROM ticket_flights tf JOIN flights f USING (flight_id) WHERE f.flight_id = 12345;
QUERY PLAN ---------------------------------------------------- Nested Loop -> Index Scan using flights_pkey on flights f Index Cond: (flight_id = 12345) -> Gather Workers Planned: 2 -> Parallel Seq Scan on ticket_flights tf Filter: (flight_id = 12345) (7 rows)
План этого же запроса в 18-й версии использует материализацию рейсов:
QUERY PLAN -------------------------------------------------------------- Gather Workers Planned: 2 -> Nested Loop -> Parallel Seq Scan on ticket_flights tf Filter: (flight_id = 12345) -> Materialize -> Index Scan using flights_pkey on flights f Index Cond: (flight_id = 12345) (8 rows)
Надо отметить, что производительность запроса с новым планом особо не изменилась. В обоих случаях, по сути, выполняются те же действия. Но у планировщика появилось больше возможностей для выбора оптимального плана в других ситуациях, что не может не радовать.
Вспомогательные функции планировщика для generate_series
commit: 036bdcec9
Новая вспомогательная функция подсказывает планировщику, сколько строк вернет generate_series для диапазона дат и времени:
EXPLAIN SELECT * FROM generate_series(current_date, current_date + '1 day'::interval, '1 hour');
QUERY PLAN --------------------------------------------------------------------- Function Scan on generate_series (cost=0.01..0.26 rows=25 width=8) (1 row)
В предыдущих версиях количество строк всегда оценивалось в 1000 (значение по умолчанию параметра ROWS для функций).
Заметим, что аналогичная вспомогательная функция для целых чисел уже давно существует:
EXPLAIN SELECT * FROM generate_series(1, 42, 2);
QUERY PLAN --------------------------------------------------------------------- Function Scan on generate_series (cost=0.00..0.21 rows=21 width=4) (1 row)EXPLAIN (analyze): статистика рабочих процессов узла Parallel Bitmap Heap Scan
commit: 5a1e6df3b
Команда EXPLAIN теперь показывает статистику (количество точных и неточных фрагментов) для каждого рабочего процесса, участвующего в параллельном сканировании по битовой карте. В следующем примере это строки, начинающиеся с Worker 0 и Worker 1.
EXPLAIN (analyze, costs off, timing off, summary off) SELECT count(*) FROM bookings WHERE total_amount < 20000 AND book_date > '2017-07-15';
QUERY PLAN ----------------------------------------------------------------------------------------------------- Finalize Aggregate (actual rows=1 loops=1) -> Gather (actual rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (actual rows=1 loops=3) -> Parallel Bitmap Heap Scan on bookings (actual rows=6415 loops=3) Recheck Cond: (total_amount < '20000'::numeric) Filter: (book_date > '2017-07-15 00:00:00+03'::timestamp with time zone) Rows Removed by Filter: 67072 Heap Blocks: exact=6345 Worker 0: Heap Blocks: exact=3632 Worker 1: Heap Blocks: exact=3470 -> Bitmap Index Scan on bookings_total_amount_idx (actual rows=220460 loops=1) Index Cond: (total_amount < '20000'::numeric)Функции min и max для составных типов
commit: a0f1fce80
Значения составных типов уже давно можно сравнивать между собой. Сравнение выполняется поэлементно. Например отсортируем строки в следующем примере:
CREATE TABLE seats( line text, number integer ); INSERT INTO seats VALUES ('A', 42), ('B', 1), ('C', 27); SELECT * FROM seats s ORDER BY s DESC;
line | number ------+-------- C | 27 B | 1 A | 42 (3 rows)
Однако агрегатных функций min и max для составных типов не было. Их и добавили:
SELECT min(s.*), max(s.*) FROM seats s;
min | max --------+-------- (A,42) | (C,27) (1 row)Имена параметров для функций regexp*
commit: 580f8727
Функции для работы с регулярными выражениями обзавелись именами параметров для большей наглядности:
SELECT regexp_replace( pattern => '$', replacement => 'Postgres!', string => 'All You Need Is ' );
regexp_replace --------------------------- All You Need Is Postgres!
Имена параметров можно найти в документации или в выводе команды \df в psql. Раньше был доступен только позиционный способ передачи параметров.
Режим отладки в pgbench
commit: 3ff01b2b
Большинство утилит сервера используют параметр -d для указания базы данных для подключения. Однако в pgbench этот параметр включал режим отладки. Для унификации параметры pgbench изменили: теперь задать базу данных можно в -d или —dbname, а режим отладки включается только с полным именем —debug.
Изменение доступно с версии 17, хотя формально завершено в июльском коммитфесте 18-й версии.
pg_get_backend_memory_contexts: столбец path вместо parent и новый столбец type
commit: 32d3ed81, f0d11275, 12227a1d
Разработчики сервера и любители исследовать использование памяти оценят изменения в представлении pg_backend_memory_contexts.
Стало удобнее и надежнее связывать родительские и дочерние контексты между собой. Для этого добавлен столбец path, представляющий собой массив идентификаторов контекстов. Первый элемент массива ― всегда контекст верхнего уровня TopMemoryContext, а последний элемент ― идентификатор контекста текущей строки. Также, для удобства написания запросов поправили нумерацию в столбце level, теперь она начинается с 1, а не 0.
Пример запроса, показывающего контекст памяти TopPortalContext и все его дочерние контексты.
WITH memory_contexts AS ( SELECT * FROM pg_backend_memory_contexts ) SELECT child.name, child.type, child.level, child.path, child.total_bytes FROM memory_contexts parent, memory_contexts child WHERE parent.name = 'TopPortalContext' AND child.path[parent.level] = parent.path[parent.level];
name | type | level | path | total_bytes --------------------------------+------------+-------+-----------------------+------------- TopPortalContext | AllocSet | 2 | {1,20} | 8192 PortalContext | AllocSet | 3 | {1,20,31} | 1024 ExecutorState | AllocSet | 4 | {1,20,31,136} | 65632 tuplestore tuples | Generation | 5 | {1,20,31,136,138} | 32768 printtup | AllocSet | 5 | {1,20,31,136,139} | 8192 ExprContext | AllocSet | 5 | {1,20,31,136,140} | 8192 ExprContext | AllocSet | 5 | {1,20,31,136,141} | 8192 tuplestore tuples | Generation | 5 | {1,20,31,136,142} | 8192 ExprContext | AllocSet | 5 | {1,20,31,136,143} | 8192 Table function arguments | AllocSet | 5 | {1,20,31,136,144} | 8192 ExprContext | AllocSet | 5 | {1,20,31,136,145} | 32768 pg_get_backend_memory_contexts | AllocSet | 6 | {1,20,31,136,145,146} | 16384 (12 rows)
Раньше для связывания контекстов использовался столбец parent (удален во втором коммите), указывающий на name. Требовался более сложный рекурсивный запрос. Но более важно, что эта связка работала менее надежно, т. к. имена контекстов могли быть не уникальными.
Кроме того, в представление добавлен столбец type (третий коммит). Сейчас используются четыре типа контекста: AllocSet, Generation, Slab, Bump. Подробнее о контекстах памяти можно прочитать в исходном коде: src/backend/utils/mmgr/README.
Функция pg_get_acl
commit: 4564f1ce, e311c6e5, d898665b
После выполнения следующих команд появляется зависимость таблицы test от роли alice.
CREATE ROLE alice; CREATE TABLE test (id int); GRANT SELECT ON test TO alice;
Теперь роль alice нельзя удалить, без удаления зависимости от таблицы:
DROP ROLE alice;
ERROR: role "alice" cannot be dropped because some objects depend on it DETAIL: privileges for table test
Информация о зависимостях объектов хранится в двух системных каталогах pg_depend (между объектами внутри одной БД) и pg_shdepend, когда в зависимостях участвуют общие объекты кластера, например роли, как в нашем случае.
Сейчас в pg_shdepend ровно одна строка об этой зависимости:
SELECT * FROM pg_shdepend\gx
-[ RECORD 1 ]----- dbid | 5 classid | 1259 objid | 16528 objsubid | 0 refclassid | 1260 refobjid | 16527 deptype | a
Расшифровать информацию из pg_shdepend не так просто, поскольку объекты в ней представлены в виде идентификаторов: идентификатор таблицы системного каталога (classid), идентификатор строки в этом системном каталоге (objid) и порядковый номер столбца для таблиц (objsubid). Объекты, на которые есть ссылки, представлены соответственно refclassid и refobjid (ссылок на столбцы таблиц не бывает, поэтому нет столбца refobjsubid).
Для удобства работы с объектами по идентификаторам предусмотрен ряд функций. Например функция pg_identify_object выводит запись об объекте:
SELECT * FROM pg_identify_object(1259,16528,0);
type | schema | name | identity -------+--------+------+------------- table | public | test | public.test
Объект, от которого таблица зависит:
SELECT * FROM pg_identify_object(1260,16527,0);
type | schema | name | identity ------+--------+-------+---------- role | | alice | alice
В этом семействе функций произошло пополнение. Новая функция pg_get_acl показывает права доступа к объекту, без необходимости запроса к конкретному системному каталогу.
Следующий запрос покажет, какие именно привилегии выданы alice:
SELECT *, pg_identify_object(classid, objid, objsubid) AS object, pg_identify_object(refclassid, refobjid, 0) AS ref_object, pg_get_acl(classid, objid, objsubid) FROM pg_shdepend\gx
-[ RECORD 1 ]--------------------------------------------- dbid | 5 classid | 1259 objid | 16528 objsubid | 0 refclassid | 1260 refobjid | 16527 deptype | a object | (table,public,test,public.test) ref_object | (role,,alice,alice) pg_get_acl | {postgres=arwdDxtm/postgres,alice=r/postgres}pg_upgrade: оптимизация работы pg_dump
commit: 6e16b1e4, 64f34eb2e, 2329cad1b, 23687e925, 68e962998, c8b06bb96, bd15b7db4, 6ebeeae29
Сразу несколько оптимизаций сделано для обновления сервера. Их есть смысл рассмотреть вместе. Все так или иначе связаны с работой утилиты pg_dump, которая используется в процессе обновления сервера для выгрузки содержимого системных каталогов.
Первая заключается в том, что pg_upgrade будет запускать pg_dump с параметром —no-sync. При обновлении сервера не требуется гарантия записи файлов на диск, ведь если что-то пойдет не так, то всю процедуру нужно повторять заново. А сэкономить на синхронизации файлов с диском можно.
Благодаря второй оптимизации, команды на создание баз данных будут использовать стратегию FILE_COPY вместо используемой по умолчанию WAL_LOG:
CREATE DATABASE .. STRATEGY = FILE_COPY;
При этой стратегии содержимое шаблонной базы не записывается в журнал, а надежность гарантируется выполнением контрольных точек перед копированием файлов и после него. При обычной работе это менее эффективно, но в режиме двоичного обновления (binary upgrade) сервер не будет выполнять лишние контрольные точки (потребуется только одна, после создания шаблонной базы template0).
Эффективность этих двух оптимизаций будет расти с количеством баз данных в обновляемом кластере.
Третья оптимизация ускоряет выгрузку описания таблиц в режиме pg_dump —binary-upgrade. Именно в этом режиме pg_upgrade запускает pg_dump. Ускорение достигается за счет того, что в начале своей работы pg_dump одним запросом к pg_class получает информацию о всех таблицах и сохраняет ее в упорядоченном массиве в памяти. В предыдущих версиях при выгрузке каждой таблицы выполняется отдельный запрос к pg_class, на что тратится много ресурсов.
Четвертая оптимизация ускоряет выгрузку последовательностей. Тем же способом, что и выгрузку таблиц. В начале работы pg_dump сохраняет информацию о последовательностях в упорядоченном массиве. В дальнейшем обращаться к системному каталогу не требуется. Но эта оптимизация работает не только в режиме —binary-upgrade, но и в обычном.
Третья и четвертая оптимизации не совсем бесплатны. Для хранения информации о таблицах и последовательностях придется выделять дополнительную оперативную память. Но разработчики уверены, что такие накладные расходы вполне оправданы.
В базах данных с небольшим количеством таблиц и последовательностей особого эффекта от последних двух оптимизаций не будет. Но если говорить о системах с десятками, сотнями тысяч таблиц и последовательностей, то ускорение более чем заметно.
Для проверки создадим в отдельной базе данных 17-й версии 100 000 таблиц со столбцом, к которому привязана последовательность. Повторим эти же действия в базе данных 18-й версии:
$ psql -d test -o /dev/null <<EOF SELECT format('CREATE TABLE t%s (id int GENERATED ALWAYS AS IDENTITY)', g.i) FROM generate_series(1,100_000) AS g(i) \gexec EOF
А теперь измерим, сколько времени потребуется на работу pg_dump в режиме —binary-upgrade.
Версия 17:
$ time pg_dump -d test --schema-only --binary-upgrade > /dev/null
real 1m40,677s user 0m5,403s sys 0m3,265s
Версия 18:
$ time pg_dump -d test --schema-only --binary-upgrade > /dev/null
real 0m7,427s user 0m2,220s sys 0m0,264s
Ускорение более чем на порядок.
Предопределенная роль pg_signal_autovacuum_worker
commit: ccd38024b
Роли, включенные в предопределенную роль pg_signal_backend, могут отправлять процессам сервера сигнал на прерывание текущего запроса (pg_cancel_backend) или даже сигнал на прерывание работы (pg_terminate_backend). С одним ограничением. Эти сигналы нельзя посылать процессам суперпользователя.
Однако при сопровождении сервера могут возникать ситуации, когда требуется прервать работу процессов автоочистки. Члены новой предопределенной роли pg_signal_autovacuum_worker, не являясь суперпользователями, смогут отправлять рабочим процессам автоочистки сигнал на завершение очистки текущей таблицы или сигнал на прекращение работы, используя эти же две функции.
На этом пока всё. Впереди новости сентябрьского коммитфеста.
ссылка на оригинал статьи https://habr.com/ru/articles/856498/
Добавить комментарий