PostgreSQLでは配列型が使える。
プログラムで扱う配列をそのまま格納できるのは便利なんだけど、そこからjoin
してSQLで操作しようとすると難しくなる。
例えばタグのidなどを配列で持っておいて、タグ名を取得するときにはunnest
でバラしてjoin
する。
create table tag_type(
tag_type_id int primary key,
tag_type_name text not null unique
);
create table tag(
tag_id int primary key,
tag_type_id int not null references tag_type(tag_type_id) on delete cascade,
tag_name text not null unique
);
create index tag_tag_type_id_btree on tag(tag_type_id);
create table item (
item_id serial primary key,
item_name text not null,
item_tags int[] not null
);
insert into tag_type values(1, 'ブランド');
insert into tag_type values(2, 'バージョン');
insert into tag_type values(3, 'OS');
insert into tag values(11, 1, 'Chrome');
insert into tag values(12, 1, 'Safari');
insert into tag values(13, 1, 'Firefox');
insert into tag values(14, 1, 'IE');
insert into tag values(21, 2, '73');
insert into tag values(22, 2, '10');
insert into tag values(23, 2, '9');
insert into tag values(24, 2, '72');
insert into tag values(25, 2, '12.1');
insert into tag values(31, 3, 'Widnows');
insert into tag values(32, 3, 'macOs');
insert into tag values(33, 3, 'iOS');
insert into tag values(34, 3, 'Android');
insert into tag values(35, 3, 'Linux');
insert into item values(default, 'プロトタイプ', '{35,11,21,24}');
insert into item values(default, 'テスト版', '{32,35,11,24,12,22}');
insert into item values(default, '製品版', '{31,11,24,31,14,22,23,32,13,25}');
こんなデータがあったとして
select item.*, tags
from item
left outer join lateral (
select json_agg(tags_attr) as tags
from (
select *
from unnest(item_tags) as arr(tag_id)
join tag using(tag_id)
join tag_type using(tag_type_id)
) as tags_attr
) as tags on true
order by item_id
;
item_id | item_name | item_tags | tags
---------+--------------+---------------------------------+----------------------------------------------------------------------------------
1 | プロトタイプ | {35,11,21,24} | [{"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":21,"tag_name":"73","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":3,"tag_id":35,"tag_name":"Linux","tag_type_name":"OS"}]
2 | テスト版 | {32,35,11,24,12,22} | [{"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":1,"tag_id":12,"tag_name":"Safari","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":22,"tag_name":"10","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":3,"tag_id":32,"tag_name":"macOs","tag_type_name":"OS"}, +
| | | {"tag_type_id":3,"tag_id":35,"tag_name":"Linux","tag_type_name":"OS"}]
3 | 製品版 | {31,11,24,31,14,22,23,32,13,25} | [{"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":1,"tag_id":13,"tag_name":"Firefox","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":1,"tag_id":14,"tag_name":"IE","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":22,"tag_name":"10","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":23,"tag_name":"9","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":25,"tag_name":"12.1","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":3,"tag_id":31,"tag_name":"Widnows","tag_type_name":"OS"}, +
| | | {"tag_type_id":3,"tag_id":31,"tag_name":"Widnows","tag_type_name":"OS"}, +
| | | {"tag_type_id":3,"tag_id":32,"tag_name":"macOs","tag_type_name":"OS"}]
一気に取得できる。
でもよく見るとitem_tags
の順番じゃなくてtag_id
の数値順に並んでしまっている。
explain で見てみると、どうやら先にtag
テーブルにseq scanが走っているようだ。
https://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement
ここに書いているようにleft joinにしても変わらず。
・・・と思っていたらitem_tags
の順番になったりtag_id
の順番になったりして結果が不安定だ。
indexを付けたり外したり、outer join と inner join を色々変更したりすると不安定さが分かる。
order by を指定しない場合の並び順は不定(見つけた順)だそうだ。
stackoverflow の二個目のコメントのように array_position
で並び替えてもいいけど、今回の例は重複があるし 関数インデックスにできないので微妙。
seq scan が走るとその順番になるようなので切ってみる。
set enable_seqscan = off;
select item.*, tags
from item
left outer join lateral (
select json_agg(tags_attr) as tags
from (
select *
from unnest(item_tags) as arr(tag_id)
join tag using(tag_id)
join tag_type using(tag_type_id)
) as tags_attr
) as tags on true
order by item_id
;
item_id | item_name | item_tags | tags
---------+--------------+---------------------------------+----------------------------------------------------------------------------------
1 | プロトタイプ | {35,11,21,24} | [{"tag_type_id":3,"tag_id":35,"tag_name":"Linux","tag_type_name":"OS"}, +
| | | {"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":21,"tag_name":"73","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}]
2 | テスト版 | {32,35,11,24,12,22} | [{"tag_type_id":3,"tag_id":32,"tag_name":"macOs","tag_type_name":"OS"}, +
| | | {"tag_type_id":3,"tag_id":35,"tag_name":"Linux","tag_type_name":"OS"}, +
| | | {"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":1,"tag_id":12,"tag_name":"Safari","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":22,"tag_name":"10","tag_type_name":"バージョン"}]
3 | 製品版 | {31,11,24,31,14,22,23,32,13,25} | [{"tag_type_id":3,"tag_id":31,"tag_name":"Widnows","tag_type_name":"OS"}, +
| | | {"tag_type_id":1,"tag_id":11,"tag_name":"Chrome","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":24,"tag_name":"72","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":3,"tag_id":31,"tag_name":"Widnows","tag_type_name":"OS"}, +
| | | {"tag_type_id":1,"tag_id":14,"tag_name":"IE","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":22,"tag_name":"10","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":2,"tag_id":23,"tag_name":"9","tag_type_name":"バージョン"}, +
| | | {"tag_type_id":3,"tag_id":32,"tag_name":"macOs","tag_type_name":"OS"}, +
| | | {"tag_type_id":1,"tag_id":13,"tag_name":"Firefox","tag_type_name":"ブランド"}, +
| | | {"tag_type_id":2,"tag_id":25,"tag_name":"12.1","tag_type_name":"バージョン"}]
期待通りに並んだ。
つまり、正規化せずに配列型を使いつつ結合して配列順に並び替えもする場合、PostgreSQLの実装依存の部分、つまりプランナの動きを把握しておく必要がありそうだ。
正規化してテーブルを分ける場合は、json_agg
にorder by
が書けるので順序の列を追加すれば良いだけである。
・・・とここまで書きつつ調べていたら、最近は出来るようになっていた。
なるほど、with ordinality
ね。
select item.*, tags
from item
left outer join lateral (
select json_agg(tags_attr order by i) as tags
from (
select *
from unnest(item_tags) with ordinality as arr(tag_id, i)
join tag using(tag_id)
join tag_type using(tag_type_id)
) as tags_attr
) as tags on true
order by item_id
;
かなりシンプルになった。
ちなみに業務で使ってるデータを持ってきてjoin, left outer join, lateral, ordinality をそれぞれ試してみると、実行プランは全て同じだった。
データがバラけていて数多くあると想定通りのインデックスが使われるので問題なかったみたい。
保険みたいなものかな。
json_agg(join_table order by i)
って書いてるのにorder by無しとプランが同じなのはびっくりしたけど。