Explore the new SEARCH and CYCLE features in PostgreSQL® 14の翻訳です。
2021年12月14日
PostgreSQL® 14の新機能SEARCHとCYCLEを試す
再帰問い合わせを使用していますか?PostgreSQL 14で利用可能な新しいSEARCHとCYCLE機能を、以前のブログ記事の更新で確認してください。
休日、旅行、楽しい時間はいつも私たちの頭の中にあります。数ヶ月前、PostgreSQL再帰問い合わせを使用したナップザック問題を解決する方法を見ました。しかし、ブログ記事はいつもワインのように熟成するわけではありません。公開日から数週間後、PostgreSQL 14が発表され、実に興味深い新機能がいくつか含まれています：CYCLEとSEARCH`です。これらは再帰問い合わせの記述方法を大幅に単純化します。この記事では、好きなトピックである旅行計画に基づいたいくつかの例を紹介します！
データベースの作成
この記事の例は、PostgreSQL 14またはそれ以降のデータベースで動作します。ここではシンプルに、Aiven-managed PostgreSQL serviceとAiven CLIを使用します（インストールとログイン方法については専用ドキュメントを確認してください）。以下はデータベースを作成する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) サービスを作成します。これでテストは十分である。私たちが提供するツールのバージョンを確認するには、専用ページに記載されている avn service versions コマンドを使用する。
これでPostgreSQL 14データベースが立ち上がるまで数分の待ち時間ができた。お気に入りの旅行ガイドを開き、目的地をブラウズし始めることができる。その間に、サービス作成タスクの進捗を見守ることができます：
avn service wait holidays-pg`クリップボードにコピーする
上記のコマンドは、サーバーが起動するまで定期的にサーバーの状態を要求する。これが返ってきたら、holidays-pg PostgreSQL 14 サービスに接続する準備ができたことになる：
avn service cli holidays-pg`クリップボードにコピーする
データセットを作成する
我々はヨーロッパを横断し、予算内でいくつかの主要都市を訪れたい。これが以前議論したナップザック問題のバリエーションであることがわかるだろうか？一見異なる問題が，似たような戦略で解決できるのは，いつも興味深い．
訪問したい都市を保存するために、citiesテーブルを作成し、選んだ場所で埋める。
citiesテーブルを作成する(
city_id int primary key、
city_name varchar
);
citiesに値を挿入 (0, 'Rome')、
(1, 'London')、
(2, 'Paris')、
(3, 'ヘルシンキ')、
(4, 'Barcelona');`Copy to clipboard
どうやって都市間を移動するのか？簡単だ。旅行予約サイトにアクセスして、乗り継ぎと各旅行代金を調べる。通常、このようなグラフが返ってくる：
上記の情報を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ユーロに設定しましょう。
前の記事から学ぶと、旅の再帰クエリはこのように書くことができる：
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 に
SEARCHとCYCLEのドキュメントがあります。 - 以前のバージョンのPostgreSQLで検索パターンを定義し、サイクルを回避する方法を確認することができます。
- Aiven for PostgreSQLでは、AivenがマネージドサービスとしてPostgreSQLに提供しているものを確認することができます。
次のステップ
次のステップとしては、Aiven for PostgreSQLをチェックアウトすることです。単なる思いつきです。
まだAivenのサービスをご利用でない方は、https://console.aiven.io/signupから無料トライアルにお申し込みください！
それまでは、changelogとblogのRSSフィード、またはLinkedInとTwitterのアカウントをフォローして、製品や機能関連の最新情報をご確認ください。
続きを読む
- PostgreSQL®とは
- (Postgre)SQLの概念と用語
- リモートリードレプリカ - 何を、なぜ、どのように