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

Question

I have a query in my dev environment that typically takes about 1.7 ms to run on the dev mySQL database. When bumped up to Heroku and PostgreSQL, the same query on the same data set is taking about 1.2 seconds!

SELECT distinct user_id, score, quality 
FROM `reports` 
WHERE (datetime_utc >= '2012-01-13 14:00:00' AND 
       datetime_utc <= '2012-01-14 14:00:00') 
ORDER BY score DESC, quality DESC LIMIT 20

I created a compound index on score and quality which helped with the SQL version, but the query running on PostgreSQL is still very, very slow. My first instinct is to check that the index is actually in place on the Heroku side, but i'm not sure quite how to do that - in any case, i have a feeling it's more to do with the fact that mySQL and PostgreSQL don't do things quite the same way.

Any insights or pointers would be hugely appreciated!

You should be able to directly connect to your Heroku pgsql instance with any standard client tools, and look at the state of your indices. — Jake Feasel
– Jake Feasel, Commented Jan 9, 2012 at 23:28
That is only for dedicated PostGres - it's not possible with Shared Postgres. — John Beynon
– John Beynon, Commented Jan 10, 2012 at 7:53

Erwin Brandstetter · Accepted Answer · 2012-01-10 03:19:40Z

2

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.

edited Jan 10, 2012 at 3:19

answered Jan 10, 2012 at 2:34

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doug · Accepted Answer · 2012-01-09 23:22:34Z

1

Could the difference in performance be caused by size of datasets between dev and heroku?

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.

answered Jan 9, 2012 at 23:22

Doug

15.8k18 gold badges82 silver badges105 bronze badges

4 Comments

user1051849 Over a year ago

hi 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!

Doug Over a year ago

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.

Erwin Brandstetter Over a year ago

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.

Matthew Rudy Over a year ago

seems reasonable that the index should be on "datetime_utc"

user425367 · Accepted Answer · 2012-01-10 11:05:09Z

1

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

answered Jan 10, 2012 at 11:05

user425367

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related