从RoR查询postgreSQL比从提示中查询慢吗？

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

我通过这种方式从我的ruby on rails应用程序查询postgreSQL数据库：

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.

rails服务器控制台中显示的执行时间为327.8 ms。

I execute an identical query from the psql promtp:

我从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.

执行时间为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:

我认为一些缓存可能是执行时间不同的原因，但如果我尝试在rails应用程序中连续执行2次相同的查询，则1个查询的执行时间不会改变。例如：

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.

给出330.7 ms和327.8 ms的执行时间。

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

由于2个查询是相同的，我不应该期望在RoR和提示中执行相同的时间吗？

Thanks in advance for any idea.

提前感谢任何想法。

2 个解决方案

#1

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

请看http://www.depesz.com/2008/05/10/prepared-statements-gotcha/ - 也许理由类似？

#2

One is using Ruby and one isn't.

一个是使用Ruby，一个不是。

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.

你还没有说过如何计算这些东西。如果时间包括Ruby案例中的客户端处理，那么在第二种情况下，显然处理较少，并且提示不包括将结果作为“执行时间”的一部分处理到显示中的传输时间或时间“它报告给你。

#1