This is a somewhat bad example of when to use recursive queries in PostgreSQL, but they're useful a lot of places Activerecord's preload can't hack it and trees

This is a somewhat bad example of when to use recursive queries in PostgreSQL, but they're useful a lot of places Activerecord's preload can't hack it and trees. Examples: * Recursive queries can update, insert, or delete rows. This is a huge win over doing it in Activerecord with large data sets. I've done updates and inserts in under an hour that testing with Activerecord indicated would quite literally take days. It's not really black magic, but you have to actually learn SQL instead of simple selects to use this properly. * Pg's query optimizer with a recursive query & internal data handling is much higher performance than anything Activerecord can portably produce (or produce at all) even if you're lucky enough not to have to retrieve most of your results. Preloading is fine for small data sets, but by the time you hit a few hundred thousand rows you're going to start feeling it. This is one of those places where SQL as set handling and Activerecord as ORM diverge more than just conceptually. * Pulling up rows more than a few step away from the parents (especially through other tables) tends to move a lot of unnecessary data over the network with preloading and can produce ridiculous joins. * "WITH RECURSIVE" can do things Activerecord can't without multiple costly queries round-tripping their results to the client, getting processed there, and fed back into more queries. http://www.postgresql.org/docs/current/static/queries-with.html http://www.postgresql.org/docs/current/static/queries-with.h...