Расширение pg_variables

Часто при разрабоке прикладного ПО можно столкнуться с проблемой такого рода — для промежуточных данных требуется получить несколько результирующих наборов, например, для некоторых товаров надо иметь возможность получить их наличие в текущих заказах и сумму скидок, выданных для них ранее; или для некоторых пользователей получить список их друзей и сообщения этих пользователей в соцсетях и т.д и т.п.

Решение обычно выглядит вполне прямолинейным — сначала получаем список, скажем, пользователей, потом для них строим требуемый результирующий набор; потом опять получаем список пользователей и строим второй набор; и все бы хорошо, если бы построение такого списка не оказывалось бы достаточно затратной операцией — и, таким образом, если на основании этого списка надо построить несколько результатов, то получается, что этот список надо получить несколько раз со всеми сопутствующими накладными расходами. Очевидным решением этой проблемы кажутся временные таблицы, и это действительно так; к сожалению, с ними связан ряд не самых приятных особенностей — для каждой временной таблицы требуется создавать файл (а при уничтожении таблицы — удалять его). Кроме того, эти таблицы, разумеется, не видны для процессов автовакуума и, следовательно, не очищаются автоматически, и по ним не собирается статистика. Что еще хуже, при наличии длительных активных транзакций может происходить неограниченный рост системного каталога; более того, кеш операционной системы заполняется данными о созданных файлах для временных таблиц, что ведет к общей деградации производительности.

Следует также отметить, что так как имя таблицы должно быть известно при компиляции запроса, то использование разных таблиц может оказаться достаточно неуклюжим и заставляет прибегнуть к динамическому формированию запросов со всеми вытекающими последствиями; если же вспомнить, что plpgsql для динамических запросов не сохраняет план, то в случаях сложных запросов это может оказаться значительной проблемой.

Другое напрашивающееся решение — массивы, но и у них есть ряд недостатков. Во-первых, массивы иммутабельны; для небольших массивов это не страшно, а вот для больших может оказаться довольно неприятным; во-вторых, массивы существуют только во время выполнения запроса, а не все время жизни сессии; и, в-третьих, массивы не позволяют искать по ключу.

Еще для сессии нередко требуются временные данные, доступные для всех функций на время выполнения запроса — текущий пользователь, права и т.д.

Наконец, при выполнении read-only запросов на реплике нередко где-то требуется хранить временные данные.

Для избежания подобных проблем и предназначен описываемое расширение.

Что же оно делает?

Во-первых, оно позволяет определять скалярные сессионные переменные, что само по себе может оказаться очень ценным — например, там можно хранить идентификатор пользователя, от имени которого выполняется запрос, его, пользователя, различные атрибуты. Например:

select pgv_set_int('package','usr_id',1)

и потом:

select pgv_get_int('package','usr_id')

Здесь (и далее) package — имя пакета, usr_id — имя переменной в этом пакете. Разумеется, таких переменных может быть много:

select pgv_set_int('package','#'||n,n), n from generate_series(1,1000000) as gs(n)

Кроме типа integer могут быть и другие — text, numeric, timestamp, timestamptz, date и jsonb. Все эти переменные существуют во время жизни сессии, их установившей, и недоступны для других.

Кроме скалярных переменных данное расширение поддерживает и наборы.

