PostgreSQL 图式搜索(graph search)实践 - 百亿级图谱,毫秒响应

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 标签 PostgreSQL , CTE , 递归查询 , cycle , depth , loop , deep , level , 层级 , array , row array , JSON 背景 图式搜索是PostgreSQL在(包括流计算、全文检索、图式搜索、K-V存储、图像搜索、指纹搜索、空间数据、时序数据、推荐等)诸多特性中的一个。

标签

PostgreSQL , CTE , 递归查询 , cycle , depth , loop , deep , level , 层级 , array , row array , JSON


背景

图式搜索是PostgreSQL在(包括流计算、全文检索、图式搜索、K-V存储、图像搜索、指纹搜索、空间数据、时序数据、推荐等)诸多特性中的一个。

采用CTE语法,可以很方便的实现图式搜索(N度搜索、最短路径、点、边属性等)。

其中图式搜索中的:层级深度,是否循环,路径,都是可表述的。

pic

pic

例子

创建1000万用户,每5万作为一个有牵连的群体,平均每个用户牵连500个用户,形成50亿的大规模关系网。

在此基础上,演示如下

1、如何实现N度搜索,边的属性查看,以及最短路径搜索等需求。

2、如何去除循环点,如何控制深度,如何展示路径等。

3、如何生成绘图数据。

创建50亿测试数据

创建1000万用户,每5万作为一个有牵连的群体,平均每个用户牵连500个用户,形成50亿的大规模关系网。

1、建表,表结构如下,可以描述点、边。

create table a(    
  c1 int,                -- 点1    
  c2 int,                -- 点2    
  prop jsonb,            -- 点1,2对应的边的属性,使用JSON存储,包括权重,关系等等。    
  primary key (c1,c2)    -- 主键    
);    
    
create index idx_a_2 on a(c1, COALESCE(((prop ->> 'weight'::text))::float8, 0));  

2、生成测试数据:

vi test.sql    
    
\set id random(1,10000000)    
insert into a select :id, ((width_bucket(:id,1,10000000,2000)-1)*50000 + (random()*50000)::int) from generate_series(1,1000) on conflict (c1,c2) do nothing;    
pgbench -M prepared -n -r -P 5 -f ./test.sql -c 50 -j 50 -t 100000      

3、数据约340GB

如何去除循环点、控制深度、展示路径

1、当路径中重复出现某个点时,说明发生了循环。

2、每递归一次,深度加1。

3、使用数组存储路径。单列数组,或多列(ROW数组),多列路径参考: https://www.postgresql.org/docs/10/static/queries-with.html

SQL如下:

WITH RECURSIVE search_graph(    
  c1,   -- 点1    
  c2,   -- 点2    
  prop, -- 边的属性    
  depth, -- 深度,从1开始    
  path,  -- 路径,数组存储    
  cycle  -- 是否循环    
) AS (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1,        -- 初始深度=1    
          ARRAY[g.c1],   -- 初始路径    
          false          -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = ?         -- ROOT节点=?    
      UNION ALL    
        SELECT     -- 递归子句    
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1,    -- 深度+1    
          path || g.c1,    -- 路径中加入新的点    
          g.c1 = ANY(path)    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
          AND sg.depth <= ?    -- 搜索深度=?       
)    
SELECT * FROM search_graph;    -- 查询递归表,可以加LIMIT输出,也可以使用游标    

N度搜索

N度搜索,如上SQL,输入sg.depth <= N。

例如,搜索ROOT=31208的三度数据。

WITH RECURSIVE search_graph(    
  c1,   -- 点1    
  c2,   -- 点2    
  prop, -- 边的属性    
  depth, -- 深度,从1开始    
  path,  -- 路径,数组存储    
  cycle  -- 是否循环    
) AS (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1,        -- 初始深度=1    
          ARRAY[g.c1],   -- 初始路径    
          false          -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = 31208         -- ROOT节点=?    
      UNION ALL    
        SELECT     -- 递归子句    
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1,    -- 深度+1    
          path || g.c1,    -- 路径中加入新的点    
          g.c1 = ANY(path)    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
          AND sg.depth <= 3    -- 搜索深度=?       
)    
SELECT * FROM search_graph;    -- 查询递归表,可以加LIMIT输出,也可以使用游标    

