souki-paranoiastのブログ

地方都市でプログラマーをやっている人のブログ。技術ネタ以外も少し書く。メインの言語はJava。https://paranoiastudio-japan.jimdo.com/ に所属

【PostgreSQL】同じ計算が複数出る問い合わせにlateralは有効か？

プログラミング

今回やること

表題の通り。Aを求めれるのにカラムXとYが必要で、BやCも同様にカラムXとYあるいはAの結果が必要なケースを想定している。

group byなどの集約に関するパフォーマンスの向上などについては触れられている記事を見かけるが、あまり表題の使い方について触れている記事が見当たらなかった。 見つけられなかっただけかもしれない。。

結論から言うと、こういう使い方でもパフォーマンスは上がった（ここでは書かないが、逆に、同じ計算が複数回出ないのにlateralを使うと遅くなってしまう。恐らくNestedLoop分のコスト。）。 個人的には可読性も上がるので、使うタイミングさえ見誤らなければ積極的に使っていきたい。

また、「副問い合わせのネストした箇所で計算すれば？」という意見はあるだろうが、多分正論。でも個人的にネスト量が増えるのが好きではないのです…。いやまあ、lateralも副問い合わせだし、ネストしてるけど。少なくともメインのfromは副問い合わせじゃないじゃないですかー…。

環境

テスト用テーブル

確認用に3つのテーブルを用意した。 今回は意味のある計算は一切考えないが、「商品」と「税」と「データ」を対象に、「数量 * 単価」と「数量 * 単価 * 税率」と「数量 * 単価 + 数量 * 単価 * 税率」を求めることとした。

create table item (
  code text,
  price numeric,
  tax_code text,

  PRIMARY KEY (code)
);

create table tax (
  code text,
  rate numeric,

  PRIMARY KEY (code)
);

create table data (
  record_id bigserial,
  item_code text,
  quantity numeric,

  PRIMARY KEY (record_id)
);

テストデータの作成

1万件程度で試したい（10万を超えるような仕事をしていないので…）。 全部同じだと計算にキャッシュが効きそうなイメージなので、一応多少はばらけるようにしているつもりだ。意味あるかもばらけているかもわからないが。

with a as(
  select generate_series(1, 26) c
)
insert into item
select
  chr(64 + c) as code,
  c * 100 as price,
  (c % 3)::text as tax_code
from a
;

insert into tax
values('0', 0)
     ,('1', 8)
     ,('2', 10)
;

with a as(
  select generate_series(0, 10000) c
)
insert into data(item_code, quantity)
select
  chr(65 + (c % 26)) as item_code,
  c % 10 + 1 as quantity
from a
;

素直に書いた場合

SELECT句にて計算を全て書く方法

select
  data.record_id
 ,data.quantity * item.price as r1
 ,data.quantity * item.price * tax.rate / 100::numeric as r2
 ,data.quantity * item.price + data.quantity * item.price * tax.rate / 100::numeric as r3
from data
left join
  item
on data.item_code = item.code
left join
  tax
on item.tax_code = tax.code

lateralを用いた場合

lateralで複数回用いる値を先に計算する形（moneyとtax_moneyのこと）。

select
  data.record_id
 ,work_lateral.money as r1
 ,work_lateral.tax_money as r2
 ,work_lateral.money + work_lateral.tax_money as r3
from data
left join
  item
on data.item_code = item.code
left join
  tax
on item.tax_code = tax.code
left join lateral(
  select
    data.quantity * item.price as money
   ,data.quantity * item.price * tax.rate / 100::numeric as tax_money
) work_lateral
on true

explain analyze の実行結果

  • 素直に書いたケース
