TIL
closure_tree: filter early
In a side project I use the closure_tree gem for hierarchical data in Rails. Methods like #leaves and #hash_tree are handy, but once I added a scope on top they got slow.
What I was doing: build the tree, then filter the result in Ruby. So Postgres did all the hierarchy work for the whole tree, and most of it got thrown away.
The faster way is to push the WHERE into the query so Postgres filters before doing the recursive/join work, not after. Far less data moves through the expensive part.
Beyond this gem, the takeaway is to filter as early in the query as you can, so fewer rows have to go through the expensive part.