Query to postgreSQL from RoR slower than from prompt?

Question

I am querying a postgreSQL DB from my ruby on rails application this way:

var = Map.connection.execute("
SELECT * 
FROM shortest_path('SELECT * FROM japan WHERE japan.geom_way && ST_MakeEnvelope(139.68012, 35.63993, 139.71918, 35.66024)', 242945, 582735, false, false) 
JOIN japan ON edge_id = id;")

The execution time shown in the rails server console is 327.8 ms.

I execute an identical query from the psql promtp:

SELECT * 
FROM shortest_path('SELECT * FROM japan WHERE japan.geom_way && ST_MakeEnvelope(139.68012, 35.63993, 139.71918, 35.66024)', 242945, 582735, false, false) 
JOIN japan ON edge_id = id;

The execution time is 53.108 ms.

I thought that some caching could be the reason of the different execution times, but if I try to execute 2 times in a row the same query in the rails application, the execution time for 1 query doesn't change. For instance:

var = Map.connection.execute("SELECT * FROM shortest_path('SELECT * FROM japan WHERE japan.geom_way && ST_MakeEnvelope(139.68012, 35.63993, 139.71918, 35.66024)', 242945, 582735, false, false) JOIN japan ON edge_id = id;") 
var = Map.connection.execute("SELECT * FROM shortest_path('SELECT * FROM japan WHERE japan.geom_way && ST_MakeEnvelope(139.68012, 35.63993, 139.71918, 35.66024)', 242945, 582735, false, false) JOIN japan ON edge_id = id;")

gives an execution time of 330.7 ms and 327.8 ms.

Since the 2 queries are identical, shouldn't I expect the same execution time in RoR and in the prompt?

Thanks in advance for any idea.

How are you timing it? If you're doing it from an rspec test or some such where you can do a one-shot execution, it has to load the entire Rails before executing. — wadesworld, Jun 18 '12 at 18:43
For RoR, I am just reading the execution times from the terminal in which I launched the rails server. For the psql prompt, I turned "timing" on with \timing. — user1463862, Jun 19 '12 at 09:35
For those experiencing the same issue, I was using one of Amazon's EC2 servers. After a lot of research on Internet, it seemed that EC2 servers are very bad when dealing with postgresql (and other platforms?) databases. The query response kept changing randomly, and was, in the average, veeeeery slow. We changed the server to another company, and now everything works smoothly, predictably and faster. Forget Amazon's EC2 if you plan to heavily use a DB! — user1463862, Jul 13 '12 at 02:03

score 1 · Answer 1 · answered Jun 18 '12 at 15:36

1

Look at http://www.depesz.com/2008/05/10/prepared-statements-gotcha/ - maybe reason is similar?

answered Jun 18 '12 at 15:36

underley

61
2
4

I tried using prepared statements, as described in http://www.daniel-azuma.com/blog/archives/216, but the execution time did not change in rails. Thank you for the link, though. – user1463862 Jun 19 '12 at 09:49

score 0 · Answer 2 · answered Jun 18 '12 at 18:33

0

One is using Ruby and one isn't.

Also you haven't indicated if there are any network transport effects, and if there are network differences, how many rows are being returned across the wire.

You haven't said how you are timing these things. If the time includes client-side processing in the Ruby case, then in the second case, there is obviously less processing and also the prompt doesn't include transfer time or time to process the results into the display as part of the "execution time" that it is reporting to you.

answered Jun 18 '12 at 18:33

Cade Roux

88,164
40
182
265

I am working in the localhost, so no network transport. For the execution time in RoR, I am reading the execution times from the terminal in which I launched the rails server. I don't know how does RoR handle this execution times (if it includes the displaying time and so). – user1463862 Jun 19 '12 at 09:44

Query to postgreSQL from RoR slower than from prompt?

2 Answers2