最短路径

去掉搜索深度,并且在查询递归表的语句中,加上WHERE条件(过滤C2)以及LIMIT 1 即可。

SQL如下:

WITH RECURSIVE search_graph(    
  c1,   -- 点1    
  c2,   -- 点2    
  prop, -- 边的属性    
  depth, -- 深度,从1开始    
  path,  -- 路径,数组存储    
  cycle  -- 是否循环    
) AS (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1,        -- 初始深度=1    
          ARRAY[g.c1],   -- 初始路径    
          false          -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = ?         -- ROOT节点=?      --(最短路径的起点)    
      UNION ALL    
        SELECT     -- 递归子句    
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1,    -- 深度+1    
          path || g.c1,    -- 路径中加入新的点    
          g.c1 = ANY(path)    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
--        AND sg.depth <= ?    -- 搜索深度=?    
)    
SELECT * FROM search_graph    
  where c2 = ?   -- 最短路径的终点    
  limit 1;       -- 查询递归表,可以加LIMIT输出,也可以使用游标    

例如搜索 1 到 100 的最短路径。

WITH RECURSIVE search_graph(    
  c1,   -- 点1    
  c2,   -- 点2    
  prop, -- 边的属性    
  depth, -- 深度,从1开始    
  path,  -- 路径,数组存储    
  cycle  -- 是否循环    
) AS (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1,        -- 初始深度=1    
          ARRAY[g.c1],   -- 初始路径    
          false          -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = 1         -- ROOT节点=?      --(最短路径的起点)    
      UNION ALL    
        SELECT     -- 递归子句    
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1,    -- 深度+1    
          path || g.c1,    -- 路径中加入新的点    
          g.c1 = ANY(path)    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
--        AND sg.depth <= ?    -- 搜索深度=?    
)    
SELECT * FROM search_graph    
  where c2 = 100   -- 最短路径的终点    
  limit 1;         -- 查询递归表,可以加LIMIT输出,也可以使用游标    

如果要控制深度,比如5度以内搜不到就不搜了,把搜索深度的条件再加进去即可。

如何生成绘图数据

为了提高响应速度,使用游标返回。

begin;    
declare cur1 cursor for $query;    
FETCH 1000 from cur1;    
....    
close cur1;    
end;    

响应时间飞快,毫秒级响应。

控制每一层的返回记录数

层级越深,返回的记录就越多,而实际上在图搜索中,并不需要返回每个层级的所有记录,(例如只返回相关性较高的前N条,或者是满足权重大于多少的,前N条),从而控制每个层级的记录数。

WITH RECURSIVE search_graph(    
  c1,     -- 点1    
  c2,     -- 点2    
  prop,   -- 边的属性    
  depth,  -- 深度,从1开始    
  path,   -- 路径,数组存储    
  cycle   -- 是否循环    
) AS (    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1 depth,        -- 初始深度=1    
          ARRAY[g.c1] path,   -- 初始路径    
          false  as cycle        -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = ?             -- ROOT节点=?    
          -- AND coalesce((prop->>'weight')::float8,0) >= ?        -- 相关性权重    
          -- ORDER BY coalesce((prop->>'weight')::float8,0) desc   -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit ?            -- 每个层级限制多少条?    
        ) t    
      UNION ALL    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT     -- 递归子句     
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1 depth,    -- 深度+1    
          path || g.c1 path,    -- 路径中加入新的点    
          (g.c1 = ANY(path)) as cycle    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
          AND sg.depth <= ?    -- 搜索深度=?      
          -- AND coalesce((prop->>'weight')::float8,0) >= ?   -- 相关性权重    
          -- ORDER BY coalesce((prop->>'weight')::float8,0) desc   -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit ?            -- 每个层级限制多少条?               
        ) t    
)    
SELECT * FROM search_graph;    -- 查询递归表,可以加LIMIT输出,也可以使用游标    

