Django cursor.execute(QUERY) much slower than running the query in the postgres database

Question

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.

How are measuring this? Also what are your database settings (eg. do they say "localhost", or include full host name)? Any other details regarding the environment, connection, Django installation? — Tadeck
– Tadeck, Commented Sep 24, 2012 at 15:46
I'm using explain analyze in psql and timeit.Timer in python. Other queries I've run so far don't show this pattern. The server is running locally so there is no connection, ie localhost, environment is osx mountain lion and I'm using django 1.3. — parmenides
– parmenides, Commented Sep 24, 2012 at 15:57
It shouldnt be so slow ... we regularly do fetches that are much bigger than that with no where near that kind of time... — Joran Beasley
– Joran Beasley, Commented Sep 24, 2012 at 16:09

A.H. · Accepted Answer · 2012-09-24 17:51:44Z

1

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.

answered Sep 24, 2012 at 17:51

A.H.

66.8k16 gold badges98 silver badges132 bronze badges

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

1 Comment

parmenides Over a year ago

The timing is still the same even though the query is prepared. I've created a function to run the it but it has the same results whether or not the query is run directly.

Collectives™ on Stack Overflow

Django cursor.execute(QUERY) much slower than running the query in the postgres database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related