Recursive Postgres Queries

I&#x27;ve implemented similar designs in the past, but found ltree[1] to be much more useful&#x2F;expressive when it comes to needing to fetch, order and manage lists of hierarchical data.<p>[1] <a href="http://www.postgresql.org/docs/9.3/static/ltree.html" rel="nofollow">http:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.3&#x2F;static&#x2F;ltree.html</a>

I&#x27;ve dealt with recursive queries many many times. For legacy production systems I stick to Oracle&#x27;s CONNECT BY statement and half joins to keep performance okay. Most of the tables I deal with typically have 80 million rows with a fanout of 1:2.5 and a average depth of ~3.<p>A better solution is to create a temporary table to insert results in as you go if you can&#x27;t afford extra DB results and perform additional inserts to that table in order to effectively take advantage of shared memory on the server side. The second better result is to effectively flatten out the rows as a closure table.

I don&#x27;t know Ruby, but does Benchmark.ms clear buffers and flush cache? If no, the second query has a large advantage, sometimes you can get similar performance improvements without changing query...

This is absurd. Is all this just to eliminate the n+1 queries problem? There are already solutions for this in any ORM.<p>When loading a survey into an ActiveRecord object, why not just preload the associations? You can then iterate through the children and subchildren of survey in Ruby. Either Survey.includes(categories: { subcategories: questions }).find(1) (one query) or Survey.preload(categories: { subcategories: questions }).find(1) (four queries) would do the trick.<p>No need for convoluted Postgres-only queries.

Super cool, but it seems like this is throwing technology at a problem that could be solved in a much simpler way.<p>Since you will rarely (if ever?) be looking up a single question in a survey, storing the whole set as a JSON array-of-arrays makes much more sense. Then you can look up the set of questions with a dead-simple SELECT.

This is basically a tree like data structure.<p>Did they look at <a href="http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html" rel="nofollow">http:&#x2F;&#x2F;www.ibase.ru&#x2F;devinfo&#x2F;DBMSTrees&#x2F;sqltrees.html</a> and see if it can be adapted to their problem?

Please, please, never do this.<p>Programming like this in SQL is a maintainability nightmare. Can you even imagine how difficult it would be to modify this to add in new features? I&#x27;ve worked on projects where people have done &#x27;clever&#x27; tricks like this, and it always ends in terrible awful terrible. The SQL itself is also non-portable which makes it even worse.<p>Follow the advice of Justin in the comments there and rather denormalize - which basically means caching the results of deep tree queries at the top level where they can be easily and simply understood.<p>EDIT: If you don&#x27;t like the idea of denormalizing, another simple alternative is to create a separate lookup table&#x2F;model which you can use to easily map back the items to the correct parent.