探索PostgreSQL 14新特性--SEARCH和CYCLE

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 探索PostgreSQL 14新特性--SEARCH和CYCLE

探索PostgreSQL 14新特性--SEARCH和CYCLE


PG14的SEARCH和CYCLE新功能大大简化了递归查询的方式,本文给出一些基于旅行计划的示例。


创建数据库


本文示例基于任何PG14或更新版本的数据库。使用Aiven分支的服务及CLI。下面是创建数据库的CLI命令:


avn service create holidays-pg \
    --service-type pg \
    --plan hobbyist \
    --cloud google-europe-west3 \
    -c pg_version=14

上述在google-europe-west3区创建一个PG14(-c pg_version=14)的服务,名为holidays-pg,并使用最小的hobbyist计划。足够我们测试。使用avn service versions命令检查提供工具的版本。

需要等待几分钟以上PG14服务启动。可以通过以下方式关注服务创建任务的进度:

avn service wait holidays-pg

使用下面命令连接到holidays-pg的服务:

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');

    我们如何在城市之间旅行?很简单,前往一个旅行预订网站,找到连接以及每次旅行的费用。通常会返回这样的图表:

     

    为了在PG中表示上述信息,创建一个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欧元。


        计划行程


        我们的旅程需要从某个地方开始,既然知道条条道路通罗马,可以选择eternal city作为起点。为了查看可以去哪里旅行,可以在cities和trips表做join。

          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表join以发现所有可能的目的地和相关成本。

                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行很好地总结了我们可以采取的所有可能的路径。但是该数据集是怎么排序的?在PG14中,SEARCH选项提供了一种新的方法定义递归查询:

                  1) 基于stops的个数进行排序,可以使用BREADTH选项。我们会首先看到0 stops的trips然后1依次向后。

                  2) 如果想基于trip路径进行排序,可以使用DEPTH选项。我们将看到旅程在每一步扩展,例如{Rome}-> {Rome->London} -> {Rome->London->Helsinki}直到找到旅程最大深度,然后探索树的连续分支。

                  为了在我们的数据集上提供一个示例,只需要将最后一条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选项,所以结果集按stops排序:


                    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)说明旅行包括2此行程,并以Rome(city_id=0)结束。stops列包含相同信息{Rome,Paris,Rome}。

                    如果使用DEPTH替换BREADTH,可以得到以trip路径排序的前15个trip。

                      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的拼接列表,例如{(0),(1),(0),(2)}表示,我们经过Rome->London->Rome->Paris。返回行的顺序依赖于ordercol。


                      通过CYCLE选项避免循环


                      Rome->London->Rome->Paris的旅途是不是很美好?你可能不喜欢多次经过一个城市。循环时一种非常低效的旅行方式,我们应该尽可能避免。PG14的CYCLE选项提供了一种跳过他们的方法。

                      在原始递归查询中,使用下面语句替换select * from trip_journey:



                      CYCLE city_id SET is_cycle USING journey_ids
                      select * from trip_journey where is_cycle=false;

                      以上为递归查询添加了几列:

                      1) journey_ids以数组ARRAY形式包含city_id的序列

                      2) 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表的额外join。将返回成本添加到每个旅程中,可以用下面查询:


                          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的一个回程。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)

                            Wrapping up


                            SEARCH和CYCLE选项提供了一种新的、更优雅的方式来定义递归查询行为。可以在手册中查找使用方法。


                            原文


                            https://aiven.io/blog/explore-the-new-search-and-cycle-features-in-postgresql-14

                            相关实践学习
                            使用PolarDB和ECS搭建门户网站
                            本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
                            阿里云数据库产品家族及特性
                            阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
                            目录
                            相关文章
                            |
                            存储 关系型数据库 数据库
                            深入了解 PostgreSQL:功能、特性和部署
                            PostgreSQL,通常简称为Postgres,是一款强大且开源的关系型数据库管理系统(RDBMS),它在数据存储和处理方面提供了广泛的功能和灵活性。本文将详细介绍 PostgreSQL 的功能、特性以及如何部署和使用它。
                            792 1
                            深入了解 PostgreSQL:功能、特性和部署
                            |
                            关系型数据库 大数据 PostgreSQL
                            PostgreSQL16-新特性-并行聚合
                            PostgreSQL16-新特性-并行聚合
                            163 0
                            |
                            缓存 监控 关系型数据库
                            [译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
                            [译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
                            268 0
                            |
                            存储 缓存 关系型数据库
                            PostgreSQL 14新特性--减少索引膨胀
                            PostgreSQL 14新特性--减少索引膨胀
                            510 0
                            |
                            存储 SQL JSON
                            大分区表高并发性能提升100倍?阿里云 RDS PostgreSQL 12 特性解读
                            世界上几乎最强大的开源数据库系统 PostgreSQL,于 2019 年 10 月 3 日发布了 12 版本,该版本已经在阿里云正式发布。PostgreSQL 12 在功能和性能上都有很大提升,如大分区表高并发性能提升百倍,B-tree 索引空间和性能优化,实现 SQL 2016 标准的 JSON 特性,支持多列 MCV(Most-Common-Value)统计,内联 CTE(Common table expressions)以及可插拔的表存储访问接口等。本文对部分特性进行解读。
                            3135 0
                            大分区表高并发性能提升100倍?阿里云 RDS PostgreSQL 12 特性解读
                            |
                            存储 SQL Oracle
                            AnalyticDB PostgreSQL 7.0 支持存储过程(CREATE PROCEDURE)特性
                            AnalyticDB PostgreSQL 7.0 新增了存储过程功能的支持,让用户在使用ADB PG时能够更方便高效地开发业务,并能够更好地兼容Oracle等传统数仓的业务。
                            541 1
                            AnalyticDB PostgreSQL 7.0 支持存储过程(CREATE PROCEDURE)特性
                            |
                            SQL 关系型数据库 Linux
                            知识分享之PostgreSQL——OIDS的特性与新版本去除SQL
                            之前一直使用的PostgreSQL 9.6系列版本,由于官方不再维护了,就准备换成最新稳定版本的,查看了一下官方版本说明,发现13系列版本是目前稳定性较好的版本,于是兴冲冲的更换了过来,但随之而来的就是一些新特性,其中就比如表中的OID字段,这个字段是对象标识符,之前能用于行标记,现在发现只有表才具有这个隐藏字段,行数据没有这个支持了,于是就需要将老版本的表进行关闭掉这个字段。下面我们就开始关闭之旅。
                            176 0
                            知识分享之PostgreSQL——OIDS的特性与新版本去除SQL
                            |
                            SQL 存储 JSON
                            PostgreSQL 14 版本特性浅析
                            ## 性能增强 ### 大量连接高并发优化 - 场景: SaaS场景,微服务架构下的中心库场景 - 业务特点:客户端多,在线用户多,数据库并发连接非常多 - 价值: 比连接池网络少1跳, 性能更好, 支持绑定变量等连接池会话模式不支持的全部功能 ### 索引增强 1. 缓解高频更新负载下的btree索引膨胀 - 场景: 数据频繁更新,如游戏、交易、共享出行、IoT等行业 - 价值: 减少膨胀
                            |
                            SQL 存储 缓存
                            从研发角度深入了解RDS AliSQL内核2020新特性
                            内容简要: 一、关于内核 二、内核特性详解 一、 关于内核 (一)回归内核
                            从研发角度深入了解RDS AliSQL内核2020新特性
                            |
                            SQL 存储 缓存
                            从研发角度深入了解RDS AliSQL内核2020新特性 ——楼方鑫(黄忠)
                            从研发角度深入了解RDS AliSQL内核2020新特性 ——楼方鑫(黄忠)
                            从研发角度深入了解RDS AliSQL内核2020新特性    ——楼方鑫(黄忠)