Does Django do index optimization for basic queries (with Postgres)?

Question

I want to know if the following query is being optimized by django. I have an address model that is indexed on the field User but not on the field zip_code.

When I do:

Address.objects.get(user=user, zip_code=zip_code)

What I hope happens is that the query first takes advantage of the index to return addresses associated with the user, and then it iterates through the list of addresses for that user and matches the one that has the given zip_code.

However, I'm afraid that this query will not take advantage of the fact that I have the user model indexed on users and will instead iterate through all addresses trying to make sure both the user and zip_code match.

it is not Djanog but underline DB will use index

Grijesh Chauhan
– Grijesh Chauhan

2014-09-11 17:35:15 +00:00
Commented Sep 11, 2014 at 17:35 — Grijesh Chauhan
– Grijesh Chauhan, Commented Sep 11, 2014 at 17:35

Daniel Roseman · Accepted Answer · 2014-09-11 18:39:16Z

1

This question has nothing whatsoever to with Django. Django is not a database, and is not in any way responsible for index optimization. Django simply translates the query to SQL and passes it to the real database. It might be that Postgres does what you describe: to find out, you can use the Django Debug Toolbar to run EXPLAIN on the particular query, and see what indexes are used. However, if this is a query you will do a lot, you probably want to create a combined index on user_id and zip_code anyway.

answered Sep 11, 2014 at 18:39

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

2 Comments

WillHaack Over a year ago

How does this question have nothing to do with Django? I'm essentially asking if Django translates this correctly. I don't see the point in creating a combined index since users should only have 1-10 addresses anyways.

David S Over a year ago

Daniel is correct. Django just builds a SQL statement based on the fields in your model and the filter you specify. Postgres (and databases in general), have complex things called "query optimizers" that take that query string and attempt to figure out the fastest possible way to retrieve the results. And here's the kicker... Postgresql (and any other database) might decide that a sequential table scan is best and not use your finely crafted and clever index. And the Query Optimizer is almost always correct.

Collectives™ on Stack Overflow

Does Django do index optimization for basic queries (with Postgres)?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related