Hi Erik,
I think the prefetch api uses IN [id1, ..., idn] instead of IN (SELECT *
...)
because the default isolation level Django runs with is READ COMITTED
and using the latter could return different results for the second query.
e.g.
SELECT id FROM foo WHERE bar = true;
-- An other transaction changes a matching row from bar = true to false.
SELECT id, foo_id FROM baz WHERE foo_id IN (
SELECT id FROM foo WHERE bar = true
);
If you want to use this approach I'm afraid you'll have to do
what prefetch_related does under the hood by yourself.
e.g.
from collections import defaultdict
prefetched = defaultdict(list)
subjects = Subject.objects.filter(...)
lessons = Lesson.objects.filter(...)
for lesson_subject in
Lesson.subjects.through.objects.filter(lesson__in=lessons,
subject__in=subject).select_related('subject').iterator():
prefetched[lesson_subject.lesson_id].append(lesson_subject.subject)
Simon
Le mercredi 16 septembre 2015 04:54:13 UTC-4, Erik Cederstrand a écrit :
>
> Hi folks,
>
> I'm working on a school timetable app. I want to fetch hundreds of
> thousands of Lesson instances with prefetched m2m relations (e.g.
> subjects). My m2m relations use through models (I'm not sure this actually
> makes a difference here), and I'm running into performance issues because
> the prefetch query does something along the lines of "SELECT ... FROM
> lesson_subjects WHERE lesson_id IN [insane_list_of_lesson_ids]".
>
> The initial query on Lesson uses a properly indexed filter on e.g. dates,
> so I thought I'd try to use the same filter to get the related Subjects via
> the relation to Lessons:
>
> qs = Subject.objects.filter(lessons__school_id=8,
> lessons__start__lt=datetime(2015, 8, 1))
> Lesson.objects.filter(school_id=8, start__lt=datetime(2015, 8, 1))\
> .prefetch_related(Prefetch('subjects', queryset=qs))
>
> I can see that the extra filters are added to the prefetch query, but the
> huge IN clause is still there.
>
> Am I using Prefetch() wrong? Are there any other techniques to avoid the
> huge IN clause in prefetch queries?
>
> Thanks,
> Erik
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/f3a3f269-993e-46dd-93ea-0ff015c52c1d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
