Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL Recursive Query – Examples of Depth-First and Breadth-First Search

PostgreSQL Recursive Query – Examples of Depth-First and Breadth-First Search

This article shows examples of depth-first and breadth-first searches.

By digoal

Background

Recursive queries are generally used to deal with social relationships, doctor-patient relationships, student-teacher relationships, families, upstream and downstream sensors, etc.

https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-SEARCH

PostgreSQL recursive query supports depth-first and breadth-first search, using the BREADTH DEPTH keyword syntax.

Note:

  • The depth and breadth-first search can search all the results and sort them to get depth and breadth-first results, rather than being implemented in the search method so the performance will be lost.
  • If the results are not sorted, it is breadth-first (level-by-level search).

Imagine there is a tree root:

  • If the root is hollow, pour the colored liquid into it, and the liquid flows into the root. The process is similar to the breadth-first search, penetrating downward level by level.
  • It is impossible for the colored liquid to flow to the bottom first and then into other branches at the top.
  • If you want to achieve the depth-first search, you may have to add a state mark to the fork during the search process (and store the mark) and then go back to continue the search after reaching the end.

Example

Friend Relationship Chain: When uid 1 makes friends with uid 2, lkuid is a friend of uid. Write 1,2:

create table bs (uid int, lkuid int, primary key(uid,lkuid));  
  
insert into bs select * from   
  (select (random()*10)::int c1, (random()*10)::int c2 from generate_series(1,100)) t   
where c1<>c2 on conflict do nothing;

Breadth-First Search:

with recursive tmp as (  
select uid, lkuid, 0 as depth, array[uid,lkuid] as path, false as cycle from bs where uid=1   
union all  
select bs.uid, bs.lkuid, tmp.depth+1, tmp.path||bs.lkuid, bs.lkuid=any(tmp.path)   
  from bs , tmp    
  where bs.uid=tmp.lkuid   
  and not bs.lkuid=any(tmp.path)   
)   
SEARCH BREADTH FIRST BY uid SET ordercol   
select * from tmp order by ordercol;   
  
  
 uid | lkuid | depth |           path           | cycle | ordercol   
-----+-------+-------+--------------------------+-------+----------  
   1 |     8 |     0 | {1,8}                    | f     | (0,1)  
   1 |     3 |     0 | {1,3}                    | f     | (0,1)  
   1 |     7 |     0 | {1,7}                    | f     | (0,1)  
   1 |    10 |     0 | {1,10}                   | f     | (0,1)  
   1 |     5 |     0 | {1,5}                    | f     | (0,1)  
   1 |     2 |     0 | {1,2}                    | f     | (0,1)  
   1 |     4 |     0 | {1,4}                    | f     | (0,1)  
   2 |     6 |     1 | {1,2,6}                  | f     | (1,2)  
   2 |     4 |     1 | {1,2,4}                  | f     | (1,2)  
   2 |     3 |     1 | {1,2,3}                  | f     | (1,2)  
   3 |     0 |     1 | {1,3,0}                  | f     | (1,3)  
   3 |     8 |     1 | {1,3,8}                  | f     | (1,3)  
   3 |     9 |     1 | {1,3,9}                  | f     | (1,3)  
   3 |     2 |     1 | {1,3,2}                  | f     | (1,3)  
   3 |     6 |     1 | {1,3,6}                  | f     | (1,3)  
   4 |     6 |     1 | {1,4,6}                  | f     | (1,4)  
   4 |    10 |     1 | {1,4,10}                 | f     | (1,4)  
   4 |     9 |     1 | {1,4,9}                  | f     | (1,4)  
   4 |     0 |     1 | {1,4,0}                  | f     | (1,4)  
   4 |     3 |     1 | {1,4,3}                  | f     | (1,4)  
   5 |     7 |     1 | {1,5,7}                  | f     | (1,5)  
   5 |     6 |     1 | {1,5,6}                  | f     | (1,5)  
   5 |     2 |     1 | {1,5,2}                  | f     | (1,5)  
   5 |     4 |     1 | {1,5,4}                  | f     | (1,5)  
   7 |     2 |     1 | {1,7,2}                  | f     | (1,7)  
   7 |     6 |     1 | {1,7,6}                  | f     | (1,7)  
   7 |     0 |     1 | {1,7,0}                  | f     | (1,7)  
   7 |     5 |     1 | {1,7,5}                  | f     | (1,7)  
   7 |    10 |     1 | {1,7,10}                 | f     | (1,7)  
   7 |     8 |     1 | {1,7,8}                  | f     | (1,7)  
   7 |     3 |     1 | {1,7,3}                  | f     | (1,7)  
   8 |     7 |     1 | {1,8,7}                  | f     | (1,8)  
   8 |     2 |     1 | {1,8,2}                  | f     | (1,8)  
   8 |     4 |     1 | {1,8,4}                  | f     | (1,8)  
   8 |     5 |     1 | {1,8,5}                  | f     | (1,8)  
   8 |     9 |     1 | {1,8,9}                  | f     | (1,8)  
   8 |     3 |     1 | {1,8,3}                  | f     | (1,8)  
  10 |     5 |     1 | {1,10,5}                 | f     | (1,10)  
  10 |     8 |     1 | {1,10,8}                 | f     | (1,10)  
  10 |     9 |     1 | {1,10,9}                 | f     | (1,10)  
   0 |     5 |     2 | {1,7,0,5}                | f     | (2,0)