例如,搜索root=31208的3度数据,同时限制每个层级返回100条。

WITH RECURSIVE search_graph(    
  c1,     -- 点1    
  c2,     -- 点2    
  prop,   -- 边的属性    
  depth,  -- 深度,从1开始    
  path,   -- 路径,数组存储    
  cycle   -- 是否循环    
) AS (    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1 depth,        -- 初始深度=1    
          ARRAY[g.c1] path,   -- 初始路径    
          false  as cycle        -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = 31208             -- ROOT节点=?    
          -- AND coalesce((prop->>'weight')::float8,0) >= ?   -- 相关性权重    
          -- ORDER BY coalesce((prop->>'weight')::float8,0) desc   -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit 100            -- 每个层级限制多少条?    
        ) t    
      UNION ALL    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT     -- 递归子句     
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1 depth,    -- 深度+1    
          path || g.c1 path,    -- 路径中加入新的点    
          (g.c1 = ANY(path)) as cycle    -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
          AND sg.depth <= 3    -- 搜索深度=?      
          -- AND coalesce((g.prop->>'weight')::float8,0) >= ?   -- 相关性权重    
          -- ORDER BY coalesce((g.prop->>'weight')::float8,0) desc   -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit 100            -- 每个层级限制多少条?               
        ) t    
)    
SELECT * FROM search_graph;    -- 查询递归表,可以加LIMIT输出,也可以使用游标    
   c1    |    c2    | prop | depth |          path          | cycle     
---------+----------+------+-------+------------------------+-------    
   31208 |   300008 |      |     1 | {31208}                | f    
   31208 |   300040 |      |     1 | {31208}                | f    
   31208 |   300046 |      |     1 | {31208}                | f    
   31208 |   300050 |      |     1 | {31208}                | f    
   31208 |   300061 |      |     1 | {31208}                | f    
   31208 |   300082 |      |     1 | {31208}                | f    
   31208 |   300093 |      |     1 | {31208}                | f    
.................
 3032152 | 30347906 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30300272 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30316175 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30309844 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30336508 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30322201 |      |     3 | {31208,300008,3032152} | f    
 3032152 | 30312579 |      |     3 | {31208,300008,3032152} | f    
(300 rows)    
    
Time: 3.245 ms    

