I've run into situations like this where I was able to use a window function or a materialized view, to get huge speed gains. It basically was restructuring th

I've run into situations like this where I was able to use a window function or a materialized view, to get huge speed gains. It basically was restructuring the tables into a new view, much like the shell script did in this case, and that view then could be optimized by the query planner much more effectively. One was with an internal database we used for tracking employee time and generating invoices and reports. I forget the exact details but this technique took it down from an hour to a second, or similar. Another time was more recently in an interview, using the mysql employee sample database. I had a naive query that did most of what was needed, but after quite a bit more work I was able to make a materialized view that caused the query to go from using 64+GB of RAM and dying from OOM after 4 hours, to running in 45 minutes in 2GB of RAM. It usually takes me hours and hours to put together though. Because I do it so infrequently.