After some more investigation yesterday, I found out that it isn't the prefetches and the subquery, and exactly as you write, it's annotations which generate joins, and for count() the difference is it has to go thru the whole db table, and that is slowing it down a lot. For my queryset, the normal .all() query takes 0.006s, and the count query takes 16.6s. And I noticed, that even for queries that didn't seem problematic (1 join or so), it actually slows them down a lot, so basically every queryset in a paginated view is getting slowed down if it's a bit more complex, and you're totally true that the real solution would be to optimize them directly for the .count() calls. That would be an awesome tweak! :)
Yesterday I was looking into Django, because I needed to implement some workaround for my app, and created a PR which doesn't seem to me like a good change now. I basically implemented the stuff mentioned in my first message: https://github.com/django/django/pull/10114 I guess I can reply to the PR and close it now, sorry for the SPAM, I thought it was a good idea to have it there. For now I have a workaround which basically adds the functionality in the PR to my views using a mixin, so It's fine, but I would be really happy to see the tweak you mentioned in Django some day :) Thank you for your time, Jakub. On Saturday, June 30, 2018 at 5:40:32 PM UTC+2, Tom Forbes wrote: > > Are you sure it is the prefetches that is causing this? As Adam pointed > out these are correctly ignored. Annotations however are not, which can > cause unnecessary work and longer execution times. i.e: > > Book.objects.annotate(something=Max('pages__word_count')).count() > > We have enough information to be able to strip the pages join when using > count() it certain conditions are met, and this might be preferable than > adding workarounds to various places. > > > On Fri, 29 Jun 2018, 22:00 Jakub Kleň, <kuk...@gmail.com <javascript:>> > wrote: > >> Hi guys, I came across a problem today while testing my webapp with a >> full migrations applied from my legacy site for the first time. >> The problem is I'm using Prefetch in my view query (get_queryset), and it >> works fine when I just fetch it, but the problem comes with the .count() >> query which generates sql with subqueries, and it takes really long to >> process by MySQL. >> The nicest solution I'm thinking about would be to use 2 different >> queries for object_list / pagination count, because for count the query >> would be really simple, and wouldn't need the prefetches. >> I think this scenario may be pretty likely to come across also for other >> Django users, so I'm thinking if it wouldn't be nice to have it directly in >> Django. >> From my point of view, it seems like the implementation would be pretty >> straightforward and easy, and I would even like to look into it and >> contribute to the Django project if possible and if you think it would be >> useful. >> I would like to add count_queryset param to Paginator.__init__, and def >> get_count_queryset to MultipleObjectMixin. Happy to hear your opinions on >> this :) >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers (Contributions to Django itself)" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-develop...@googlegroups.com <javascript:>. >> To post to this group, send email to django-d...@googlegroups.com >> <javascript:>. >> Visit this group at https://groups.google.com/group/django-developers. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/a29e6b81-f05c-46af-a479-04e1f4bce705%40googlegroups.com >> >> <https://groups.google.com/d/msgid/django-developers/a29e6b81-f05c-46af-a479-04e1f4bce705%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a294cf3c-9509-4788-8f18-b4e33a580b4a%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