响应速度3.2毫秒。(理由很简单,因为每一个层级都是索引命中,结合PG的cluster特性(按c1排序存储),可以降低块数,再次提高性能)

                                                                      QUERY PLAN                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------  
 CTE Scan on search_graph  (cost=25460.78..25482.78 rows=1100 width=77) (actual time=0.044..2.009 rows=300 loops=1)  
   Output: search_graph.c1, search_graph.c2, search_graph.prop, search_graph.depth, search_graph.path, search_graph.cycle  
   Buffers: shared hit=522  
   CTE search_graph  
     ->  Recursive Union  (cost=0.58..25460.78 rows=1100 width=77) (actual time=0.042..1.755 rows=300 loops=1)  
           Buffers: shared hit=522  
           ->  Limit  (cost=0.58..402.52 rows=100 width=77) (actual time=0.040..0.183 rows=100 loops=1)  
                 Output: g.c1, g.c2, g.prop, 1, (ARRAY[g.c1]), false  
                 Buffers: shared hit=97  
                 ->  Index Scan using a_pkey on public.a g  (cost=0.58..393024.56 rows=97782 width=77) (actual time=0.038..0.166 rows=100 loops=1)  
                       Output: g.c1, g.c2, g.prop, 1, ARRAY[g.c1], false  
                       Index Cond: (g.c1 = 31208)  
                       Buffers: shared hit=97  
           ->  Limit  (cost=2249.76..2502.53 rows=100 width=77) (actual time=0.372..0.473 rows=67 loops=3)  
                 Output: g_1.c1, g_1.c2, g_1.prop, ((sg.depth + 1)), ((sg.path || g_1.c1)), ((g_1.c1 = ANY (sg.path)))  
                 Buffers: shared hit=425  
                 ->  Nested Loop  (cost=2249.76..41872589.09 rows=16564685 width=77) (actual time=0.372..0.462 rows=67 loops=3)  
                       Output: g_1.c1, g_1.c2, g_1.prop, (sg.depth + 1), (sg.path || g_1.c1), (g_1.c1 = ANY (sg.path))  
                       Buffers: shared hit=425  
                       ->  WorkTable Scan on search_graph sg  (cost=0.00..22.50 rows=167 width=40) (actual time=0.001..0.011 rows=35 loops=3)  
                             Output: sg.c1, sg.c2, sg.prop, sg.depth, sg.path, sg.cycle  
                             Filter: ((NOT sg.cycle) AND (sg.depth <= 3))  
                       ->  Bitmap Heap Scan on public.a g_1  (cost=2249.76..248006.21 rows=99190 width=40) (actual time=0.010..0.010 rows=2 loops=104)  
                             Output: g_1.c1, g_1.c2, g_1.prop  
                             Recheck Cond: (g_1.c1 = sg.c2)  
                             Heap Blocks: exact=3  
                             Buffers: shared hit=425  
                             ->  Bitmap Index Scan on a_pkey  (cost=0.00..2224.96 rows=99190 width=0) (actual time=0.009..0.009 rows=19 loops=104)  
                                   Index Cond: (g_1.c1 = sg.c2)  
                                   Buffers: shared hit=422  
 Planning time: 0.436 ms  
 Execution time: 2.128 ms  
(32 rows)  
  
Time: 3.301 ms  

压测,TPS:1.2万,平均响应时间5.2毫秒。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 1463760  
latency average = 5.239 ms  
latency stddev = 1.171 ms  
tps = 12196.876360 (including connections establishing)  
tps = 12201.718092 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         5.295  WITH RECURSIVE search_graph(  

图式搜索UDF封装例子

将冗长的SQL封装成UDF,可以简化应用调用的接口。

1、UDF1 ,返回记录

create or replace function graph_search1(  
  IN i_root int,                       -- 根据哪个节点开始搜    
  IN i_depth int  default 99999,       -- 搜索层级、深度限制  
  IN i_limit int8 default 2000000000,  -- 限制每一层返回的记录数  
  IN i_weight float8 default 0,        -- 限制权重  
  OUT o_path int[],                    -- 输出:路径, ID 组成的数组  
  OUT o_point1 int,                    -- 输出:点1 ID  
  OUT o_point2 int,                    -- 输出:点2 ID  
  OUT o_link_prop jsonb,               -- 输出:当前两点之间的连接属性  
  OUT o_depth int                      -- 输出:当前深度、层级  
) returns setof record as $$  
declare  
  sql text;  
begin  
sql := format($_$  
WITH RECURSIVE search_graph(    
  c1,     -- 点1    
  c2,     -- 点2    
  prop,   -- 当前边的属性     
  depth,  -- 当前深度,从1开始     
  path,   -- 路径,数组存储     
  cycle   -- 是否循环     
) AS (    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT                               -- ROOT节点查询    
          g.c1,                              -- 点1    
          g.c2,                              -- 点2    
          g.prop,                            -- 边的属性    
          1 depth,                           -- 初始深度=1    
          ARRAY[g.c1] path,                  -- 初始路径    
          false  as cycle                    -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = %s                                                    -- ROOT节点=?    
          AND coalesce((g.prop->>'weight')::float8,0) >= %s          -- 相关性权重    
          ORDER BY coalesce((g.prop->>'weight')::float8,0) desc      -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit %s                           -- 每个层级限制多少条?    
        ) t    
      UNION ALL    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT                               -- 递归子句     
          g.c1,                              -- 点1    
          g.c2,                              -- 点2    
          g.prop,                            -- 边的属性    
          sg.depth + 1 depth,                -- 深度+1    
          path || g.c1 path,                 -- 路径中加入新的点    
          (g.c1 = ANY(path)) as cycle        -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg      -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2                       -- 递归JOIN条件    
          AND NOT cycle                      -- 防止循环    
          AND sg.depth <= %s                 -- 搜索深度=?      
          AND coalesce((g.prop->>'weight')::float8,0) >= %s          -- 相关性权重    
          ORDER BY coalesce((g.prop->>'weight')::float8,0) desc      -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit %s                           -- 每个层级限制多少条?               
        ) t    
)    
SELECT path||c2 as o_path, c1 as o_point1, c2 as o_point2, prop as o_link_prop, depth as o_depth  
FROM search_graph;                           -- 查询递归表,可以加LIMIT输出,也可以使用游标   
$_$, i_root, i_weight, i_limit, i_depth, i_weight, i_limit  
);  
  
