Hacker News Reader

Comment by aidos | Hacker News Reader

I don't know the details but won't that be quite inefficient? (it's not a construct I've seen before - never used Postgres in anger)

You're effectively working with a tree and there are much more relational friendly ways of doing that in SQL.

I know I'm just picking on this specific use but I can't help imagining that recursive querying will always be slow. Would love to hear how it's implemented if that's not the case.

View on HN

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)

But that could end up being quite deep couldn't it? Is it not like stacking up an unknown number of correlated queries? Could you even screw it up and have an infinite joining condition?

If you have self-referencing rows, you're going to wind up with two options: an inefficient recursive query or inefficiently issuing N+1 queries. The recursive query would wind up being faster simply because there's a lot less overhead. That said, I don't know what additional optimizations or penalties are going on in the system, but I have never converted a situation from N+1 queries into a recursive query and found a performance degradation.

Of course other options should always be considered. Joe Celko has a book on storing trees in the database I've been meaning to pick up.

Its a reasonable book from memory, although there are problems with all the methods.

No doubt - the various tree methods all have their drawbacks too (more to manage when manipulating the tree).

It's all going to depend on your usecase but in general these sorts of path operations tend to be more read and less manipulation. You'll almost certainly get much faster lookups if you're not using recursive queries (as you can normally just use an index).

The main advantage of using common table expressions is the improved readability and ease in maintenance of complex queries, after a while using them coming up with a solution for a complex query is quite easy. Regarding the performance of them, it depends on what you are trying to accomplish, some times theres some performance penalties but in my personal experience (using them in SQL Server) I have never run into a case where the performance isn't good, well that's not entirely true, in cases where you need to return large datasets CTE are never the best solution.

Reply on news.ycombinator.com