Django query extremely slow

Question

I am having a problem with a Django application. Scope model requests are extremely slow, and after some debugging, I still haven't figured out where the problem is.

When I request db as scope = Scope.objects.get(pk='Esoterik I') in django, it takes 5-10 seconds. The database contains less than 10 records and a primary key index. therefore it is too slow. When executing an equivalent query on db, as SELECT * FROM scope WHERE title='Esoterik I'; everything is in order, it takes only about 50 ms.

The same problem occurs if I execute a query with a set of results, such as scope_list = Scope.objects.filter(members=some_user) , and then execute, for example. print (scope_list) or iterate over list items. The request itself takes only a few ms, but the fingerprint or iteration of the elements is repeated in 5-10 seconds, but the set has only two entries.

Backend Database - Postgresql. The problem occurs the same on the local development server and apache.

here is the model code:

 class Scope(models.Model): title = models.CharField(primary_key=True, max_length=30) ## the semester the scope is linked with assoc_semester = models.ForeignKey(Semester, null=True) ## the grade of the scope. can be Null if the scope is not a class assoc_grade = models.ForeignKey(Grade, null=True) ## the timetable of the scope. can be null if the scope is not direct associated with a class assoc_timetable = models.ForeignKey(Timetable, null=True) ## the associated subject of the scope assoc_subject = models.ForeignKey(Subject) ## the calendar of the scope assoc_calendar = models.ForeignKey(Calendar) ## the usergroup of the scope assoc_usergroup = models.ForeignKey(Group) members = models.ManyToManyField(User) unread_count = None 

The update here is the output of the python profiler. query.py seems to be called 1.6 million times, that's too much.

Answer 1

You should first try to isolate the problem. Run the manage.py shell and run the following:

 scope = Scope.objects.get(pk='Esoterik I') print scope 

Now django requests are not executed until they need it. That is, if you are faced with slowness after the first line, the problem is to create a query that can cause problems with the object manager. The next step is to try to execute raw SQL through django and make sure that the problem is really related to the manager, and not to the error in django in general.

If you are experiencing slowness with the second line, the problem is either in the actual execution of the request, or when displaying / printing data. You can force a request without listing it (check the documentation) to find out which one it represents.

Which, as I understand it, but I think the best way to solve it is to break the process into different parts and find out which part is the one that causes slowness.

Answer 2

To be sure of the database runtime, it is better to check the queries generated by Django, since the queries created by Django may not be just SELECT * from blah blah

To view the generated Django request:

 _somedata = Scope.objects.filter(pk='Esoterik I') # you must use filter in here print somedata.query.__format__('') 

This will show you the complete query created by Django. Then copy it and open the Postgresql console and use the Postgresql analysis tools:

 EXPLAIN ANALYZE <your django query here>; 

as:

 EXPLAIN ANALYZE SELECT * FROMsomeapp_scope WHERE id = 'Esoterik I'; 

EXPLAIN display the average execution data, and ANAYLZE will also show you additional data on the execution time of this analysis.

You can also find out if any postgresql index is used during query execution in the analysis results.