#36764: QuerySet.only() causes n+1 queries with reverse foreign key relation
-------------------------------------+-------------------------------------
Reporter: bernhard | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset, only, | Triage Stage:
defered | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by bernhard:
Old description:
> To reproduce I tried adding the following test to
> `tests.defer.tests.DeferTests`:
>
> {{{
> def test_only_reverse_fk(self):
> qs = self.s1.primary_set.only("name")
> with self.assertNumQueries(1):
> for primary in qs:
> primary.name
> }}}
>
> This fails because it produces an additional query for every `Primary`
> instance to just fetch `related_id`:
>
> {{{
> test_only_reverse_fk (defer.tests.DeferTests.test_only_reverse_fk)
> failed:
>
> AssertionError('3 != 1 : 3 queries executed, 1 expected
> Captured queries were:
> 1. SELECT "defer_primary"."id", "defer_primary"."name" FROM
> "defer_primary" WHERE "defer_primary"."related_id" = 1
> 2. SELECT "defer_primary"."id", "defer_primary"."related_id" FROM
> "defer_primary" WHERE "defer_primary"."id" = 1 LIMIT 21
> 3. SELECT "defer_primary"."id", "defer_primary"."related_id" FROM
> "defer_primary" WHERE "defer_primary"."id" = 2 LIMIT 21
> ')
> }}}
>
> When replacing the queryset with `qs =
> Primary.objects.filter(related_id=self.s1.pk).only("name")` basically the
> same SQL is produced, but no additional queries.
> I think the additional queries are quite an unexpected behaviour and it
> is unnecessary to retrieve `related_id` for every row. Somehow this is
> also a dangerous behaviour as it can cause n + 1 queries when actually
> trying to optimize your query.
>
> If this behaviour should be the expected one it should at least be
> documented and tested somewhere (I don't think there are tests for
> `only()` when using relations like this somewhere).
New description:
To reproduce I tried adding the following test to
`tests.defer.tests.DeferTests`:
{{{#!python
def test_only_reverse_fk(self):
qs = self.s1.primary_set.only("name")
with self.assertNumQueries(1):
for primary in qs:
primary.name
}}}
This fails because it produces an additional query for every `Primary`
instance to just fetch `related_id`:
{{{
test_only_reverse_fk (defer.tests.DeferTests.test_only_reverse_fk) failed:
AssertionError('3 != 1 : 3 queries executed, 1 expected
Captured queries were:
1. SELECT "defer_primary"."id", "defer_primary"."name" FROM
"defer_primary" WHERE "defer_primary"."related_id" = 1
2. SELECT "defer_primary"."id", "defer_primary"."related_id" FROM
"defer_primary" WHERE "defer_primary"."id" = 1 LIMIT 21
3. SELECT "defer_primary"."id", "defer_primary"."related_id" FROM
"defer_primary" WHERE "defer_primary"."id" = 2 LIMIT 21
')
}}}
When replacing the queryset with `qs =
Primary.objects.filter(related_id=self.s1.pk).only("name")` basically the
same SQL is produced, but no additional queries.
I think the additional queries are quite an unexpected behaviour and it is
unnecessary to retrieve `related_id` for every row. Somehow this is also a
dangerous behaviour as it can cause n + 1 queries when actually trying to
optimize your query.
If this behaviour should be the expected one it should at least be
documented and tested somewhere (I don't think there are tests for
`only()` when using relations like this somewhere).
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36764#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019adf12efeb-4d8480d1-8db9-4eea-8144-4cd4c26dd1e6-000000%40eu-central-1.amazonses.com.
