Thanks for your reply Josh. Can you elaborate on why optimizing out '
.annotate(local=F('related__field'))' would not be safe?
On 20 Aug 2017 00:10, "Josh Smeaton" <josh.smea...@gmail.com> wrote:
I'd like to see this provided all bases were covered. I'll just list below
the cases where I think it wouldn't be safe.
- Filtered annotations
- Annotations that join to anything other than a non-null foreign key:
.annotate(local=F('related__field'))
- Annotations that have a GROUP BY on fields that are not just the PK or
entire field set (with .values())
The group by one I'm unsure of, because I forget if a count is called with
an aggregate query as a subquery or if it's an error.
I think an optimisation is available if:
1. No filters ref an annotation
2. values() has not been called
3. There are no joins at all
Further optimisation scenarios may be available with stricter rules (such
as join types), but we could look at doing that separately if it was
difficult.
On Sunday, 20 August 2017 03:11:13 UTC+10, Tom Forbes wrote:
>
> Hello,
> I think there is potential room for improvement with how Django handles
> count() queries, specifically relating to annotations. Currently any
> annotations that are added to a queryset are included in the SQL statement
> that is generated in the count() query, including all joins and SQL
> function calls - I've personally been bitten by this with DRF and a query
> with a particularly complex set of annotations where the count query took
> longer than fetching the data itself, but was semantically equivalent to a
> much faster, plain Model.objects.count() call.
>
> I think in most cases annotations can be stripped, for example in the two
> queries below the annotation is not filtered on so it can be safely removed
> from the query without affecting the result:
>
> one = Book.objects.annotate(Count('chapters')).count()
> two = Book.objects.count()
>
> I wanted to gather some feedback from the group as to whether this always
> holds true: if an annotation is not filtered (or ordered on) it can always
> be safely removed from a count query? I wouldn't be surprised if there is a
> case where this isn't safe, but I cannot think of one.
>
> I've made an initial merge request that removes annotations from querysets
> that have no filter or ordering that depends on annotations here:
> https://github.com/django/django/pull/8928. It seems like Django also has
> all the information to optimize out annotations that are not directly or
> indirectly used by filters, but I wanted to gather some feedback before I
> attempted this.
>
> Tom
>
--
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/506d0f2c-51e8-400f-a4fb-66c2b8597aeb%40googlegroups.
com
<https://groups.google.com/d/msgid/django-developers/506d0f2c-51e8-400f-a4fb-66c2b8597aeb%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/CAFNZOJP%3DwknS2hDLT2XE4qxKn6MXrMNK2C4AHMLpY7T0ywi%3D9A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