return query execute sql;  
  
end;  
$$ language plpgsql strict;  

使用举例:

postgres=# select * from graph_search1(31208, 3, 100, 0);  
             o_path              | o_point1 | o_point2 | o_link_prop | o_depth   
---------------------------------+----------+----------+-------------+---------  
 {31208,344710}                  |    31208 |   344710 |             |       1  
 {31208,319951}                  |    31208 |   319951 |             |       1  
 {31208,340938}                  |    31208 |   340938 |             |       1  
 {31208,325272}                  |    31208 |   325272 |             |       1  
 {31208,346519}                  |    31208 |   346519 |             |       1  
 {31208,314594}                  |    31208 |   314594 |             |       1  
 {31208,307217}                  |    31208 |   307217 |             |       1  
 {31208,348009}                  |    31208 |   348009 |             |       1  
 {31208,300046}                  |    31208 |   300046 |             |       1  
 {31208,344359}                  |    31208 |   344359 |             |       1  
 {31208,318790}                  |    31208 |   318790 |             |       1  
 {31208,321034}                  |    31208 |   321034 |             |       1  
 {31208,301609}                  |    31208 |   301609 |             |       1  
 {31208,344339}                  |    31208 |   344339 |             |       1  
 {31208,314087}                  |    31208 |   314087 |             |       1  
......  
 {31208,319951,3199647,31991191} |  3199647 | 31991191 |             |       3  
 {31208,319951,3199647,31954904} |  3199647 | 31954904 |             |       3  
 {31208,319951,3199647,31986691} |  3199647 | 31986691 |             |       3  
 {31208,319951,3199647,31986448} |  3199647 | 31986448 |             |       3  
 {31208,319951,3199647,31993624} |  3199647 | 31993624 |             |       3  
 {31208,319951,3199647,31997771} |  3199647 | 31997771 |             |       3  
 {31208,319951,3199647,31982764} |  3199647 | 31982764 |             |       3  
 {31208,319951,3199647,31993420} |  3199647 | 31993420 |             |       3  
 {31208,319951,3199647,31962666} |  3199647 | 31962666 |             |       3  
 {31208,319951,3199647,31957536} |  3199647 | 31957536 |             |       3  
(300 rows)  

2、UDF2,返回游标

create or replace function graph_search2(  
  IN i_root int,                       -- 根据哪个节点开始搜    
  IN i_res name,                       -- 游标名  
  IN i_depth int  default 99999,       -- 搜索层级、深度限制  
  IN i_limit int8 default 2000000000,  -- 限制每一层返回的记录数  
  IN i_weight float8 default 0         -- 限制权重  
) returns refcursor as $$  
declare  
  sql text;  
  res refcursor := i_res;  
