Postgresで行集合1行ずつのリストを取得するとき、 ウィンドウ関数 と 副問い合わせ、どちらが早いか

概要

キー情報で一意になる行のリストを取得するときに、キー情報ではない列は適当に該当した一列から値を取得したいと思ったが、意外とやり方が難しかった。

いまいち使い方が分かっていなかったウィンドウ関数の勉強しながら、処理コストなど検討してみた。

ちなみにキーは複合キーである。

方法

やり方として、

A.DISTINCT　+　副問い合わせ
1 DISTINCTでキーが一意になるような行セットを取得し、
2 さらにそこに副問い合わせでlimit1で、適当な1行から値を取得する

B.ウィンドウ関数を使う
1 ウィンドウ関数で行集合内での行番号付与
2 行集合内の先頭行のみ取得

の2通りを考えた。
(もっといい方法あったら教えてください）

■Aの場合のサンプルSQL

--1意なキーのリストに、キーではない列の適当な1行を副問い合わせで加える
SELECT
col_key1
,col_key2
,col_key3
,(SELECT col_prm1 FROM shohin_master mst2
WHERE col_key1 = col_key1
AND col_key2 = col_key2
AND col_key3 = col_key3
limit 1)
FROM
--1意なキーのリストを取得
(SELECT DISTINCT
col_key1
,col_key2
,col_key3
FROM shohin_master mst1
) keylist
;

■Bの場合のサンプルSQL

--row_number = 1の行のみ取得（行集合ごとの、1行目を取得）
SELECT
col_key1
,col_key2
,col_key3
,col_prm1
FROM
--キーごとの行集合を作り、行集合内での行番号を付与したリストを作成
(SELECT
col_key1
,col_key2
,col_key3
,row_number() over(partition by col_key1,col_key2,col_key3)
FROM shohin_master mst1
) mst_with_num
WHERE row_number = 1
;

結果

マスタ数万件くらいで、Aパターンのほうが早かった。

件数ごとの処理コスト比較、ウィンドウ関数の使い方について、追記したい。

参考

■ウィンドウ関数のPostgresドキュメント
https://www.postgresql.jp/document/11/html/functions-window.html