pg_variables拡張機能

多くの場合、アプリケーションソフトウェアを開発する際にこの種の問題が発生する場合があります。中間データの場合、いくつかの結果セットを取得する必要があります。 一部のユーザーは、ソーシャルネットワークなどでこれらのユーザーから友人やメッセージのリストを取得できます。

通常、ソリューションは非常に単純に見えます。最初に、たとえばユーザーのリストを取得し、次にユーザーに対して必要な結果セットを作成します。 次に、ユーザーのリストを取得し、2番目のセットを作成します。 そして、そのようなリストの構築がかなり高価な操作であることが証明されない場合、すべてがうまくいきます。したがって、このリストに基づいていくつかの結果を構築する必要がある場合、関連するすべてのオーバーヘッドでこのリストを数回取得する必要があることがわかります 一時テーブルは、この問題の明らかな解決策のように見えますが、実際はそうです。 残念ながら、あまり気に入らない機能がいくつか関連付けられています。一時テーブルごとにファイルを作成する必要があります（テーブルを破棄する場合は削除してください）。 また、これらのテーブルはもちろん、自動バキュームプロセスには表示されないため、自動的にクリアされず、統計は収集されません。 さらに悪いことに、長くアクティブなトランザクションがある場合、システムカタログの無制限の成長が発生する可能性があります。 さらに、オペレーティングシステムのキャッシュには、一時テーブル用に作成されたファイルに関するデータが入力され、一般的なパフォーマンスの低下につながります。

また、クエリをコンパイルするときにテーブル名を知っている必要があるため、異なるテーブルの使用は非常に厄介であり、クエリの動的な生成に頼らざるを得ず、その後の結果はすべて発生します。 動的クエリのplpgsqlがプランを保存しないことを思い出すと、複雑なクエリの場合、これは重大な問題になる可能性があります。

別の明らかな解決策は配列ですが、それらには多くの欠点もあります。 まず、配列は不変です。 小さい配列の場合、これは恐ろしいことではありませんが、大きい配列の場合、非常に不快になります。 第二に、配列はリクエストの実行中にのみ存在し、セッションの寿命全体ではありません。 そして、第三に、配列はキーによる検索を許可しません。

別のセッションでは、多くの場合、リクエストの実行中にすべての機能で使用可能な一時データ（現在のユーザー、権限など）が必要です。

最後に、レプリカで読み取り専用クエリを実行する場合、多くの場合、一時データをどこかに保存する必要があります。

このような問題を回避するために、説明されている拡張機能が意図されています。

それは何をしますか？

まず、スカラーセッション変数を定義できます。スカラーセッション変数自体は非常に価値があります。たとえば、リクエストを行うユーザーの識別子、ユーザー、ユーザー、さまざまな属性を格納できます。 例：

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして：

 select pgv_get_int('package','usr_id')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここで（およびさらに） package



は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.usr



）を表す3つのテーブルのテストデータベースが使用されます。 データベースは、 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='af ' 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='[az]{3,16}\.[az]{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



実行時間は、一時テーブルを使用したバリアントの実行時間よりも著しく短くなっています。 さらに、一時テーブルを持つバリアントの実行時間は、OSキャッシュの状態に大きく依存します。これは、上記のように、各一時テーブルに対してファイルが作成されるためです（削除されると、それに応じて削除されます）。

上記のコードを注意深く見ると、かなり公平な発言をすることができます-一時テーブルがあるバージョンでは、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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、時間は非常に類似しています。1つの操作は、タイムテーブルと比較して約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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果を表に示します。

オプション-トランザクション	100	1000	5000	10,000	20000
平野	10170	11349	11537	11560	11639
誘惑的	3364	3380	561	678	378
pg_variables	11852	15944	16634	16748	16719

ご覧のように、しばらくすると、一時テーブルを使用したプロシージャの生産性がさらに低下します。 これは、前述のとおり、不要なファイルになった一時テーブルに関するガベージデータでOSキャッシュがいっぱいになるためです。 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から比較的活発に開始し、すぐに5千までスライドし、さらに低下します。 コマンド実行

 /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 )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一方では、これはあまり便利ではありません-誤って入力されたデータを削除する必要がある場合もありますが、非常に役立つ場合もあります-たとえば、トランザクションのロールバックが発生した場合でも一部のデータを保存します。

もちろん、提示されたモジュールには欠点もあります-たとえば、大量の一時データで複雑な検索を実行する必要がある場合、最も成功したソリューションではありません-一時テーブルを作成し、そこにデータを挿入し、構築する方が便利で生産的ですインデックス（任意！1列のハッシュだけでなく）、統計を収集し、必要なクエリを実行します。

同時に、場合によっては、トランザクションフローが大きすぎない場合に一時テーブルも非常に合理的なソリューションになることもあります。 実際、10 tpsの負荷では、一時テーブルを使用したオプションは、他のセッションでの長期トランザクションがシステムカタログのサイズの増加につながることを忘れない限り、まったく問題なく動作します。

モジュールの完全なドキュメントは、次のリンクで表示できます： https : //github.com/postgrespro/pg_variables