begin  
sql := format($_$  
WITH RECURSIVE search_graph(    
  c1,     -- 点1    
  c2,     -- 点2    
  prop,   -- 当前边的属性     
  depth,  -- 当前深度,从1开始     
  path,   -- 路径,数组存储     
  cycle   -- 是否循环     
) AS (    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT                               -- ROOT节点查询    
          g.c1,                              -- 点1    
          g.c2,                              -- 点2    
          g.prop,                            -- 边的属性    
          1 depth,                           -- 初始深度=1    
          ARRAY[g.c1] path,                  -- 初始路径    
          false  as cycle                    -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = %s                                                    -- ROOT节点=?    
          AND coalesce((g.prop->>'weight')::float8,0) >= %s          -- 相关性权重    
          ORDER BY coalesce((g.prop->>'weight')::float8,0) desc      -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit %s                           -- 每个层级限制多少条?    
        ) t    
      UNION ALL    
        select c1,c2,prop,depth,path,cycle from (    
        SELECT                               -- 递归子句     
          g.c1,                              -- 点1    
          g.c2,                              -- 点2    
          g.prop,                            -- 边的属性    
          sg.depth + 1 depth,                -- 深度+1    
          path || g.c1 path,                 -- 路径中加入新的点    
          (g.c1 = ANY(path)) as cycle        -- 是否循环,判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg      -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2                       -- 递归JOIN条件    
          AND NOT cycle                      -- 防止循环    
          AND sg.depth <= %s                 -- 搜索深度=?      
          AND coalesce((g.prop->>'weight')::float8,0) >= %s          -- 相关性权重    
          ORDER BY coalesce((g.prop->>'weight')::float8,0) desc      -- 可以使用ORDER BY,例如返回权重排在前面的N条。    
          limit %s                           -- 每个层级限制多少条?               
        ) t    
)    
SELECT path||c2 as o_path, c1 as o_point1, c2 as o_point2, prop as o_link_prop, depth as o_depth  
FROM search_graph;                           -- 查询递归表,可以加LIMIT输出,也可以使用游标   
$_$, i_root, i_weight, i_limit, i_depth, i_weight, i_limit  
);  
  
open res for execute sql;  
return res;  
  
end;  
$$ language plpgsql strict;  

使用举例,

postgres=# begin;  
BEGIN  
postgres=# select * from graph_search2(31208, 'cur1', 3, 100, 0);  
 graph_search2   
---------------  
 cur1  
(1 row)  
  
postgres=# fetch 10 from cur1;  
     o_path     | o_point1 | o_point2 | o_link_prop | o_depth   
----------------+----------+----------+-------------+---------  
 {31208,344710} |    31208 |   344710 |             |       1  
 {31208,319951} |    31208 |   319951 |             |       1  
 {31208,340938} |    31208 |   340938 |             |       1  
 {31208,325272} |    31208 |   325272 |             |       1  
 {31208,346519} |    31208 |   346519 |             |       1  
 {31208,314594} |    31208 |   314594 |             |       1  
 {31208,307217} |    31208 |   307217 |             |       1  
 {31208,348009} |    31208 |   348009 |             |       1  
 {31208,300046} |    31208 |   300046 |             |       1  
 {31208,344359} |    31208 |   344359 |             |       1  
(10 rows)  
  
postgres=# close cur1;  
CLOSE CURSOR  
postgres=# end;  
COMMIT  

小结

使用PostgreSQL的CTE语法,可以非常方便的实现图式搜索的需求,包括N度搜索、最短路径搜索,路径、点、边属性(边的属性使用JSON存储,方便架构设计。)展示,层级深度控制和展示,控制每一层的返回数,控制每一层的返回顺序和权重等 等)。

性能方面,PG 10 ON ECS的环境,50亿的点边网络,N度搜索、最短路径搜索,响应时间都在毫秒级(其中3度搜索,每层100条限制,仅2.1毫秒,TPS达到1.2万)。

以上查询,可以封装成PostgreSQL的plpgsql UDF接口,便于业务调用(暴露一些输入条件即可)。

参考

《金融风控、公安刑侦、社会关系、人脉分析等需求分析与数据库实现 - PostgreSQL图数据库场景应用》

