All that WITH RECURSIVE does is allow a WITH query to refer to its own output. B...

All that WITH RECURSIVE does is allow a WITH query to refer to its own output. Beyond that there is no overhead.

Here is the query plan I got for my above query:

  QUERY PLAN
  CTE Scan on path  (cost=292.79..304.41 rows=581 width=104)
    CTE path
      ->  Recursive Union  (cost=0.00..292.79 rows=581 width=72)
          ->  Index Scan using fs_pkey on fs  (cost=0.00..8.27 rows=1 width=40)
                Index Cond: (id = 1)
          ->  Hash Join  (cost=0.33..27.29 rows=58 width=72)
                Hash Cond: (public.fs.parent_id = parentpath.id)
                ->  Seq Scan on fs  (cost=0.00..21.60 rows=1160 width=40)
                ->  Hash  (cost=0.20..0.20 rows=10 width=36)
                      ->  WorkTable Scan on path parentpath  (cost=0.00..0.20 rows=10 width=36)