相関副問い合わせ＋max() vs ウィンドウ関数

きっかけ

社内で作っているシステムで「社員ごとに最新のXXXデータを一件ずつ取得したい」という要件があり、SQLで解決する方法を考えてみました。このエントリでは、pgbenchのpgbench_tellersテーブルを題材にして「branchごとに最も収支の良いtellerを一件ずつ取得する」というシナリオにしています。

$ pgbench -i -s 5

でbidが1〜５でデータが作られるようにテーブル群を初期化しましたが、bbalanceは初期値「0」なので以下のSQL文でランダムに更新しておきます。

postgres=# UPDATE pgbench_tellers SET tbalance = random() * 10000;
UPDATE 50

相関副問い合わせ＋max()

まずは、よく見る素直(?)な書き方です。

postgres=# SELECT * FROM pgbench_tellers t
postgres-# WHERE t.tbalance = (
postgres-# SELECT max(tbalance) FROM pgbench_tellers t2 WHERE t.bid = t2.bid)
postgres-# ORDER BY bid;
 tid | bid | tbalance | filler 
-----+-----+----------+--------
   7 |   1 |     9839 | 
  18 |   2 |     7625 | 
  25 |   3 |     7266 | 
  32 |   4 |     8821 | 
  43 |   5 |     9984 | 
(5 rows)

Time: 4.679 ms

結果は正しく取れていますが、EXPLAINで実行計画を見てみると…

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_tellers t
postgres-# WHERE t.tbalance = (
postgres-# SELECT max(tbalance) FROM pgbench_tellers t2 WHERE t.bid = t2.bid);
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Sort (actual time=2.869..2.870 rows=5 loops=1)
   Sort Key: t.bid
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on pgbench_tellers t (actual time=0.449..2.842 rows=5 loops=1)
         Filter: (tbalance = (SubPlan 1))
         Rows Removed by Filter: 45
         SubPlan 1
           ->  Aggregate (actual time=0.050..0.051 rows=1 loops=50)
                 ->  Seq Scan on pgbench_tellers t2 (actual time=0.019..0.038 rows=10 loops=50)
                       Filter: (t.bid = bid)
                       Rows Removed by Filter: 40
 Total runtime: 2.986 ms
(12 rows)

やはりpgbench_tellersテーブルを二回スキャンしていますね。bidごとの最大値を取るのに一回と、その値に一致する行を探すのに一回です。このクエリならば仕方ないところです。

ウィンドウ関数

PostgreSQLには便利なウィンドウ関数があるので、これを使って書き換えてみます。dense_rank()関数でbranchごとのtbalanceの順位を求め、一位の行だけを抽出しています。dense_rank()でなくrank()を使うと、同率首位が全て出るようになります。

postgres=# SELECT tid, bid, tbalance, filler FROM (
postgres-# SELECT dense_rank() OVER (PARTITION BY bid ORDER BY tbalance DESC) AS rank,
postgres-# * FROM pgbench_tellers ORDER BY bid, 1) AS a WHERE rank = 1 ORDER BY bid;
 tid | bid | tbalance | filler 
-----+-----+----------+--------
   7 |   1 |     9839 | 
  18 |   2 |     7625 | 
  25 |   3 |     7266 | 
  32 |   4 |     8821 | 
  43 |   5 |     9984 | 
(5 rows)

Time: 1.930 ms

結果は先ほどのものと同じですが、所要時間が短くなっていますね。こちらもEPXLAINで実行計画を見てみましょう。

postgres=# EXPLAIN ANALYZE SELECT tid, bid, tbalance, filler FROM (
postgres-# SELECT dense_rank() OVER (PARTITION BY bid ORDER BY tbalance DESC) AS rank,
postgres-# * FROM pgbench_tellers ORDER BY bid, 1) AS a WHERE rank = 1 ORDER BY bid;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Subquery Scan on a (actual time=0.550..0.595 rows=5 loops=1)
   Filter: (a.rank = 1)
   Rows Removed by Filter: 45
   ->  Sort (actual time=0.542..0.555 rows=50 loops=1)
         Sort Key: pgbench_tellers.bid, (dense_rank() OVER (?))
         Sort Method: quicksort  Memory: 29kB
         ->  WindowAgg (actual time=0.162..0.446 rows=50 loops=1)
               ->  Sort (actual time=0.146..0.164 rows=50 loops=1)
                     Sort Key: pgbench_tellers.bid, pgbench_tellers.tbalance
                     Sort Method: quicksort  Memory: 27kB
                     ->  Seq Scan on pgbench_tellers (actual time=0.015..0.045 rows=50 loops=1)
 Total runtime: 0.725 ms
(12 rows)

pgbench_tellersへのスキャンが一回になり、それをbidとtbalanceでソートしてからWindowAggノードでランク付け処理しているのが分かります。最後にランク一位以外をフィルタして結果を作っているので、ORDER BYのために改めてソートする必要もなく、効率的に処理できています。

というわけで、ウィンドウ関数を使うと集計関連の処理が簡単かつ高速に実現できることがあるので、是非覚えましょう（自分に向けて）。