https://www.postgresql.org/docs/10/static/queries-with.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
存储 SQL Cloud Native
深入了解云原生数据库CockroachDB的概念与实践
作为一种全球领先的分布式SQL数据库,CockroachDB以其高可用性、强一致性和灵活性等特点备受关注。本文将深入探讨CockroachDB的概念、设计思想以及实践应用,并结合实例演示其在云原生环境下的优越表现。
|
1月前
|
Cloud Native 关系型数据库 大数据
CockroachDB:云原生数据库的新概念与实践
本文将介绍CockroachDB,一种先进的云原生数据库,它具备分布式、强一致性和高可用性等特点。我们将探讨CockroachDB的基本原理、架构设计以及在实际应用中的种种优势和挑战。
|
8月前
|
负载均衡 监控 关系型数据库
百度搜索:蓝易云【PostgreSQL 主从复制方案】
请注意,上述仅为一种主从复制方案的概述,实际实施时可能需要根据特定环境和需求进行调整。建议参考PostgreSQL官方文档和其他可靠资源获取更详细的指南和说明。
93 1
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索,其中并行查询作为其重要组成部分,已经在线稳定运行多年,持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
234 2
|
8月前
|
Ubuntu 关系型数据库 数据库
百度搜索:蓝易云【Ubuntu系统安装 PostgreSQL详细教程。】
现在,你已经成功在Ubuntu系统上安装了PostgreSQL,并创建了一个新的数据库和用户。你可以使用所创建的用户凭据连接到数据库并开始使用。记得根据你的具体需求进行进一步的配置和安全性调整。
263 2
|
1月前
|
监控 关系型数据库 分布式数据库
【PolarDB 开源】PolarDB HTAP 实践:混合事务与分析处理的性能优化策略
【5月更文挑战第21天】PolarDB开源后在HTAP领域表现出色,允许在同一系统处理事务和分析工作负载,提高数据实时性。通过资源分配、数据分区、索引优化等策略提升性能。示例代码展示了创建和查询事务及分析表的基本操作。PolarDB还提供监控工具,帮助企业优化系统并应对业务变化。其HTAP能力为开发者和企业提供了强大支持,推动技术进步,加速数字化时代的业务发展。
395 1
|
30天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
202 1
|
26天前
|
安全 关系型数据库 分布式数据库
【PolarDB 开源】PolarDB 在金融行业中的实践:高可用与安全合规解决方案
【5月更文挑战第28天】PolarDB,一款适用于金融行业的强大数据库,以其高可用性和安全合规性脱颖而出。通过多副本机制和自动故障转移确保业务连续性,结合严格的访问控制和数据加密技术保护信息安全。在实际应用中,如银行核心系统,PolarDB 负责处理海量交易数据,同时支持主从架构以备故障切换。此外,设置强密码策略和加密存储确保合规性,并通过监控预警及时解决问题。随着金融科技发展,PolarDB 将在云原生架构和人工智能等领域发挥更大作用,助力金融行业创新与进步。
102 0
|
28天前
|
负载均衡 关系型数据库 分布式数据库
【PolarDB开源】PolarDB读写分离实践:优化读取性能与负载均衡策略
【5月更文挑战第26天】PolarDB是云原生关系型数据库,通过读写分离优化性能和扩展性。它设置主节点处理写操作,从节点处理读操作,异步复制保证数据一致性。优化读取性能的策略包括增加从节点数量、使用只读实例和智能分配读请求。负载均衡策略涉及基于权重、连接数和地理位置的分配。实践示例中,电商网站通过主从架构、只读实例和负载均衡策略提升商品查询效率。PolarDB的读写分离与负载均衡为企业应对大数据和高并发提供了有效解决方案。
137 0
|
10月前
|
SQL 关系型数据库 MySQL
PolarDB-X 针对跑批场景的思考和实践
金融行业和运营商系统,业务除了在线联机查询外,同时有离线跑批处理,跑批场景比较注重吞吐量,同时基于数据库场景有一定的使用惯性,比如直连MySQL分库分表的存储节点做本地化跑批、以及基于Oracle/DB2等数据库做ETL的数据清洗跑批等。
PolarDB-X 针对跑批场景的思考和实践

相关产品

  • 云原生数据库 PolarDB