Rails 3.1 - huge query time difference between mySQL and PostgreSQL on Heroku?

Try this modified query: SELECT user_id, score, quality FROM reports WHERE datetime_utc BETWEEN '2012-01-13 14:00:00' AND '2012-01-14 14:00:00' GROUP BY user_id, score, quality ORDER BY score DESC, quality DESC LIMIT 20 Since DISTINCT is applied last, it may be slower than GROUP BY with many non-distinct rows. You'd have to test - with EXPLAIN ANALYZE. Otherwise, the result is the same.

Minor simplification to the WHERE clause with BETWEEN. Removed non-standard MySQL syntax. An Index on (score, quality) will hardly get used.

The useful index here is (should make a big difference in most scenarios): CREATE INDEX reports_date_time_utc_idx ON reports (date_time_utc) The important part is the index.

Having an index on score,quality will not help much if there are many rows, since it still must filter based on datetime_utc. You may wish to consider an index on datetime_utc, since it needs to filter there first. If you really want to optimize for read speed, you could have a compound index on datetime_utc, score, quality, user_id which would completely eliminate the need to lookup the row data.

However, beware of doing that, since you may then cause a hotspot on inserts with such a wide index.

Doug - the datasets in the two instances are identical, as are the results. It's just that one runs about 1000 time faster than the other! – user1051849 yesterday Perhaps try running a query analyzer such as github.com/trevorturk/pg_query_analyzer and dump the query analysis here.

That would help us understand what is causing the slowdown. – Doug yesterday Eliminate the need to look up data completely? Not true for PostgreSQL - yet.

Covering indexes or index-only scans in PostgreSQL parlance are expected for version 9.2. I wrote more (plus links) in a recent answer on dba.SE. – Erwin Brandstetter yesterday seems reasonable that the index should be on "datetime_utc" – Matthew Rudy yesterday.

As Heroku doesn't allow you to connect to the database unless having one of the >200$/month plan you could try to retrieve a local copy of the database for local inspection. Heroku db:pull // Will give you a local copy of the db The result will be something like this: Receiving schema Receiving data 8 tables, 591 records users: 100% |================================| Time: 00:00:00 pages: 100% |================================| Time: 00:00:00 comments: 100% |================================| Time: 00:00:00 tags: 100% |================================| Time: 00:00:00 Receiving indexes Resetting sequences.

That's a great bit of thinking - thanks – user1051849 yesterday.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.