Database query times out on heroku <p>I'm stress testing an app by adding loads and loads of items and forcing it to do lots of work. </p> <pre><code>select *, ( select price from prices WHERE widget_id = widget.id ORDER BY id DESC LIMIT 1 ) as maxprice FROM widgets ORDER BY created_at DESC LIMIT 20 OFFSET 0 </code></pre> <ul> <li>that query selects from widgets (approx 8500) and prices has 777000 or so entries in it.</li> </ul> <p>The query is timing out on the test environment which is using the basic Heroku shared database. (193mb in use of the 5gig max.)</p> <p>What will solve that time out issue? The prices update each hour, so every hour you get 8500x new rows. </p> <p>It's hugely excessive amounts for the app (in reality it's unlikely it would ever have 8500 widgets) but I'm wondering what's appropriate to solve this?</p> <p>Is my query stupid? (i.e. is it a bad style of query to do that subselect - my SQL knowledge is terrible, one of the goals of this project is to improve it!) </p> <p>Or am I just hitting a limit of a shared db and should expect to move onto a dedicated db (e.g. the min $200 per month dedicated postgres instance from Heroku.) given the size of the prices table? Is there a deeper issue in terms of how I've designed the DB? (i.e. it's a one to many, one widget has many prices.) Is there a more sensible approach?</p> <p>I'm totally new to the world of sql and queries etc. at scale, hence the utter ignorance expressed above. :)</p>