I'm running a rails website on Heroku and am doing some performance profiling and improvements.
I was wondering if anyone with experience profiling Postgres performance on Heroku could tell me if I'm doing something obviously wrong or if this database call can really expected to be this slow.
This is a SELECT on a table called Cities which has about 3000 rows. There is an index on country_id.
Here's an image showing profiling of the slow call
If you can read the image, you'll see that I'm calling SELECT name, slug,... FROM "Cities" WHERE "cities"."country_id" = 151.
This call (in production on Heroku) took 14.739 seconds according to Miniprofiler. This call seems to take anywhere from 2 seconds to 25 seconds with some correlation between the number of cities the country has (between 2 and around 50). In development it takes a fraction of a second.
I'm running Heroku hosted Postgres on the $10 plan.
Right now I'm thinking about ajax-loading the content that this call is used for on my page, but it still makes no sense to me that it shoiuld be taking this long.
Anyone have any ideas?
For reference, the site can be found at www.istorical.com and this call took place when loading www.istorical.com/countries/russia
If you do hit the actual site, you might find it doesn't take that long to load, that's because this call is only made when the cache is expired for the page.
EDIT: Here's an EXPLAIN (analyze,verbose,buffers) of the query.
Run some more explain analyse tests, but if they all come out like that one then it's not the database. That query is only taking 20ms.
If other queries seem OK, then it's not something general with the database connection.
Is anything on that page communicating with a remote server - fetching data/images etc?