Simple Postgres queries running slow on Heroku

Question

I have a Movie model with around 15,000 items and a Dvd model with 3500.

The following queries are simple Rails associations using a Crane Postgres db running on Heroku. I'm wondering why the following queries are taking so long, and how could I eventually reduce the time of it.

2012-10-24T05:42:19+00:00 app[postgres]: [30-1] [BLACK] LOG:  duration: 57.914 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 37 ORDER BY scene LIMIT 1
2012-10-24T05:42:20+00:00 app[postgres]: [31-1] [BLACK] LOG:  duration: 77.086 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 915 ORDER BY scene LIMIT 1
2012-10-24T05:42:20+00:00 app[postgres]: [32-1] [BLACK] LOG:  duration: 85.602 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 108 ORDER BY scene LIMIT 1
2012-10-24T05:42:21+00:00 app[postgres]: [33-1] [BLACK] LOG:  duration: 70.147 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 11 ORDER BY scene LIMIT 1
2012-10-24T05:42:21+00:00 app[postgres]: [34-1] [BLACK] LOG:  duration: 144.204 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 6 ORDER BY scene LIMIT 1
2012-10-24T05:42:22+00:00 app[postgres]: [35-1] [BLACK] LOG:  duration: 56.623 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 1956 ORDER BY scene LIMIT 1
2012-10-24T05:42:23+00:00 app[postgres]: [36-1] [BLACK] LOG:  duration: 64.860 ms  statement: SELECT  "movies".* FROM "movies"  WHERE "movies"."dvd_id" = 747 ORDER BY scene LIMIT 1

Please show EXPLAIN (BUFFERS, ANALYZE) and other info as requested here: stackoverflow.com/tags/postgresql-performance/info — Craig Ringer
– Craig Ringer, Commented Oct 24, 2012 at 6:53

dpassage · Accepted Answer · 2012-10-24 05:58:05Z

4

You can connect directly to your database CLI and then use the PostgresQL EXPLAIN command to get information on how it's running your query. This can show places where you can add indices to your tables to speed things up. The Postgres docs explain (see what I did there?) EXPLAIN in more detail.

answered Oct 24, 2012 at 5:58

dpassage

5,4533 gold badges28 silver badges53 bronze badges

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

Comments

Bryan Ash · Accepted Answer · 2016-01-20 16:46:44Z

4

What is your code that is responsible for these queries? Are you using eager loading? Your query should be something like:

DVD.includes(:movies).where(whatever) #assuming DVD has many movies

This should reduce the number of queries/requests. to your database. Your queries suggest that you may have a N+1 problem.

You should also have a database index on the movies table for dvd_id as it is a foreign key.

edited Jan 20, 2016 at 16:46

Bryan Ash

4,4993 gold badges46 silver badges58 bronze badges

answered Oct 24, 2012 at 6:24

John

4,3825 gold badges34 silver badges50 bronze badges

Comments

mvp · Accepted Answer · 2012-10-24 06:30:54Z

0

Make sure you at least have index on dvd_id. If you can create compound index on (dvd_id, scene), your query will be fully optimal and cannot be optimized any further.

In other words, just execute

CREATE INDEX movies_dvd_id_scene_idx ON movies (dvd_id, scene);

and you should be all set

answered Oct 24, 2012 at 6:30

mvp

118k15 gold badges132 silver badges155 bronze badges

2 Comments

dbenhur Over a year ago

You say "fully optimal" as if such a thing could be determined in the abstract instead of by rigorous testing with actual data, production query corpse, and the constraints of the hardware the system runs on. For example: adding a second field to the index makes the key size bigger, reducing key density in the index blocks. This can make it harder to fully cache the index or cause the index tree depth to increase sooner requiring extra seeks to satisfy the queries. If the average cardinality of scenes/dvd_id is close to one, the extra field will almost never be a benefit.

mvp Over a year ago

I agree in general. But, in this case, considering amount of data (just 15k rows), using this recipe cannot possibly hurt, and will not need any revising and re-asking for more information from OP. In this sense, it is fully optimal.

Stéphane Bruckert · Accepted Answer · 2017-12-05 20:26:26Z

0

duration: 57.914 ms
duration: 77.086 ms
duration: 85.602 ms
duration: 70.147 ms
duration: 144.204 ms
duration: 56.623 ms
duration: 64.860 ms

On average (~80ms) this is the usual duration needed for queries operating over different continents. Make sure both your Heroku app and DB are hosted in the same region (EU or US).

answered Dec 5, 2017 at 20:26

Stéphane Bruckert

23.1k15 gold badges102 silver badges136 bronze badges

Collectives™ on Stack Overflow

Simple Postgres queries running slow on Heroku

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related