Depth-First Search:

with recursive tmp as (  
select uid, lkuid, 0 as depth, array[uid,lkuid] as path, false as cycle from bs where uid=1   
union all  
select bs.uid, bs.lkuid, tmp.depth+1, tmp.path||bs.lkuid, bs.lkuid=any(tmp.path)   
  from bs , tmp    
  where bs.uid=tmp.lkuid   
  and not bs.lkuid=any(tmp.path)   
)   
SEARCH DEPTH FIRST BY uid SET ordercol   
select * from tmp order by ordercol;   
  
  
 uid | lkuid | depth |           path           | cycle |                  ordercol                    
-----+-------+-------+--------------------------+-------+--------------------------------------------  
   1 |     8 |     0 | {1,8}                    | f     | {(1)}  
   1 |     7 |     0 | {1,7}                    | f     | {(1)}  
   1 |     4 |     0 | {1,4}                    | f     | {(1)}  
   1 |     2 |     0 | {1,2}                    | f     | {(1)}  
   1 |     5 |     0 | {1,5}                    | f     | {(1)}  
   1 |    10 |     0 | {1,10}                   | f     | {(1)}  
   1 |     3 |     0 | {1,3}                    | f     | {(1)}  
   2 |     4 |     1 | {1,2,4}                  | f     | {(1),(2)}  
   2 |     3 |     1 | {1,2,3}                  | f     | {(1),(2)}  
   2 |     6 |     1 | {1,2,6}                  | f     | {(1),(2)}  
   3 |     8 |     2 | {1,2,3,8}                | f     | {(1),(2),(3)}  
   3 |     0 |     2 | {1,2,3,0}                | f     | {(1),(2),(3)}  
   3 |     9 |     2 | {1,2,3,9}                | f     | {(1),(2),(3)}  
   3 |     6 |     2 | {1,2,3,6}                | f     | {(1),(2),(3)}  
   0 |     8 |     3 | {1,2,3,0,8}              | f     | {(1),(2),(3),(0)}  
   0 |     5 |     3 | {1,2,3,0,5}              | f     | {(1),(2),(3),(0)}  
   0 |     9 |     3 | {1,2,3,0,9}              | f     | {(1),(2),(3),(0)}  
   5 |     7 |     4 | {1,2,3,0,5,7}            | f     | {(1),(2),(3),(0),(5)}  
   5 |     4 |     4 | {1,2,3,0,5,4}            | f     | {(1),(2),(3),(0),(5)}  
   5 |     6 |     4 | {1,2,3,0,5,6}            | f     | {(1),(2),(3),(0),(5)}  
   4 |     9 |     5 | {1,2,3,0,5,4,9}          | f     | {(1),(2),(3),(0),(5),(4)}  
   4 |     6 |     5 | {1,2,3,0,5,4,6}          | f     | {(1),(2),(3),(0),(5),(4)}  
   4 |    10 |     5 | {1,2,3,0,5,4,10}         | f     | {(1),(2),(3),(0),(5),(4)}  
   6 |     8 |     6 | {1,2,3,0,5,4,6,8}        | f     | {(1),(2),(3),(0),(5),(4),(6)}  
   6 |    10 |     6 | {1,2,3,0,5,4,6,10}       | f     | {(1),(2),(3),(0),(5),(4),(6)}  
   8 |     7 |     7 | {1,2,3,0,5,4,6,8,7}      | f     | {(1),(2),(3),(0),(5),(4),(6),(8)}  
   8 |     9 |     7 | {1,2,3,0,5,4,6,8,9}      | f     | {(1),(2),(3),(0),(5),(4),(6),(8)}  
   7 |    10 |     8 | {1,2,3,0,5,4,6,8,7,10}   | f     | {(1),(2),(3),(0),(5),(4),(6),(8),(7)}  
  10 |     9 |     9 | {1,2,3,0,5,4,6,8,7,10,9} | f     | {(1),(2),(3),(0),(5),(4),(6),(8),(7),(10)}  
