Database performance in Django/Postgres

Question

In one of my views for the staff, I show the last 10 registrars on my site and some of their activity like so:

recentlyjoined = User.objects.order_by('-date_joined').annotate(post_count=Count('post', distinct=True),
                                                                    text_count=Count('text', distinct=True),
                                                                    pdf_count=Count('pdf', distinct=True),
                                                                    discussion_count=Count('discussion', distinct=True))[0:10]

And in my template I do

{% for applicant in recentlyjoined %}
{{ applicant.post_count }}
{% endfor %}

for all the annotations above.

When I call this site, it takes VERY long to load, sometimes I even get a timeout error. I narrowed it down to this annotations. Whats wrong? Why does this improve so bad? And how can I fix it?

(And in case it matters: In my sandbox database I have only 15 users, but one of them has like 5000 posts. So its not exactly big data to handle.)

Andreas · Accepted Answer · 2021-04-08 19:19:29Z

1

Hello I won't answer your question straight away but since you suspect that the query is not slow you can rule it out.

Enable query logging using the following config in your project's settings.py LOGGING configuration:

        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
            'propagate': False
        }

resource: https://docs.djangoproject.com/en/3.1/topics/logging/#django-db-backends

this will log each query executed and the time it takes

Another option is to enable a slow query log on your database but the above is simpler.
If it appears that some query is slow you can get further details by copying the query and analysing the cost and the operations happening using buildin query plan analysis tools shiped with your database ie explain analyze for postgres.
If it's not db query related you can profile your app with django debug toolbar or django silk
Above all before all the above check that you didn't leave the debaugger on, stopped at some breakpoint.

edited Apr 8, 2021 at 19:19

answered Apr 8, 2021 at 18:54

Andreas

1,18219 silver badges35 bronze badges

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

4 Comments

zeus Over a year ago

logging shows 7 seconds to perform the request... annotate() seems to perform very bad. now I try to solve it with functions on the class... but I run in circular import problems now...

Andreas Over a year ago

to get rid of circular imports you can do the import in your function but I wouldn't recommend doing aggregates in code just optimize your query. In the long run it is always more expensive to do aggregates on your server rather than in your database. Please stick to using a database query(ies) for the job. Do think of memoryallocation / garbage collection transfering data, trips to the database, database connections if not using a connection pool like pgbouncer e.t.c

Andreas Over a year ago

what's the actual query produced?

Andreas Over a year ago

if you run you copy paste your query to the database how much does timed query take ie(in postgres using \timing). Are there any seq scans in explain analyze?

Collectives™ on Stack Overflow

Database performance in Django/Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related