Understand possible optimizable foreign key queries

Using Django Using the ORM
1

Following a foreign key relationship incurs a DB lookup, as with this example from documentation:

# Hits the database.
e = Entry.objects.get(id=5)

# Hits the database again to get the related Blog object.
b = e.blog

As outlined in the select_related documentation, you can optimize this query to also fetch the Blog in a single SQL query if you know you’re going to need it.

What I’m interested in understanding is the number of DB lookups my application performs on foreign keys where following a foreign key relationship requires that extra SQL query at time of access. I’ve found printing out len(connection.queries) super helpful for understanding how many queries a particular block of code makes, but I’m also interested in narrowing in on those queries which could benefit from a select_related.

It would be great to know that a particular web request followed foreign key relationships in a way which didn’t hit the cache n times, somehow. Is there any way to hook into that?

2

Hello @benhubsch!

I suggest you have a look at django-seal which allows querysets to be sealed. When querysets are sealed related field attribute accesses on retrieved model instances that would result in a query (e.g. lack of select_related or prefetch_related usage for multi-valued relationships) a Python warning is emitted.

These warnings can be logged or even elevated to errors when running your test suite.

If you’re looking for a more interactive experience when developing you could also try using django-debug-toolbar which includes a panel that displays all queries performed on a per-request basis as long as it’s negotiated as text/html.

900×700 77.4 KB

3

django-seal looks very interesting and like the exact thing I need. Will take a look into it, thanks much @charettes!