Query Performance

select_related

When you access a reference field for the first time, AMSDAL loads the related object in a separate query. The resolved object is cached on the instance, so repeated access to the same FK on the same object is free — no extra query. But iterating over a list of N objects still produces N+1 queries: each instance has its own cache and resolves its FK independently.

Note

select_related is for forward FKs (it emits a SQL JOIN). For reverse-FK and many-to-many relations, use prefetch_related — they cannot be JOIN-ed at the parent level without changing row cardinality.

See Working with Models — Forward References for the per-instance FK cache behaviour and the async-mode awaitable pattern.

select_related retrieves related objects in the same query:

SyncAsync

# Without select_related — N+1 queries
for person in Person.objects.all().execute():
    print(person.company.name)  # extra query per person

# With select_related — 1 query
for person in Person.objects.select_related('company').all().execute():
    print(person.company.name)  # already loaded

# Without select_related — N+1 queries
for person in await Person.objects.all().aexecute():
    print(person.company.name)

# With select_related — 1 query
for person in await Person.objects.select_related('company').all().aexecute():
    print(person.company.name)

Multi-Level Relations

To eager-load nested relations, pass each level as a separate argument using __ notation:

SyncAsync

for person in Person.objects.select_related(
    'company',
    'company__location',
).all().execute():
    print(f'{person.name} — {person.company.location.name}')

for person in await Person.objects.select_related(
    'company',
    'company__location',
).all().aexecute():
    print(f'{person.name} — {person.company.location.name}')

Without company__location, accessing person.company.location would trigger an extra query per person.

prefetch_related

For reverse-FK and many-to-many relations, accessing the related collection per parent triggers one query per parent (N+1). prefetch_related issues a single extra query for all parents combined and attaches the rows to each parent's collection cache.

For what each accessor returns and how the populated cache interacts with subsequent reads, see Relationships — RelatedSet.

SyncAsync

# Without prefetch_related — N+1 queries
for author in Author.objects.execute():
    for book in author.book_set:        # extra query per author
        print(book.title)

# With prefetch_related — 2 queries total
for author in Author.objects.prefetch_related('book_set').execute():
    for book in author.book_set:        # already loaded, no extra query
        print(book.title)

for author in await Author.objects.prefetch_related('book_set').aexecute():
    for book in author.book_set:
        print(book.title)

Same syntax for M2M:

Post.objects.prefetch_related('tags').execute()

Nested prefetch

Chain hops with __ notation — each hop is prefetched and cached on the appropriate intermediate target:

# Each author's books, and each book's publisher
Author.objects.prefetch_related('book_set__publisher').execute()

# Multiple parallel prefetches
Author.objects.prefetch_related('book_set', 'co_authored_books').execute()

Prefetch object — filtering and renaming

For finer control, pass a Prefetch object instead of a string:

from amsdal_models.querysets.prefetch import Prefetch

# Filter what gets prefetched
authors = Author.objects.prefetch_related(
    Prefetch('book_set', queryset=Book.objects.filter(year__gte=2020)),
).execute()

# Attach the result under a custom attribute name (leaves book_set untouched)
authors = Author.objects.prefetch_related(
    Prefetch('book_set', queryset=Book.objects.filter(year__gte=2020), to_attr='recent_books'),
).execute()
for author in authors:
    print(author.recent_books)   # the filtered list lives under .recent_books

Parameter	Description
lookup	Required. The relation path (supports __ for nested hops).
queryset	Optional. A queryset for the related model to scope/order the prefetched rows. Inherits its connection from the parent queryset — passing using() raises.
to_attr	Optional. If set, the prefetched list is attached under this attribute on the parent instead of overwriting the relation accessor. Applies only to the leaf hop (Django semantics).

Note

Prefetch.queryset cannot use .using(), .first() / .last() / .get(), slicing (limit/offset), .distinct(), or .annotate() — these either raise ValueError / TypeError or NotImplementedError.

Combining with select_related

select_related and prefetch_related compose — use forward-FK JOINs at the parent level and prefetch for reverse-FK / M2M children:

# JOIN the author's company; prefetch each author's books
Author.objects.select_related('company').prefetch_related('book_set').execute()

only

Load only specific fields to reduce data transfer. Returns partial model instances:

SyncAsync

persons = Person.objects.only(['name', 'email']).execute()

persons = await Person.objects.only(['name', 'email']).aexecute()

distinct

Return unique results based on specific fields:

SyncAsync

countries = Person.objects.distinct(['country']).execute()

countries = await Person.objects.distinct(['country']).aexecute()

Note

distinct() requires a list of field names — distinct() without arguments is not supported.