Розширення pg_variables

Розширення 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=гаусса 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 де 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 де 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 public FUNCTION.get_mailru_discounts_plain()
RETURNS TABLE(usr_cnt integer, discounts_cnt integer) AS
$BODY$
begin
select count(*) into usr_cnt from ord.usr u де 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 public FUNCTION.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 де u.email like 'ab%@mail.ru';
get diagnostics usr_cnt = row_count;
select count(*) into discounts_cnt from ord.discount d де 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 де 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

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






Варіант — транзакцій 100 1000 5000 10000 20000 plain 10170 11349 11537 11560 11639 temptable 3364 3380 561 678 378 pg_variables 11852 15944 16634 16748 16719
Як можна бачити, через деякий час і без того невисока продуктивність процедури з використанням тимчасової таблиці падає ще більше; це пов'язано, як вже зазначалося вище, з наповненням кеша ОС сміттєвими даними про непотрібними файлами тимчасових таблиць. Цікаво простежити за видачею 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(int i);
i 
---
1
2
(2 рядки)

З одного боку, це не дуже зручно — в ряді випадків необхідно передбачити видалення некоректно внесених даних, але в інших може виявитися вельми корисним — наприклад, зберігати якісь дані навіть в разі відкоту транзакції.
Зрозуміло, представленого модуля є і недоліки — так, наприклад, він не буде самим вдалим рішенням в тому випадку, якщо потрібно здійснювати якийсь складний пошук у великому обсязі тимчасових даних — зручніше і продуктивніше буде створити тимчасову таблицю, вставити туди дані, побудувати індекси (будь-які! А не тільки хеш по одній колонці), зібрати статистику та виконати з нею необхідний запит.
У той же час варто, мабуть, зазначити, що і тимчасові таблиці в ряді випадків можуть виявитися цілком розумними рішенням — коли потік транзакцій не занадто великий. У насправді, при навантаженні 10 tps варіант з тимчасовими таблицями буде вести себе досить прийнятно, якщо тільки не забувати, що тривалі транзакції в інших сесіях можуть призводити до збільшення розмірів системного каталогу.
Повну документацію модуля можна переглянути за посиланням: https://github.com/postgrespro/pg_variables
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.