探索 PostgreSQL? 14 中的新功能 - SEARCH 和 CYCLE
使用遞歸查詢？在較早的博客文章的此更新中查看 PostgreSQL 14 中可用的新 SEARCH 和 CYCLE 功能。
假期、旅行、快樂時光總是在我們的腦海中，幾個月前我們看到了如何Solving the knapsack problem in PostgreSQL。然而，博客文章并不總是像葡萄酒一樣陳年。發布日期幾周后，PostgreSQL 14 發布了，它包含了幾個非常有趣的新特性：CYCLE和SEARCH. 它們大大簡化了我們編寫遞歸查詢的方式。這篇文章給出了幾個例子，基于一個最喜歡的話題：旅行計劃！
創建數據庫
本文中的示例適用于任何 PostgreSQL 14 或更高版本的數據庫。我將使用Aiven 管理的 PostgreSQL 服務和 Aiven CLI（查閱安裝和登錄的專用文檔)。這是創建數據庫的 CLI 命令：
avn service create holidays-pg \ --service-type pg \ --plan hobbyist \ --cloud google-europe-west3 \ -c pg_version=14
上面創建了一個在google-europe-west3區域上的名為holidays-pg的PostgreSQL 14 (-c pg_version=14 ) 服務，具有最小的hobbyist計劃。這對我們的測試來說已經足夠了。要檢查我們提供的工具的版本，請使用專用文檔中記錄的avn service versions命令。
需要一點時間來啟動PostgreSQL 14 數據庫并運行。可以打開我們最喜歡的旅游指南并開始瀏覽目的地。同時，我們可以通過以下方式關注服務創建任務的進度：
avn service wait holidays-pg
上述命令將定期請求服務器的狀態，直到啟動。一旦它返回，我們就可以通過以下方式連接到我們的holidays-pgPostgreSQL 14 服務：
avn service cli holidays-pg
創建數據集
我們想穿越歐洲，在預算范圍內參觀一些主要城市。
為了存儲想要參觀的城市，我們創建了一個cities表，并插入挑選的城市。
create table cities(
city_id int primary key,
city_name varchar
);
insert into cities values (0, 'Rome'),
(1, 'London'),
(2, 'Paris'),
(3, 'Helsinki'),
(4, 'Barcelona');
如何在城市之間旅行呢？很容易，我們前往旅行預訂網站，并找到對應的城市。通常我們會返回這樣的圖表：
為了在 PostgreSQL 中表示上述信息，我們創建了一個trips表，出發地 ( city_a_id)、目的地 ( city_b_id) 和以歐元為單位的旅行費用 ( price_in_eur)
create table 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
);
insert into trips values
(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表之間進行連接。
select
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_b_id = dst.city_id
where src.city_name='Rome';
查詢結果與上圖顯示的信息相同：可以直接到達London，Paris以及Helsinki
city_name | city_name | price_in_eur
-----------+-----------+--------------
Rome | London | 200
Rome | Paris | 250
Rome | Helsinki | 150
(3 rows)
為旅程添加更多的落腳點
好的，下一步在哪里？我們可以使用遞歸查詢來遍歷所有可能的組合。
假如有足夠的錢，我們就可以環游世界。用數據庫術語來解釋，這意味著我們在遞歸查詢中可能有無限循環。為了避免無線循環，設定一個800歐元的總預算。
為旅程編寫遞歸查詢，如下所示：
with recursive trip_journey(
city_id,
trip_id,
total_price_in_eur,
journey_stops
)
as (
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
select
trips.city_b_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_b_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800
)
select * from trip_journey;
讓我們分解一下。第一部分陳述了起點：我們要從Rome( city_id=0) 開始。如果我們不去旅行，那trip_id就是null，總成本是0。
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
然后我們開始添加旅行，使用遞歸部分，將先前定義trip_journey的與trips表連接起來，以發現所有可能的目的地以及對應的成本。
UNION ALL
select
trips.city_b_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_b_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800
將city_b.city_name包含在journey_stops中。 然后，將之前的總費用和當前的行程價格相加來計算總行程成本 ( tj.total_price_in_eur + trips.price_in_eur)。最后，通過where子句限制總預算在800歐以內
查詢結果 89 行，從不旅行（留在Rome）開始，到長途旅行{Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}跨越多個城市。
city_id | trip_id | total_price_in_eur | journey_stops
---------+---------+--------------------+-----------------------------------------------------------------
0 | | 0 | {Rome}
1 | 1 | 200 | {Rome,London}
2 | 2 | 250 | {Rome,Paris}
3 | 3 | 150 | {Rome,Helsinki}
0 | 4 | 320 | {Rome,London,Rome}
3 | 5 | 550 | {Rome,London,Helsinki}
....
4 | 10 | 770 | {Rome,Helsinki,Rome,Helsinki,Barcelona,Rome,Helsinki,Barcelona}
0 | 11 | 700 | {Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}
(89 rows)
使用 SEARCH 選項定義搜索路徑
上面的89行總結了所有可能的行程。但是該數據集是如何進行排序的呢？PostgreSQL14中，SEARCH選項提供了一種新的方法來定義遞歸查詢方式：
-
如果想根據參觀城市的數量來安排行程，可以使用
BREADTH選項。首先看到涉及 0 站的行程，然后是涉及 1 站、2 站等的行程。
-
如果想根據旅行路徑來安排行程，可以使用
DEPTH選項。可以看到行程的每一步都在擴展，例如
{Rome}->{Rome->London}->{Rome->London->Helsinki}直到找到旅程的最大深度，然后它將搜索樹的連續分支。
BREADTH示例：
上述遞歸查詢只需將最后一條select * from trip_journey語句替換為以下內容：
SEARCH BREADTH FIRST BY city_id SET ordercol
select * from trip_journey order by ordercol limit 15;
為了節省計算量，不打算掃描整個結果集，將查詢限制為僅返回前 15 行limit 15。因為我們正在使用BREADTH選項，所以結果集仍按站點數排序。
city_id | trip_id | total_price_in_eur | journey_stops | ordercol
---------+---------+--------------------+--------------------------------+----------
0 | | 0 | {Rome} | (0,0)
1 | 1 | 200 | {Rome,London} | (1,1)
2 | 2 | 250 | {Rome,Paris} | (1,2)
3 | 3 | 150 | {Rome,Helsinki} | (1,3)
0 | 4 | 320 | {Rome,London,Rome} | (2,0)
0 | 9 | 200 | {Rome,Helsinki,Rome} | (2,0)
0 | 7 | 420 | {Rome,Paris,Rome} | (2,0)
3 | 5 | 550 | {Rome,London,Helsinki} | (2,3)
3 | 8 | 570 | {Rome,Paris,Helsinki} | (2,3)
4 | 6 | 650 | {Rome,London,Barcelona} | (2,4)
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | (2,4)
0 | 9 | 600 | {Rome,London,Helsinki,Rome} | (3,0)
0 | 11 | 300 | {Rome,Helsinki,Barcelona,Rome} | (3,0)
0 | 9 | 620 | {Rome,Paris,Helsinki,Rome} | (3,0)
0 | 11 | 680 | {Rome,London,Barcelona,Rome} | (3,0)
(15 rows)
ordercol列包含一個元組(A,B)，其中第一項表示級別，第二項表示最新city_id。例如(2,0)，表示旅程包括兩次行程，并以Rome( city_id=0) 結尾，相同的信息可以在包含的旅程停靠點列中找到{Rome,Paris,Rome}。
用DEPTH替換BREADTH子句，會得到按旅行路徑排序的前15條組合，逐步搜索如何擴展行程。
city_id | trip_id | total_price_in_eur | journey_stops | ordercol
---------+---------+--------------------+-----------------------------------------------------+-------------------------------
0 | | 0 | {Rome} | {(0)}
1 | 1 | 200 | {Rome,London} | {(0),(1)}
0 | 4 | 320 | {Rome,London,Rome} | {(0),(1),(0)}
1 | 1 | 520 | {Rome,London,Rome,London} | {(0),(1),(0),(1)}
0 | 4 | 640 | {Rome,London,Rome,London,Rome} | {(0),(1),(0),(1),(0)}
3 | 3 | 790 | {Rome,London,Rome,London,Rome,Helsinki} | {(0),(1),(0),(1),(0),(3)}
2 | 2 | 570 | {Rome,London,Rome,Paris} | {(0),(1),(0),(2)}
0 | 7 | 740 | {Rome,London,Rome,Paris,Rome} | {(0),(1),(0),(2),(0)}
3 | 3 | 470 | {Rome,London,Rome,Helsinki} | {(0),(1),(0),(3)}
0 | 9 | 520 | {Rome,London,Rome,Helsinki,Rome} | {(0),(1),(0),(3),(0)}
1 | 1 | 720 | {Rome,London,Rome,Helsinki,Rome,London} | {(0),(1),(0),(3),(0),(1)}
2 | 2 | 770 | {Rome,London,Rome,Helsinki,Rome,Paris} | {(0),(1),(0),(3),(0),(2)}
3 | 3 | 670 | {Rome,London,Rome,Helsinki,Rome,Helsinki} | {(0),(1),(0),(3),(0),(3)}
0 | 9 | 720 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Rome} | {(0),(1),(0),(3),(0),(3),(0)}
4 | 10 | 790 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Barcelona} | {(0),(1),(0),(3),(0),(3),(4)}
(15 rows)
ordercol包含city_id的串聯列表，例如，journey_stops列{(0),(1),(0),(2)}表示我們將按照Rome->London->Rome->Paris的方式旅行。返回的行順序遵循ordercol
使用 CYCLE 選項避免循環
Rome->London->Rome->Paris是一段美好的旅程么？啊，可能你并不喜歡多次經過同一個城市。循環是一種非常低效的旅行方式，我們應該盡可能避免。幸運的是，PostgreSQL 14CYCLE選項提供了一種跳過它們的方法。
在原始遞歸查詢中，用下面的語句替換select * from trip_journey：
CYCLE city_id SET is_cycle USING journey_ids
select * from trip_journey where is_cycle=false;
以上為遞歸查詢增加了幾列：
journey_ids在ARRAY中包含city_id的序列is_cycle通過檢查當前city_id是否已經在journey_ids列中來標記循環
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 | {Rome,London} | f | {(0),(1)}
2 | 2 | 250 | {Rome,Paris} | f | {(0),(2)}
3 | 3 | 150 | {Rome,Helsinki} | f | {(0),(3)}
3 | 5 | 550 | {Rome,London,Helsinki} | f | {(0),(1),(3)}
4 | 6 | 650 | {Rome,London,Barcelona} | f | {(0),(1),(4)}
3 | 8 | 570 | {Rome,Paris,Helsinki} | f | {(0),(2),(3)}
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | f | {(0),(3),(4)}
4 | 10 | 690 | {Rome,Paris,Helsinki,Barcelona} | f | {(0),(2),(3),(4)}
4 | 10 | 670 | {Rome,London,Helsinki,Barcelona} | f | {(0),(1),(3),(4)}
1 | 12 | 770 | {Rome,Helsinki,Barcelona,London} | f | {(0),(3),(4),(1)}
(11 rows)
避開環路后，我們還可以比較行程：例如，行程{Rome,Helsinki,Barcelona,London}和{Rome,London,Helsinki,Barcelona}包含相同的城市，但第一個便宜 100 歐元。
回程
旅行結束后回家是一個開心的時刻，但是，如果檢查上面的旅行，因為避免了循環，不可能再次回到Rome。
為了實現這一點，在原始查詢中，我們需要考慮與trips表的額外連接，每次旅程還增加了返回Rome的費用，可以查看下面的完整查詢：
with recursive trip_journey(
city_id,
trip_id,
total_price_in_eur,
journey_stops,
journey_prices,
return_price
)
as (
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
from cities
where city_id=0
UNION ALL
select
trips.city_b_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_b_id = city_b.city_id
join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0
where 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;
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 次可能的旅程，其中包括在預算中的返回Rome的行程。
city_id | trip_id | total_price_in_eur | journey_stops | journey_prices | return_price | is_cycle | journey_ids
---------+---------+--------------------+----------------------------------+-----------------+--------------+----------+-------------------
0 | | 0 | {Rome} | {0} | 0 | f | {(0)}
1 | 1 | 200 | {Rome,London} | {0,200} | 120 | f | {(0),(1)}
2 | 2 | 250 | {Rome,Paris} | {0,250} | 170 | f | {(0),(2)}
3 | 3 | 150 | {Rome,Helsinki} | {0,150} | 50 | f | {(0),(3)}
3 | 5 | 550 | {Rome,London,Helsinki} | {0,200,350} | 50 | f | {(0),(1),(3)}
4 | 6 | 650 | {Rome,London,Barcelona} | {0,200,450} | 30 | f | {(0),(1),(4)}
3 | 8 | 570 | {Rome,Paris,Helsinki} | {0,250,320} | 50 | f | {(0),(2),(3)}
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | {0,150,120} | 30 | f | {(0),(3),(4)}
4 | 10 | 690 | {Rome,Paris,Helsinki,Barcelona} | {0,250,320,120} | 30 | f | {(0),(2),(3),(4)}
4 | 10 | 670 | {Rome,London,Helsinki,Barcelona} | {0,200,350,120} | 30 | f | {(0),(1),(3),(4)}
(10 rows)
總結
新的SEARCH和CYCLE選項提供了一種新的、更優雅的定義遞歸查詢行為的方式。
- PostgreSQL 中的 WITH 查詢（公用表達式），您可以在其中找到
SEARCH和CYCLE文檔 - Solving the knapsack problem in PostgreSQL，您可以在其中檢查如何定義搜索模式并避免以前 PostgreSQL 版本中的循環
- Aiven for PostgreSQL檢查 Aiven 為 PostgreSQL 提供的托管服務。
原文地址：https://aiven.io/blog/explore-the-new-search-and-cycle-features-in-postgresql-14
原文作者：Francesco Tisiot