Здесь и далее используется тестовая база данных из трех таблиц, представляющая гипотетических пользователй (ord.usr), товары (ord.goods) и скидки для пользователей на товары (ord.discount). База была создана с помощью утилиты datafiller (https://www.cri.ensmp.fr/people/coelho/datafiller.html) со следующим файлом настроек:

CREATE TABLE ord.goods( -- df: mult=1000.0
    id SERIAL primary key,
    name TEXT NOT NULL, -- df: lenmin=3 lenmax=30 chars='a-f ' sub=uniform 
    price numeric, -- df: float=gauss alpha=100.0 beta=30
    in_stock_qty int -- df: size=1000
);
create table ord.usr( -- df: mult=100
   id serial primary key,
   email text -- df: pattern='[a-z]{3,16}\.[a-z]{3,8}@((gmail|yahoo|mail)\.com|(mail|yandex|inbox)\.ru)'
);
create table ord.discount( -- df: mult=100
  goods_id int not null references ord.goods(id),
  usr_id int not null references ord.usr(id),
  pct numeric not null, -- df: alpha=0.01 beta=0.07
  from_date date not null, -- df: start=2010-01-01 end=2016-04-01
  duration integer not null -- df: offset=1 size=361 step=30
)

Посмотрим поподробнее. Сначала построим список всех пользователй mail.ru:

select pgv_insert('package', 'set', row(u.id, u.email)) from ord.usr u where u.email like '%@mail.ru'

Насколько это дорого?

                                                 QUERY PLAN                                                 
 Seq Scan on usr u  (cost=0.00..2041.96 rows=23984 width=30) (actual time=0.022..24.893 rows=16426 loops=1)
   Filter: (email ~~ '%@mail.ru'::text)
   Rows Removed by Filter: 83574
 Planning time: 0.070 ms
 Execution time: 25.404 ms
(5 строк)

Их можно получить как:

explain analyze
select * from pgv_select('package','set') as usr(id int, email text)

Какова производительность этой операции? Давайте посмотрим:

 Function Scan on pgv_select usr  (cost=0.00..10.00 rows=1000 width=36) (actual time=4.692..5.503 rows=16426 loops=1)
 Planning time: 0.026 ms
 Execution time: 10.733 ms
(3 строки)

Сравним с обычной выборкой:

create temporary table usr_id_email(
  id int primary key,
  email text
);

и

explain analyze
insert into usr_id_email 
select u.id, u.email from ord.usr u where u.email like '%@mail.ru'
Выполнение:
Insert on usr_id_email  (cost=0.00..1982.00 rows=23984 width=30) (actual time=31.244..31.244 rows=0 loops=1)
  ->  Seq Scan on usr u  (cost=0.00..1982.00 rows=23984 width=30) (actual time=0.007..16.220 rows=16426 loops=1)
        Filter: (email ~~ '%@mail.ru'::text)
        Rows Removed by Filter: 83574
Planning time: 0.069 ms
Execution time: 31.285 ms

Как видим, время выполнения pgv_insert заметно меньше чем у варианта с временной таблицей; помимо того, время выполнения варианта с временной таблицей в значительной степени зависит от состояния кеша ОС, так как, как уже отмечалось выше, для каждой временной таблицы создается файл (а при удалении, соотственно, удаляется).

Если внимательно посмотреть на приведенный выше код, то можно сделать вполне справедливое замечание — в варианте с временной таблицей колонка id является первичным ключом; насколько это честно по отношению к pgv_insert? В общем, совершенно честно: из результата, построенного pgv_insert также можно получить строку по id пользователя:

select * from pgv_select('package','set',9545) as t(id int, email text)

При сохранении результирующего набора первая колонка в добавляемых строках служит первичным ключом. Соответственно, она во-первых, должна быть уникальной, и, во-вторых, может использоваться для быстрого поиска строк.

Насколько выборка быстра по отношению ко временной таблице? Сравним:

explain analyze
select * 
  from generate_series(1,1000) as gs(n) 
       left outer join pgv_select('package','set') as t(id int, email text) on true
План:
Nested Loop Left Join  (cost=0.01..20010.01 rows=1000000 width=40) (actual time=10.282..2495.984 rows=16426000 loops=1)
  ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.171..0.279 rows=1000 loops=1)
  ->  Function Scan on pgv_select t  (cost=0.00..10.00 rows=1000 width=36) (actual time=0.010..0.817 rows=16426 loops=1000)
Planning time: 0.061 ms
Execution time: 2991.351 ms

и

explain analyze
select * 
  from generate_series(1,1000) as gs(n) left outer join usr_id_email on true
План:
Nested Loop Left Join  (cost=0.00..189230.42 rows=15113000 width=40) (actual time=0.172..2390.766 rows=16426000 loops=1)
'  ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.159..0.288 rows=1000 loops=1)
  ->  Materialize  (cost=0.00..345.69 rows=15113 width=36) (actual time=0.000..0.738 rows=16426 loops=1000)
        ->  Seq Scan on usr_id_email  (cost=0.00..270.13 rows=15113 width=36) (actual time=0.010..2.660 rows=16426 loops=1)
Planning time: 0.076 ms
Execution time: 2874.250 ms

