Эта статья открывает цикл о новостях будущей, 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, не являясь суперпользователями, смогут отправлять рабочим процессам автоочистки сигнал на завершение очистки текущей таблицы или сигнал на прекращение работы, используя эти же две функции.

---

На этом пока всё. Впереди новости сентябрьского коммитфеста.