1. Why the post list suddenly makes 200 queries

Your post list worked fine with one model. Then you added the author name, the category and the tag badges to the template, and the page got slow - not because the data grew, but because the number of queries grew with it.

# blog/views.py
def post_list(request):
    posts = Post.objects.published().newest_first()[:50]
    return render(request, "blog/post_list.html", {"posts": posts})

{% for post in posts %}
  <h2>{{ post.title }}</h2>
  <p>by {{ post.author.username }} in {{ post.category.name }}</p>
  <p>{% for tag in post.tags.all %}#{{ tag.name }} {% endfor %}</p>
  <p>{{ post.comments.count }} comments</p>
{% endfor %}

Count the queries: 1 for the posts, then per post one for the author, one for the category, one for the tags and one for the comment count. That is 1 + 50 * 4 = 201. This is the N+1 problem, and it is the single most common performance bug in Django projects.

2. Measure first

Never guess. Count. In the shell, with DEBUG = True:

from django.db import connection, reset_queries

reset_queries()
for post in Post.objects.all()[:50]:
    post.author.username
print(len(connection.queries))     # 51

reset_queries()
for post in Post.objects.select_related("author")[:50]:
    post.author.username
print(len(connection.queries))     # 1

In tests, assert on the number so a regression fails the build:

def test_post_list_query_count(self):
    with self.assertNumQueries(3):
        self.client.get(reverse("blog:post_list"))

For day-to-day work install django-debug-toolbar; its SQL panel shows every query, its duplicates and the line of code that triggered it.

3. select_related: one JOIN, for to-one relations

select_related() works on ForeignKey and OneToOneField - relations where each row has at most one related row, so the data fits into the same result set:

Post.objects.select_related("author", "category")

# follow two levels with the usual double underscore
Comment.objects.select_related("post__author")

SELECT "blog_post"."id", "blog_post"."title",
       "auth_user"."id", "auth_user"."username",
       "blog_category"."id", "blog_category"."name"
FROM "blog_post"
INNER JOIN "auth_user" ON ("blog_post"."author_id" = "auth_user"."id")
LEFT OUTER JOIN "blog_category" ON ("blog_post"."category_id" = "blog_category"."id");

A nullable foreign key becomes a LEFT OUTER JOIN, so posts without a category are still returned. It cannot be used for a reverse foreign key or a many-to-many: those would multiply the rows.

4. prefetch_related: a second query, for to-many relations

prefetch_related() runs one extra query per relation and joins the results in Python, so the number of queries stays constant:

posts = Post.objects.select_related("author", "category").prefetch_related("tags", "comments")

-- query 1: the posts, with author and category joined
-- query 2:
SELECT "blog_tag".*, "blog_post_tags"."post_id"
FROM "blog_tag"
INNER JOIN "blog_post_tags" ON ("blog_tag"."id" = "blog_post_tags"."tag_id")
WHERE "blog_post_tags"."post_id" IN (1, 2, 3, ..., 50);
-- query 3: the comments, WHERE post_id IN (...)

5. Prefetch objects: control the inner query

The fix for the alert above is a Prefetch object, which lets you specify the queryset used for the prefetch and store it under your own attribute name:

from django.db.models import Count, Prefetch

posts = (
    Post.objects.published()
    .select_related("author", "category")
    .prefetch_related(
        "tags",
        Prefetch(
            "comments",
            queryset=Comment.objects.filter(approved=True).order_by("-created_at"),
            to_attr="approved_comments",
        ),
    )
    .annotate(num_comments=Count("comments", filter=Q(comments__approved=True)))
)

{% for post in posts %}
  <h2>{{ post.title }}</h2>
  <p>by {{ post.author.username }} in {{ post.category.name }}</p>
  <p>{% for tag in post.tags.all %}#{{ tag.name }} {% endfor %}</p>
  <p>{{ post.num_comments }} comments</p>
  {% for comment in post.approved_comments %}...{% endfor %}
{% endfor %}

to_attr gives you a plain Python list, which makes it impossible to accidentally re-query. Use a counter annotation instead of .count(): a COUNT in the main query costs nothing extra, whereas {{ post.comments.count }} is one query per post.

Query count for the whole page: 3, whether the list shows 10 posts or 10,000.

6. only, defer and values: fetch fewer columns

A post list does not need the full article body. Stop dragging it over the wire:

# only: name the columns you want
Post.objects.only("title", "slug", "published_date")

# defer: name the ones you do not
Post.objects.defer("text")

# works through select_related
Post.objects.select_related("author").only("title", "slug", "author__username")

# values / values_list: dicts and tuples instead of model instances
Post.objects.values("id", "title")
# <QuerySet [{'id': 1, 'title': 'Hello'}, ...]>

Post.objects.values_list("slug", flat=True)
# <QuerySet ['hello', 'second-post', ...]>

Category.objects.annotate(n=Count("posts")).values_list("name", "n")

Two more tools worth knowing: iterator() streams rows without filling the QuerySet cache, which is how you loop over a million comments in a management command; and count() or exists() instead of len(list(qs)) when you only need a number or a yes/no.

7. Meta.indexes and when an index actually helps

Fewer queries is step one. Making each query fast is step two, and that usually means an index on the columns you filter and sort by:

class Post(models.Model):
    ...

    class Meta:
        ordering = ["-published_date"]
        indexes = [
            models.Index(fields=["-published_date"], name="post_published_idx"),
            models.Index(fields=["category", "-published_date"], name="post_cat_pub_idx"),
            models.Index(
                fields=["published_date"],
                condition=Q(published_date__isnull=False),
                name="post_live_idx",
            ),
        ]

-- python manage.py sqlmigrate blog 0007
CREATE INDEX "post_published_idx" ON "blog_post" ("published_date" DESC);
CREATE INDEX "post_cat_pub_idx" ON "blog_post" ("category_id", "published_date" DESC);

What to remember about indexes:

• Every ForeignKey already has one. Do not add it again.
• unique=True and UniqueConstraint create one too.
• Composite indexes are used left to right: an index on (category, published_date) helps a filter on category alone, but not one on published_date alone.
• Indexes cost write time and disk. On a table with a few thousand rows the database will often ignore them anyway - a sequential scan is cheaper.
• Prove it with EXPLAIN: print(Post.objects.published().explain()).

8. Exercise

1. Add the author, category, tags and comment count to your post list template. Wrap the view in assertNumQueries in a test and record the number.
2. Add select_related and prefetch_related and record the number again. Report both figures.
3. Replace {{ post.comments.count }} with an annotation and confirm the query count drops further.
4. Use a Prefetch with to_attr to show only approved comments, without adding a query per post.
5. Add only("title", "slug", "published_date", "author__username") to the list view and inspect the SQL to confirm text is gone.
6. Add an index on -published_date, run sqlmigrate, then compare .explain() output before and after.

Further reading

• Django docs - Database access optimization
• Django docs - select_related
• Django docs - prefetch_related
• Django docs - Model index reference
• Django docs - assertNumQueries