I would try to reproduce it locally, and see which index combinations are best (pull down prod data if you can).
You can also run the explain from the heroku console, to get feedback about how the postgres query planner is looking up the data. Most likely you need to add an index on lastreport, if that index should really only contain entries for the last 90 days (or whatever your interval is), you can also use a special postgres feature called a partial index. (the index only contains entries that match a constraint, like lastreport > '01-01-2012', and can really speed things up. you update that index every month or whatever to drop off stuff that isn't relevant) The planner will tell you if it is able to combine your existing individual indexes to get all the data it needs. If you always query these columns, it might be better to do a single index, but you'll have to test that and see. (which I think is just a lot easier to do locally) On Thu, May 17, 2012 at 1:50 PM, Josh Coffman <[email protected]> wrote: > In this rails 3.2 app, New Relic is showing location#find is taking a lot > of time from a particular controller method. I have users & locations; > location belongs to user. the user_id column is indexed, and there is a > "timestamp" column which is also indexed. I recently added a column called > "lastreport" which is also a date time field but not indexed. The resulting > query filters on user_id, and timestamp, and where timestamp + interval is > less than or equal to lastreport. Should I create a composite index on all > 3 columns? Is adding an index on just "lastreport" enough? > > This is causing me grief today. Any help is appreciated. > > Thanks, > Josh > > -- > You received this message because you are subscribed to the Google > Groups "Heroku" group. > > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/heroku?hl=en_US?hl=en > -- Thanks, -John -- You received this message because you are subscribed to the Google Groups "Heroku" group. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/heroku?hl=en_US?hl=en
