Posted at

PostgreSQLで配列のidとjoinして並び替えるのが難しい

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_aggorder byが書けるので順序の列を追加すれば良いだけである。

・・・とここまで書きつつ調べていたら、最近は出来るようになっていた。

https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number

なるほど、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無しとプランが同じなのはびっくりしたけど。