PostgreSQL® 14の新機能SEARCHとCYCLEを探る

<font color="Grey">*[Explore the new SEARCH and CYCLE features in PostgreSQL® 14](https://aiven.io/blog/explore-the-new-search-and-cycle-features-in-postgresql-14)の翻訳です。*</font>

2021年12月14日

# PostgreSQL® 14の新機能SEARCHとCYCLEを試す

再帰問い合わせを使用していますか?PostgreSQL 14で利用可能な新しいSEARCHとCYCLE機能を、以前のブログ記事の更新で確認してください。

休日、旅行、楽しい時間はいつも私たちの頭の中にあります。数ヶ月前、[PostgreSQL再帰問い合わせを使用したナップザック問題](https://aiven.io/blog/solving-the-knapsack-problem-in-postgresql)を解決する方法を見ました。しかし、ブログ記事はいつもワインのように熟成するわけではありません。公開日から数週間後、PostgreSQL 14が発表され、実に興味深い[新機能](https://www.postgresql.org/about/news/postgresql-14-released-2318/)がいくつか含まれています：CYCLE`と`SEARCH`です。これらは再帰問い合わせの記述方法を大幅に単純化します。この記事では、好きなトピックである旅行計画に基づいたいくつかの例を紹介します！


## データベースの作成


この記事の例は、PostgreSQL 14またはそれ以降のデータベースで動作します。ここではシンプルに、[Aiven-managed PostgreSQL service](https://aiven.io/postgresql)とAiven CLIを使用します（インストールとログイン方法については[専用ドキュメント](https://developer.aiven.io/docs/tools/cli.html)を確認してください）。以下はデータベースを作成するCLIコマンドです：



```
AVN SERVICE CREATE HOLIDAYS-PG
 --サービスタイプ pg
 --plan hobbyist
 --cloud google-europe-west3 ⇦ -c pg_version=14
 -c pg_version=14`Copy to clipboard
```

上記は、最小限の `hobbyist` プランで、`google-europe-west3` リージョンに `holidays-pg` という名前の PostgreSQL 14 (`-c pg_version=14`) サービスを作成します。これでテストは十分である。私たちが提供するツールのバージョンを確認するには、[専用ページ](https://developer.aiven.io/docs/tools/cli/service.html#avn-service-versions)に記載されている `avn service versions` コマンドを使用する。


これでPostgreSQL 14データベースが立ち上がるまで数分の待ち時間ができた。お気に入りの旅行ガイドを開き、目的地をブラウズし始めることができる。その間に、サービス作成タスクの進捗を見守ることができます：



```
avn service wait holidays-pg`クリップボードにコピーする
```

上記のコマンドは、サーバーが起動するまで定期的にサーバーの状態を要求する。これが返ってきたら、`holidays-pg` PostgreSQL 14 サービスに接続する準備ができたことになる：



```
avn service cli holidays-pg`クリップボードにコピーする
```

## データセットを作成する


我々はヨーロッパを横断し、予算内でいくつかの主要都市を訪れたい。これが[以前議論したナップザック問題](https://aiven.io/blog/solving-the-knapsack-problem-in-postgresql)のバリエーションであることがわかるだろうか？一見異なる問題が，似たような戦略で解決できるのは，いつも興味深い．


訪問したい都市を保存するために、`cities`テーブルを作成し、選んだ場所で埋める。



```
citiesテーブルを作成する(
 city_id int primary key、
 city_name varchar
 );

citiesに値を挿入 (0, 'Rome')、
 (1, 'London')、
 (2, 'Paris')、
 (3, 'ヘルシンキ')、
 (4, 'Barcelona');`Copy to clipboard
```

どうやって都市間を移動するのか？簡単だ。旅行予約サイトにアクセスして、乗り継ぎと各旅行代金を調べる。通常、このようなグラフが返ってくる：


![目的地とその間の料金を含む接続を示す画像](https://cdn.sanity.io/images/sczeoy4w/production/85dbb496c23734119e24ea24e47f5e44a6ff43ae-939x629.png?q=80&fit=min&auto=format&dpr=1.5)


上記の情報をPostgreSQLで表現するために、出発地(`city_a_id`)、目的地(`city_b_id`)、ユーロ建ての旅行代金(`price_in_eur`)の情報を格納する`trips`という名前のテーブルを作成する。



```
テーブル trips(
 trip_id int primary key、
 city_a_id int references cities(city_id)、
 city_b_id int references cities(city_id)、
 price_in_eur int
 );

tripsに値を挿入
 (1, 0, 1, 200),
 (2, 0, 2, 250),
 (3, 0, 3, 150),
 (4, 1, 0, 120),
 (5, 1, 3, 350),
 (6, 1, 4, 450),
 (7, 2, 0, 170),
 (8, 2, 3, 320),
 (9, 3, 0, 50),
 (10, 3, 4, 120),
 (11, 4, 0, 30),
 (12, 4, 1, 500);`クリップボードへコピー
```

trips`テーブルには利用可能なすべてのルートと関連するコストが格納されている。例えば、id `2` のトリップは、`Rome` (`city_id:0`) から `Paris` (`city_id:2`) まで、`250` ユーロで行くことができる。悪くない。そろそろ旅の計画を立てよう。


## 旅の計画を立てる


どの道もローマに通じていることがわかっているので、永遠の都ローマを出発点にすることができる。どこに行けるかを調べるには、`cities`テーブルと`trips`テーブルを結合する必要があります。



```
選択
 src.city_name、
 dst.city_name、
 trips.price_in_eur
from cities src
 join trips on src.city_id = trips.city_a_id
 join cities dst on trips.city_bashi_id = dst.city_id
where src.city_name='Rome';`Copy to clipboard
```

結果は上のグラフと同じ情報を示している。1回の旅行でロンドン、パリ、ヘルシンキに行くことができる。



```
city_name｜都市名｜価格｜eur
-----------+-----------+--------------
ローマ｜ロンドン｜200
ローマ｜パリ｜250
ローマ｜ヘルシンキ｜150
(3行)``クリップボードにコピー
```

## 旅のホップを増やす


よし、次はどこだ？再帰クエリの力を使って、可能なすべての組み合わせをループすることができる。


無限のお金があれば、世界中を永遠に旅することができる。これをデータベース用語に置き換えると、再帰クエリで無限のループができることになる。実生活を模倣し、無限ループを避けるために、すべての旅行をカバーするために全体の予算を`800`ユーロに設定しましょう。


[前の記事](https://aiven.io/blog/solving-the-knapsack-problem-in-postgresql)から学ぶと、旅の再帰クエリはこのように書くことができる：



```
with recursive trip_journey(
 city_id、
 trip_id、
 total_price_in_eur、
 journey_stops
 )
として
 select
 city_id as city_id、
 null::int as trip_id、
 0 price_in_eur、
 ARRAY[city_name] as journey_name
 from cities
 where city_id=0
 UNION ALL
 選択
 trips.city_bache_id、
 trips.trip_id、
 tj.total_price\_in_eur + trips.price_in_eur、
 tj.journey_stops || city_b.city_name
 from trip_journey tj join trips on tj.city_id = trips.city_a_id
 join cities city_a on trips.city_a_id = city_a.city_id
 join cities city_b on trips.city_bashi_id = city_b.city_id
 ここで、tj.total_price\_in eur + trips.price α < 800
 )
select ˶* from trip_journey;`Copy to clipboard
```

少し分割してみよう。最初のセクションは出発地点を示している：`Rome`（`city_id=0`）から出発したい。もし旅をしなければ、`trip_id`は`null`となり、全体のコストは`0`となる。



```
を選択する。
 city_id as city_id、
 null::int as trip_id、
 0 price_in_eur、
 ARRAY[city_name] as journey_name
from cities
where city_id=0`クリップボードにコピー
```

次に、トリップの追加を開始する。再帰的ピースを使用して、先に定義した`trip_journey`と`trips`テーブルを結合し、すべての可能な目的地と関連するコストを検出する。



```
UNION ALL
select
 trips.city_bache_id、
 trips.trip_id、
 tj.total_price_in\_eur + trips.price_in\_eur、
 tj.journey_stops || city_b.city_name
from trip_journey tj join trips on tj.city_id = trips.city_a_id
join cities city_a on trips.city_a_id = city_a.city_id
join cities city_b on trips.city_bashi_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800`Copy to clipboard
```

何が起こっているかというと、`journey_stops`に含まれる文字列の配列に`city_b.city_name`を追加することで、旅を記録している。次に、前回の合計と現在の旅行代金（`tj.total_price_in_eur` + trips.price_in_eur`）を合計して、総旅行代金を計算する。最後に、全体のコストが `WHERE` 節の制限値 `800` ユーロ以内であることを確認する。


このクエリは89行を検索し、旅行なしのオプション（ローマに滞在）から始まり、複数都市にまたがる長い旅行`{ローマ,ヘルシンキ,ローマ,ヘルシンキ,ローマ,ヘルシンキ,バルセロナ,ローマ}`までが検索された。



```
city_id｜trip_id｜total_price｜in_eur｜ journey_stops
---------+---------+--------------------+-----------------------------------------------------------------
 0 | | 0 | {ローマ｝
 1｜1｜200｜{ローマ,ロンドン｝
 2｜2｜250｜{ローマ,パリ}｜3｜150｜{ローマ,パリ
 3｜3｜150｜{ローマ,ヘルシンキ}｜0｜4｜320｜{ローマ,ヘルシンキ
 0｜4｜320｜{Rome,London,Rome} 3｜5｜550｜{Rome,London,Rome
 3 | 5 | 550 | {ローマ,ロンドン,ヘルシンキ} ....
....
 4 | 10 | 770 | {ローマ,ヘルシンキ,ローマ,ヘルシンキ,バルセロナ,ローマ,ヘルシンキ,バルセロナ} ....
 0 | 11 | 700 | {ローマ,ヘルシンキ,ローマ,ヘルシンキ,ローマ,ヘルシンキ,バルセロナ,ローマ} ....
(89行)``クリップボードにコピー
```

## SEARCH オプションで探索パスを定義する


上記の89行は、我々が取ることができるすべての可能な旅程の良い概要を与えてくれる。しかし、このデータセットはどのように並べられるのでしょうか?PostgreSQL 14では、`SEARCH`オプションが再帰問い合わせの動作を定義する新しい方法を提供します：


* もし、**停車駅の数**に基づいて旅行の順番を決めたいのであれば、`BREADTH`オプションを使うことができます。停車駅の数**に基づいてトリップを順番に並べたい場合は、`BREADTH`オプションを使うことができます。
* トリップパス**に基づいてトリップを並べたい場合は、`DEPTH`オプションを使用することができます。例えば、`{ローマ}`-> `{ローマ->ロンドン}` -> `{ローマ->ロンドン->ヘルシンキ}` のように、旅の深さが最大になるまで、それぞれのステップで旅が広がっていくのがわかる。


我々のデータセットで例を挙げると、最後の `select * from trip_journey` ステートメントを以下のように置き換えるだけである：



```
SEARCH BREADTH FIRST BY city_id SET ordercol
select ˶* from trip_journey order by ordercol limit 15;`Copy to clipboard
```

最初の15行だけを返すようにクエリを制限しています（`limit 15`）。BREADTH` オプションを使用しているため、結果セットは **ストップ数** の順に並びます。



```
 city_id｜trip_id｜total_price｜in_eur｜ journey_stops｜ ordercol
---------+---------+--------------------+--------------------------------+----------
 0 | | 0 | {ローマ｝| (0,0)
 1｜1｜200｜{Rome,London}｜ (1,1)| (1,1)
 2｜2｜250｜{Rome,Paris}｜ (1,2)| (1,2)
 3｜3｜150｜｛ローマ、ヘルシンキ｝｜ (1,3)| (1,3)
 0｜4｜320｜｛ローマ、ロンドン、ローマ｝｜ (2,0)| (2,0)
 0｜9｜200｜｛ローマ,ヘルシンキ,ローマ｝｜ (2,0)| (2,0)
 0｜7｜420｜｛ローマ,パリ,ローマ｝｜ (2,0)| (2,0)
 3｜5｜550｜｛ローマ，ロンドン，ヘルシンキ｝｜ (2,3)| (2,3)
 3 | 8 | 570 | {ローマ,パリ,ヘルシンキ}｜ (2,3)| (2,3)
 4｜6｜650｜｛ローマ、ロンドン、バルセロナ｝｜ (2,4)| (2,4)
 4｜10｜270｜｛ローマ、ヘルシンキ、バルセロナ｝｜ (2,4)| (2,4)
 0｜9｜600｜{ローマ,ロンドン,ヘルシンキ,ローマ}｜ (3,0)| (3,0)
 0｜11｜300｜{ ローマ,ヘルシンキ,バルセロナ,ローマ}｜ (3,0)| (3,0)
 0｜9｜620｜｛ローマ、パリ、ヘルシンキ、ローマ｝｜ (3,0)| (3,0)
 0｜11｜680｜｛ローマ,ロンドン,バルセロナ,ローマ｝｜ (3,0)| (3,0)
(15行)``クリップボードにコピー
```

ordercol` カラムはタプル `(A,B)` を含み、最初の項目はレベルを表し、2 番目の項目は最新の `city_id` を表す。例えば、`(2,0)`は2回の旅行を含み、`Rome` (`city_id=0`)で終了することを示し、同じ情報は`{Rome,Paris,Rome}`を含むjourney stops列で見ることができる。


BREADTH` 節を `DEPTH` 節に置き換えてみると、最初の `15` 件の旅行が旅行経路順に表示される。



```
 city_id｜trip_id｜total_price｜in_eur｜ journey_stops｜ ordercol
---------+---------+--------------------+-----------------------------------------------------+-------------------------------
 0 | | 0 | {ローマ｝| {(0)}
 1｜1｜200｜{ローマ,ロンドン}｜{(0),(1)| {(0),(1)}
 0｜4｜320｜{ローマ,ロンドン,ローマ}｜{(0),(1)
 1｜1｜520｜{Rome,London,Rome,London}｜{(0,(1))| {(0),(1),(0),(1)}
 0｜4｜640｜｛ローマ,ロンドン,ローマ,ロンドン,ローマ｝｜{(0),(1),(0),(1),(0),(1)| {(0),(1),(0),(1),(0)}
 3｜3｜790｜{ローマ,ロンドン,ローマ,ロンドン,ローマ,ヘルシンキ}｜{(0),(1),(0),(1),(0)
 2｜2｜570｜{ローマ,ロンドン,ローマ,パリ}｜{(0),(1),(0),(1),(0),(3)| {(0),(1),(0),(2)}
 0｜7｜740｜{ローマ,ロンドン,ローマ,パリ,ローマ}｜{(0),(1),(0),(2)| {(0),(1),(0),(2),(0)}
 3｜3｜470｜{Rome,London,Rome,Helsinki}｜{(0,(1),(0),(2),(0)| {(0),(1),(0),(3)}
 0｜9｜520｜{ローマ,ロンドン,ローマ,ヘルシンキ,ローマ}｜{(0),(1),(0),(0),(3)| {(0),(1),(0),(3),(0)}
 1｜1｜720｜{ローマ,ロンドン,ローマ,ヘルシンキ,ローマ,ロンドン}｜{(0),(1),(0),(3),(0)| {(0),(1),(0),(3),(0),(1)}
 2｜2｜770｜{Rome,London,Rome,Helsinki,Rome,Paris}｜{(0,(1),(0),(3),(0),(1)| {(0),(1),(0),(3),(0),(2)}
 3｜3｜670｜{ローマ,ロンドン,ローマ,ヘルシンキ,ローマ,ヘルシンキ}｜{(0),(1),(0),(3),(0),(2)| {(0),(1),(0),(3),(0),(3)}
 0｜9｜720｜{ローマ,ロンドン,ローマ,ヘルシンキ,ローマ,ヘルシンキ,ローマ}｜{(0),(1),(0),(3),(0),(3)| {(0),(1),(0),(3),(0),(3),(0)}
 | {(0),(1),(0),(3),(0),(3),(4)}
(15行)`クリップボードにコピー
```

例えば、`{(0),(1),(0),(2)}`は、`journey_stops`カラムで確認できるように、`ローマ->ロンドン->ローマ->パリ`を旅行することを意味する。返される行の順序は `ordercol` に従う。


## CYCLE オプションでループを回避する。


ローマ->ロンドン->ローマ->パリの旅は美しいでしょう？ああ、おそらくあなたは同じ都市を何度も通過するのが好きではないだろう。ループは非常に非効率的な移動方法なので、可能な限り避けるべきです。幸運なことに、PostgreSQL 14の`CYCLE`オプションはループをスキップする方法を提供しています。


元の再帰問い合わせで、`select * from trip_journey`を以下のように置き換えてください：



```
CYCLE city_id SET is_cycle USING journey_ids
select ˶* from trip_journey where is_cycle=false;`Copy to clipboard
```

上記は、再帰クエリにいくつかのカラムを追加する：


* journey_ids`は `city_id`のシーケンスを `ARRAY` に格納したものである。
* 現在の `city_id` が既に `journey_ids` カラムにあるかどうかをチェックすることで、`is_cycle` フラグを立てる。


is_cycle=false`でフィルタリングされたクエリ結果は、予算内で可能なすべてのループしない旅行のリストを提供する。



```
city_id｜trip_id｜total_price｜in_eur｜ journey_stops｜ is_cycle｜ journey_ids
---------+---------+--------------------+----------------------------------+----------+-------------------
 0｜｜0｜{Rome}| f | {(0)}
 1｜1｜200｜{ローマ,ロンドン}｜f｜{(0), (0)| f | {(0),(1)}
 2｜2｜250｜{ローマ,パリ}｜f｜{(0),(1)| f | {(0),(2)}
 3｜3｜150｜{ローマ,ヘルシンキ}｜f｜{(0),(1)}。| f | {(0),(3)}
 | f | {(0),(1),(3)}
 4｜6｜650｜{ ローマ,ロンドン,バルセロナ}｜f｜{(0),(1),(3)| f | {(0),(1),(4)}
 3｜8｜570｜{ローマ,パリ,ヘルシンキ}｜f｜{(0),(1),(3)}。| f | {(0),(2),(3)}
 4｜10｜270｜{ローマ,ヘルシンキ,バルセロナ}｜f｜{(0),(2),(3)}。| f | {(0),(3),(4)}
 4｜10｜690｜{ローマ,パリ,ヘルシンキ,バルセロナ}｜f｜{(0),(3),(4)}。| f | {(0),(2),(3),(4)}
 4｜10｜670｜{ローマ,ロンドン,ヘルシンキ,バルセロナ}｜f｜{(0),(2),(3),(4)}。| f | {(0),(1),(3),(4)}
 1｜12｜770｜{ローマ,ヘルシンキ,バルセロナ,ロンドン}｜f｜{(0),(1),(3),(4)}。| f | {(0),(3),(4),(1)}
(11行)`クリップボードにコピー
```

例えば、`{ローマ,ヘルシンキ,バルセロナ,ロンドン}`と`{ローマ,ロンドン,ヘルシンキ,バルセロナ}`は同じ都市を含むが、前者の方が100ユーロ安い。


## 帰国


どんな旅にも家に帰りたくなる瞬間があるものだが、上記の旅を確認すると、ループを避けているので、再び愛する`ローマ`に行き着くことはあり得ない。


これを実現するために、元のクエリでは、`trips`テーブルとの追加の結合を考慮する必要があります：



```
再帰的なtrip_journey(
 city_id、
 trip_id、
 total_price_in_eur、
 journey_stops、
 journey_prices、
 return_price
 )
として
 select
 city_id as city_id、
 null::int、
 0 price_in_eur、
 ARRAY[city_name] as journey_name、
 ARRAY[0::int] as journey_price、
 0 return_price
 都市から
 where city_id=0
 UNION ALL
 選択
 trips.city_bache_id、
 trips.trip_id、
 tj.total_price\_in_eur + trips.price_in_eur、
 tj.journey_stops || city_b.city_name、
 tj.journey_prices || trips.price_in_eur、
 return_trips.price_in_eur
 from trip_journey tj join trips on tj.city_id = trips.city_a_id
 join cities city_a on trips.city_a_id = city_a.city_id
 join cities city_b on trips.city_bashi_id = city_b.city_id
 join trips return_trips on trips.city_bache_id = return_trips.city_aache_id and return_trips.city_bache_id = 0
 ここで、tj.total_price\_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800
 )CYCLE city_id SET is_cycle USING journey_ids
select ˶* from trip_journey where is_cycle=false;`Copy to clipboard
```

join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0` セクションでは、`Rome`（`city_id=0`）への復路も含まれていることを確認し、`tj.total_price_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800` ステートメントでは、予算との照合に復路の費用が含まれていることを確認しています。


その結果、予算内に`ローマ`への帰路が含まれる10通りの旅程が表示された。



```
city_id | trip_id | total_price_in_eur | journey_stops | journey_prices | return_price | is_cycle | journey_ids
---------+---------+--------------------+----------------------------------+-----------------+--------------+----------+-------------------
 0 | | 0 | {ローマ｝| {0}| 0 | f | {(0)}
 1｜1｜200｜{ローマ,ロンドン}｜{0,200}｜{0,200| {0,200}| 120 | f | {(0),(1)}
 2｜2｜250｜{ローマ,パリ}｜{0,250}｜{ローマ,パリ| {0,250}| 170 | f | {(0),(2)}
 3｜3｜150｜{ローマ,ヘルシンキ}｜{0,150}｜3｜150｜{ローマ,ヘルシンキ| {0,150}| 50 | f | {(0),(3)}
 3｜5｜550｜{ ローマ、ロンドン、ヘルシンキ}｜0,200,350| {0,200,350}| 50 | f | {(0),(1),(3)}
 4 | 6 | 650 | {ローマ,ロンドン,バルセロナ} {0,200,450| {0,200,450}| 30 | f | {(0),(1),(4)}
 3｜8｜570｜{ ローマ,パリ,ヘルシンキ}｜{0,250,320}｜{0,250,320| {0,250,320}| 50 | f | {(0),(2),(3)}
 4｜10｜270｜{ ローマ,ヘルシンキ,バルセロナ}｜{0,150,120}｜{0,150,120| {0,150,120}| 30 | f | {(0),(3),(4)}
 4｜10｜690｜{ローマ,パリ,ヘルシンキ,バルセロナ}｜{0,250,320,4| {0,250,320,120}| 30 | f | {(0),(2),(3),(4)}
 4｜10｜670｜{ローマ,ロンドン,ヘルシンキ,バルセロナ}｜{0,200,350,120}｜{0,250,320,120}｜30｜f| {0,200,350,120}| 30 | f | {(0),(1),(3),(4)}
(10行)``クリップボードにコピー
```

## まとめ


新しい `SEARCH` と `CYCLE` オプションは、再帰クエリの動作をよりエレガントに定義する新しい方法を提供する。これを利用するためのリソースをいくつか紹介します：


* WITH Queries (Common Table Expression)[ in PostgreSQL](https://www.postgresql.org/docs/14/queries-with.html) に `SEARCH` と `CYCLE` のドキュメントがあります。
* 以前のバージョンのPostgreSQLで検索パターンを定義し、サイクルを回避する方法を確認することができます。
* [Aiven for PostgreSQL](https://aiven.io/postgresql)では、AivenがマネージドサービスとしてPostgreSQLに提供しているものを確認することができます。


## 次のステップ


次のステップとしては、[Aiven for PostgreSQL](https://aiven.io/postgresql)をチェックアウトすることです。単なる思いつきです。


まだAivenのサービスをご利用でない方は、<https://console.aiven.io/signup>から無料トライアルにお申し込みください！


それまでは、[changelog](https://aiven.io/changelog)と[blog](https://aiven.io/blog)のRSSフィード、または[LinkedIn](https://www.linkedin.com/company/aiven/)と[Twitter](https://twitter.com/aiven_io)のアカウントをフォローして、製品や機能関連の最新情報をご確認ください。


## 続きを読む


* [PostgreSQL®とは](https://aiven.io/blog/an-introduction-to-postgresql)
* (Postgre)[SQLの概念と用語](https://aiven.io/blog/postgresql-concepts-and-terms)
* リモートリードレプリカ - 何を、なぜ、[どのように](https://aiven.io/blog/postgresql-remote-read-replica)