.......  
   3 |     8 |     1 | {1,3,8}                  | f     | {(1),(3)}  
   3 |     0 |     1 | {1,3,0}                  | f     | {(1),(3)}  
   3 |     9 |     1 | {1,3,9}                  | f     | {(1),(3)}  
   3 |     2 |     1 | {1,3,2}                  | f     | {(1),(3)}  
   3 |     6 |     1 | {1,3,6}                  | f     | {(1),(3)}  
   0 |     8 |     2 | {1,3,0,8}                | f     | {(1),(3),(0)}  
   0 |     9 |     2 | {1,3,0,9}                | f     | {(1),(3),(0)}  
   0 |     5 |     2 | {1,3,0,5}                | f     | {(1),(3),(0)}  
   5 |     7 |     3 | {1,3,0,5,7}              | f     | {(1),(3),(0),(5)}  
   5 |     4 |     3 | {1,3,0,5,4}              | f     | {(1),(3),(0),(5)}  
   5 |     2 |     3 | {1,3,0,5,2}              | f     | {(1),(3),(0),(5)}  
   5 |     6 |     3 | {1,3,0,5,6}              | f     | {(1),(3),(0),(5)}  
   2 |     4 |     4 | {1,3,0,5,2,4}            | f     | {(1),(3),(0),(5),(2)}  
   2 |     6 |     4 | {1,3,0,5,2,6}            | f     | {(1),(3),(0),(5),(2)}  
   4 |     9 |     5 | {1,3,0,5,2,4,9}          | f     | {(1),(3),(0),(5),(2),(4)}  
   4 |    10 |     5 | {1,3,0,5,2,4,10}         | f     | {(1),(3),(0),(5),(2),(4)}  
   4 |     6 |     5 | {1,3,0,5,2,4,6}          | f     | {(1),(3),(0),(5),(2),(4)}

Breadth-first and depth-first are implemented by sorting, which is a trick for sorting the search results; one is the record type, and the other is the record[] type.

Breadth-First Search:

record
with recursive tmp as (  
select uid, lkuid, 0 as depth, array[uid,lkuid] as path, false as cycle from bs where uid=1   
union all  
select bs.uid, bs.lkuid, tmp.depth+1, tmp.path||bs.lkuid, bs.lkuid=any(tmp.path)   
  from bs , tmp    
  where bs.uid=tmp.lkuid   
  and not bs.lkuid=any(tmp.path)   
)   
SEARCH BREADTH FIRST BY uid SET ordercol   
select pg_typeof(ordercol), * from tmp limit 1;   
  
 pg_typeof | uid | lkuid | depth | path  | cycle | ordercol   
-----------+-----+-------+-------+-------+-------+----------  
 record    |   1 |     7 |     0 | {1,7} | f     | (0,1)  
(1 row)

Depth-First Search record[]:

with recursive tmp as (  
select uid, lkuid, 0 as depth, array[uid,lkuid] as path, false as cycle from bs where uid=1   
union all  
select bs.uid, bs.lkuid, tmp.depth+1, tmp.path||bs.lkuid, bs.lkuid=any(tmp.path)   
  from bs , tmp    
  where bs.uid=tmp.lkuid   
  and not bs.lkuid=any(tmp.path)   
)   
SEARCH DEPTH FIRST BY uid SET ordercol   
select pg_typeof(ordercol), * from tmp limit 1;   
  
 pg_typeof | uid | lkuid | depth | path  | cycle | ordercol   
-----------+-----+-------+-------+-------+-------+----------  
 record[]  |   1 |     7 |     0 | {1,7} | f     | {(1)}  
(1 row)

Implementation Method of Depth-First Search, Sort:

explain with recursive tmp as (  
select uid, lkuid, 0 as depth, array[uid,lkuid] as path, false as cycle from bs where uid=1   
union all  
select bs.uid, bs.lkuid, tmp.depth+1, tmp.path||bs.lkuid, bs.lkuid=any(tmp.path)   
  from bs , tmp    
  where bs.uid=tmp.lkuid   
  and not bs.lkuid=any(tmp.path)   
)   
SEARCH DEPTH FIRST BY uid SET ordercol   
select * from tmp order by ordercol;   

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Sort  (cost=111.18..112.52 rows=537 width=77)
   Sort Key: tmp.ordercol
   CTE tmp
     ->  Recursive Union  (cost=0.00..76.09 rows=537 width=77)
           ->  Seq Scan on bs  (cost=0.00..1.70 rows=7 width=77)
                 Filter: (uid = 1)
           ->  Hash Join  (cost=2.28..6.37 rows=53 width=77)
                 Hash Cond: (bs_1.uid = tmp_1.lkuid)
                 Join Filter: (bs_1.lkuid <> ALL (tmp_1.path))
                 ->  Seq Scan on bs bs_1  (cost=0.00..1.56 rows=56 width=8)
                 ->  Hash  (cost=1.40..1.40 rows=70 width=72)
                       ->  WorkTable Scan on tmp tmp_1  (cost=0.00..1.40 rows=70 width=72)
   ->  CTE Scan on tmp  (cost=0.00..10.74 rows=537 width=77)
(13 rows)
PostgreSQL Database Recursive Query Query
0 0 0
Share on

Read previous post:

The Scale up and Scale down of PostgreSQL Hash Partition Table

Read next post:

Best Practices for PostgreSQL Time Series Database Design

digoal

285 posts | 26 followers

You may also like

Comments

digoal

285 posts | 26 followers

Related Products

More Posts by digoal

See All