Как видно, время вполне сравнимое — одна операция оказывается медленнее по сравнению с временной таблицей примерно на 0.1 мсек.
Какова же скорость доступа к конкретной строке по сравнению с временными таблицами? Посмотрим:

В одном случае pgv_select можно указать первичный ключ строки, которая нам требуется:

explain analyze
select * from usr_id_email uie 
 where exists (select * from pgv_select('package','set',uie.id) as t(id int, email text))
План:
Seq Scan on usr_id_email uie  (cost=0.00..459.04 rows=7556 width=36) (actual time=0.021..19.947 rows=16426 loops=1)
  Filter: (SubPlan 1)
  SubPlan 1
    ->  Function Scan on pgv_select t  (cost=0.00..1000.00 rows=100000 width=0) (actual time=0.001..0.001 rows=1 loops=16426)
Planning time: 0.047 ms
Execution time: 20.704 ms

Сравним с нашей временной таблицей:

set enable_hashjoin=false;
set enable_mergejoin=false;
explain analyze
select * from usr_id_email uie 
 where exists (select * from usr_id_email uie2 where uie.id=uie2.id)

Nested Loop Semi Join  (cost=0.29..5620.94 rows=15113 width=36) (actual time=0.016..17.227 rows=16426 loops=1)
  ->  Seq Scan on usr_id_email uie  (cost=0.00..270.13 rows=15113 width=36) (actual time=0.007..1.130 rows=16426 loops=1)
  ->  Index Only Scan using usr_id_email_pkey on usr_id_email uie2  (cost=0.29..0.34 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=16426)
        Index Cond: (id = uie.id)
        Heap Fetches: 16426
Planning time: 0.082 ms
Execution time: 17.976 ms

Как видим, время вполне сравнимо; правда, пришлось отключить некоторые способы соединения. Кстати, если их включить, то можно рассмотреть другой вариант, и его результат в нашем случае получается заметно лучше:

Hash Semi Join  (cost=459.04..936.98 rows=15113 width=36) (actual time=5.171..12.703 rows=16426 loops=1)
  Hash Cond: (uie.id = uie2.id)
  ->  Seq Scan on usr_id_email uie  (cost=0.00..270.13 rows=15113 width=36) (actual time=0.008..1.857 rows=16426 loops=1)
  ->  Hash  (cost=270.13..270.13 rows=15113 width=4) (actual time=5.150..5.150 rows=16426 loops=1)
        Buckets: 32768 (originally 16384)  Batches: 1 (originally 1)  Memory Usage: 513kB
        ->  Seq Scan on usr_id_email uie2  (cost=0.00..270.13 rows=15113 width=4) (actual time=0.003..2.417 rows=16426 loops=1)
Planning time: 0.107 ms
Execution time: 13.603 ms

Но это все в значительной степени теоретические размышления, и стоит посмотреть, что будет в случае более-менее реальной нагрузки. Воспользуемся для этого утилитой pgbench, но сначала создадим следующие функции. Простой запрос:

CREATE OR REPLACE FUNCTION public.get_mailru_discounts_plain()
  RETURNS TABLE(usr_cnt integer, discounts_cnt integer) AS
$BODY$
begin
  select count(*) into usr_cnt from ord.usr u where u.email like 'ab%@mail.ru';
  select count(*) 
    into discounts_cnt 
    from ord.discount d, ord.usr u 
   where u.email like 'ab%@mail.ru' 
     and d.usr_id=u.id;
  return next;
end;
$BODY$
  LANGUAGE plpgsql;

Использование временной таблицы:

CREATE OR REPLACE FUNCTION public.get_mailru_discounts_array()
  RETURNS TABLE(usr_cnt integer, discounts_cnt integer) AS
$BODY$
declare
 ids int[];
begin
  select array_agg(id) into ids from ord.usr u where u.email like 'ab%@mail.ru';
  get diagnostics usr_cnt = row_count;
  select count(*) into discounts_cnt from ord.discount d where d.usr_id=any(ids);
  return next;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Использование расширения:

create or replace function get_mailru_discounts_pgvariables() returns table(usr_cnt int, discounts_cnt int) as
$code$
begin
  if exists(select * from pgv_list() where package='package' and name='set') then
    perform pgv_remove('package','set');
  end if;
  perform pgv_insert('package', 'set', row(id)) from ord.usr u where u.email like '%@mail.ru';
  get diagnostics usr_cnt = row_count;
  select count(*) into discounts_cnt 
    from ord.discount d, pgv_select('package','set') u(id int) where d.usr_id=u.id;
  return next;