----- 実行計画 -----
Hash Left Join  (cost=54.42..487.25 rows=10001 width=104) (actual time=0.036..12.618 rows=10001 loops=1)
  Hash Cond: (item.tax_code = tax.code)
  ->  Hash Left Join  (cost=24.63..206.05 rows=10001 width=77) (actual time=0.027..2.306 rows=10001 loops=1)
        Hash Cond: (data.item_code = item.code)
        ->  Seq Scan on data  (cost=0.00..155.01 rows=10001 width=15) (actual time=0.011..0.475 rows=10001 loops=1)
        ->  Hash  (cost=16.50..16.50 rows=650 width=96) (actual time=0.013..0.013 rows=26 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 10kB
              ->  Seq Scan on item  (cost=0.00..16.50 rows=650 width=96) (actual time=0.005..0.008 rows=26 loops=1)
  ->  Hash  (cost=18.80..18.80 rows=880 width=64) (actual time=0.005..0.005 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on tax  (cost=0.00..18.80 rows=880 width=64) (actual time=0.004..0.004 rows=3 loops=1)
Planning Time: 0.127 ms
Execution Time: 12.794 ms
--------------------------------------------------------------------------------
  • lateralで書いたケース
----- 実行計画 -----
Nested Loop Left Join  (cost=54.42..687.27 rows=10001 width=104) (actual time=0.189..10.882 rows=10001 loops=1)
  ->  Hash Left Join  (cost=54.42..262.23 rows=10001 width=77) (actual time=0.148..3.887 rows=10001 loops=1)
        Hash Cond: (item.tax_code = tax.code)
        ->  Hash Left Join  (cost=24.63..206.05 rows=10001 width=77) (actual time=0.033..2.430 rows=10001 loops=1)
              Hash Cond: (data.item_code = item.code)
              ->  Seq Scan on data  (cost=0.00..155.01 rows=10001 width=15) (actual time=0.013..0.568 rows=10001 loops=1)
              ->  Hash  (cost=16.50..16.50 rows=650 width=96) (actual time=0.013..0.013 rows=26 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 10kB
                    ->  Seq Scan on item  (cost=0.00..16.50 rows=650 width=96) (actual time=0.006..0.008 rows=26 loops=1)
        ->  Hash  (cost=18.80..18.80 rows=880 width=64) (actual time=0.106..0.106 rows=3 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on tax  (cost=0.00..18.80 rows=880 width=64) (actual time=0.005..0.007 rows=3 loops=1)
  ->  Result  (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=10001)
Planning Time: 0.138 ms
Execution Time: 11.079 ms
--------------------------------------------------------------------------------

結果

何度か試したが、おおむね前者が12ms、後者が11msで若干の優位性が見られた。 ちなみに、InsertのSQLを追加で発行し、dataテーブルが1110003件で試した場合は前者が1400ms、後者が1200msだった。

雑感

わりと使えることがわかって安心。 もちろん、Nested Loopが実行されるため、複数回同じ計算が出るようなこういうケース以外では素直に書いた方が速い（といってこれも同じ程度の差異しかでないが）。 処理速度も問題なんだけど、計算がからむと同じ内容なのかどうかを判断するのにも時間がかかるので、こういう形で共通化できるのは特筆すべき点だと思う。 select句にcase文の中に同じ計算を何度も入れられると読む側としてはMPがゴリゴリ削られる…(´・ω・｀)

ある意味で変数的に使えるので、可読性向上ということでどうだろうか。

おまけ

気になったので副問い合わせで計算した場合の結果も貼り付ける。これが一番速いと思ってたが違った。試した限りでは1400msという結果になった。

select 
  _money.record_id
 ,_money.r1
 ,_money.r2
 ,_money.r1 + _money.r2 as r3
from (
  select
    data.record_id
   ,data.quantity * item.price as r1
   ,data.quantity * item.price * tax.rate / 100::numeric as r2
  from data
  left join
    item
  on data.item_code = item.code
  left join
    tax
  on item.tax_code = tax.code
) _money

実行計画を見ると、素直に書いた場合の結果と一緒なんだよなぁ。計算結果って遅延評価みたいな感じなんだろうか？

----- 実行計画 -----
Hash Left Join  (cost=54.42..47991.07 rows=1110003 width=104) (actual time=0.037..1377.255 rows=1110003 loops=1)
  Hash Cond: (item.tax_code = tax.code)
  ->  Hash Left Join  (cost=24.63..20058.41 rows=1110003 width=77) (actual time=0.028..247.948 rows=1110003 loops=1)
        Hash Cond: (data.item_code = item.code)
        ->  Seq Scan on data  (cost=0.00..17101.03 rows=1110003 width=15) (actual time=0.012..63.836 rows=1110003 loops=1)
        ->  Hash  (cost=16.50..16.50 rows=650 width=96) (actual time=0.013..0.013 rows=26 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 10kB
              ->  Seq Scan on item  (cost=0.00..16.50 rows=650 width=96) (actual time=0.005..0.007 rows=26 loops=1)
  ->  Hash  (cost=18.80..18.80 rows=880 width=64) (actual time=0.005..0.005 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on tax  (cost=0.00..18.80 rows=880 width=64) (actual time=0.004..0.004 rows=3 loops=1)
Planning Time: 0.137 ms
Execution Time: 1395.279 ms
--------------------------------------------------------------------------------

参考

LATERALを使ってみよう | Let's POSTGRES

Studio ODIN - blog風小ネタ集 > SQL の LATERAL キーワード