Django cursor.execute（QUERY）比在postgres数据库中运行查询慢得多 [英] Django cursor.execute(QUERY) much slower than running the query in the postgres database

问题描述

我有一个postgresql查询返回120行 {integer，boolean，integer，varchar（255），varchar（255），bigint，text} 在运行 psql 的数据库中完成时，约 70ms 。

使用python / django django.db.connection.cursor.execute（）在同一台机器上运行 10秒。

我已经尝试将所有的行放入一个数组，而一个字符串（18k个字符，但只返回前500个时间相同），所以只有一行返回，但没有增益。关于为什么在python和db中运行查询有如此急剧的放缓的想法？

编辑

我不得不增加work_mem，使函数在psql中及时运行。其他函数/查询不显示相同的模式，psql和python之间的差异只有几毫秒。

编辑

将work_mem裁减为1MB，显示psql和django shell中的类似数字。可能是django不在work_mem中设置的内存中吗？

编辑

唉。问题是在psql中设置的work_mem在全局无效，如果我在函数中设置内存，则调用是及时的。如果在原位查询和psql查询之间的时间有所不同，那么在配置文件中设置此项将在全局范围内运行。

解决方案

那么第一个也是常见的嫌疑人是这样的：如果框架使用准备的语句，那么你必须使用准备语句来检查psql中的时序。例如：

 准备foo as select * from sometable where intcolumn = $ 1; 
执行foo（42）; 
  

如果执行的时间位于与原位查询相同的ballpark，那么你可以解释和 explain analysis execute line。

如果时间不在同一个球场，你必须寻找别的东西。

I've got a postgresql-query that returns 120 rows {integer, boolean, integer, varchar(255), varchar(255), bigint, text} in about 70ms when done in the database running psql.

Using python/django with django.db.connection.cursor.execute() it takes 10s to run, on the same machine.

I've tried putting all the rows into an array, and a single string (18k characters, but returning only the first 500 takes the same time) so there is only one row returned but with no gain.

Any ideas as to why there is such a dramatic slowdown in running a query from within python and in the db?

EDIT

I had to increase the work_mem to get the function running timely in psql. Other functions/queries don't show the same pattern, the difference between psql and python is only a few milliseconds.

EDIT

Cutting down the work_mem to 1MB shows similar numbers in psql and the django shell. Could it be that django is not going by the memory set in work_mem?

EDIT

Ugh. The problem was that the work_mem set in psql is not valid globally, if I set the memory in the function, the call is timely. I suppose setting this in the configuration file would work globally.

解决方案

If the timing between "in situ" queries and psql queries differs much then the first and usual suspect is this: If the framework uses prepared statements, then you have to check the timing in psql using prepared statements too. For example:

prepare foo as select * from sometable where intcolumn = $1;
execute foo(42);

If the timing of the execute is in the same ballpark as your in situ query, then you can explain and explain analyse the execute line.

If the timing is not in the same ballpark you have to look for something else.

这篇关于Django cursor.execute（QUERY）比在postgres数据库中运行查询慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！