end;
$code$
language plpgsql;

Файлы с запросом просты:

select * from get_mailru_discounts_plain();

select * from get_mailru_discounts_temptable();

select * from get_mailru_discounts_pgvariables();

Запуск pgbench:

pgbench -h localhost -p 5433 -U postgres  -M prepared-c 32 -j 2 -n -f /tmp/test.pgb work

Результаты представлены в таблице:

Как можно видеть, через некоторое время и без того невысокая производительность процедуры с использованием временной таблицы падает еще больше; это связано, как уже отмечалось выше, с наполнением кеша ОС мусорными данными о ставших ненужными файлами временных таблиц. Любопытно проследить за выдачей pgbench (прогон на 5000 транзакций):

...
progress: 1.0 s, 2205.8 tps, lat 11.907 ms stddev 13.122
progress: 2.0 s, 2497.0 tps, lat 12.237 ms stddev 14.372
progress: 3.0 s, 1945.0 tps, lat 15.882 ms stddev 22.674
progress: 4.0 s, 2746.1 tps, lat 12.569 ms stddev 16.776
progress: 5.0 s, 1814.2 tps, lat 16.601 ms stddev 27.144
progress: 6.0 s, 2067.4 tps, lat 15.629 ms stddev 24.284
progress: 7.0 s, 1535.0 tps, lat 20.828 ms stddev 30.302
progress: 8.0 s, 862.0 tps, lat 37.671 ms stddev 45.891
progress: 9.0 s, 1312.8 tps, lat 25.218 ms stddev 35.340
progress: 10.0 s, 1213.1 tps, lat 25.686 ms stddev 37.921
progress: 11.0 s, 962.0 tps, lat 33.685 ms stddev 37.641
progress: 12.0 s, 1455.0 tps, lat 22.055 ms stddev 27.562
progress: 13.0 s, 1146.0 tps, lat 28.127 ms stddev 33.272
progress: 14.0 s, 791.0 tps, lat 37.760 ms stddev 41.861
progress: 15.0 s, 659.9 tps, lat 42.713 ms stddev 51.816
...

Хорошо видно, как колеблется производительность — начав относительно резво с 2205 tps она быстро скатывается до полутысячи, а в дальнейшем падает еще больше. Выполнение команды

/bin/echo 3 >/proc/sys/vm/drop_caches

несколько улучшает положение, но ненадолго.

Из вышеприведенных тестов понятно, что как хранилище временных данных модуль pg_variables значительно более производителен, чем временные таблицы, да и более удобен — в самом деле, набор данных определяется парой «пакет — переменная», которые вполне могут быть переданы как параметры, возвращены из функции и т. д.

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

work=# select pgv_insert('package', 'errs',row(n)) 
work-# from generate_series(1,5) as gs(n) where 1.0/(n-3)<>0;
ОШИБКА:  деление на ноль
work=# select * from pgv_select('package','errs') as r(i int);
 i 
---
 1
 2
(2 строки)

С одной стороны, это не очень удобно — в ряде случаев необходимо предусмотреть удаление некорректно внесенных данных, но в других может оказаться весьма полезным — например, сохранять какие-то данные даже в случае отката транзакции.

Разумеется, у представленного модуля есть и недостатки — так, например, он не будет самым удачным решением в том случае, если требуется осуществлять какой-то сложный поиск в большом объеме временных данных — удобнее и производительнее будет создать временную таблицу, вставить туда данные, построить индексы (любые! А не только хеш по одной колонке), собрать статистику и выполнить с ней требуемый запрос.

В то же время стоит, пожалуй, отметить, что и временные таблицы в ряде случаев могут оказаться вполне разумным решением — когда поток транзакций не слишком велик. В самом деле, при нагрузке в 10 tps вариант с временными таблицами будет вести себя достаточно приемлемо, если только не забывать, что продолжительные транзакции в других сессиях могут приводить к увеличению размеров системного каталога.

Полную документацию модуля можно посмотреть по ссылке: https://github.com/postgrespro/pg_variables