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

PostgreSQLでは配列型が使える。
プログラムで扱う配列をそのまま格納できるのは便利なんだけど、そこから`join`してSQLで操作しようとすると難しくなる。
例えばタグのidなどを配列で持っておいて、タグ名を取得するときには`unnest`でバラして`join`する。


```sql
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}');
```

こんなデータがあったとして

```sql
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 が走るとその順番になるようなので切ってみる。

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



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

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

なるほど、`with ordinality` ね。

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