Optimization Subquery Django ORM

I have function with some calculations. Subquery with OuterRef running too long, how i can optimization or find another way to get id for average_ndmi?

def get_ranking_manager_ndvi_regions(
    year: str, month: int, pk: Optional[int] = None
) -> List[Region]:

    return (
        get_regions(pk=pk)
        .annotate(
            year=SearchVector("commune__field__fieldattribute__date__year"),
            month=SearchVector(
                Cast("commune__field__fieldattribute__date__month", CharField())
            ),
            size=Sum(F("commune__field__fieldattribute__planted_area")),
            field_count=Count("commune__field"),
        )
        .filter(year=year, month=str(month))
        .annotate(
            average_ndvi=Subquery(
                Field.objects.filter(commune__region__id=OuterRef("pk"))
                .values("commune__region__id")
                .annotate(ndvi_avg=Avg("fieldattribute__ndvi"))
                .values("ndvi_avg"),
                output_field=FloatField(),
            )
        )
        .annotate(
            standart_deviation=(
                Sum(
                    (F("commune__field__fieldattribute__ndvi") - F("average_ndvi"))
                    ** 2,
                    output_field=FloatField(),
                )
                / F("field_count")
            )
            ** (1 / 2)
        )
    )```

To optimize the performance of the Subquery in your function, you can try the following approaches:

  1. Use the .only() method to limit the fields that are retrieved from the database. This can reduce the amount of data that needs to be transferred between the database and the application, and can improve performance. For example:

Field.objects.only("commune__region__id", "fieldattribute__ndvi")

  1. Use the .select_related() method to prefetch related objects. This can reduce the number of database queries that need to be made, and can improve performance. For example:

Field.objects.select_related("commune__region", "fieldattribute")

  1. Use the .prefetch_related() method to prefetch many-to-many and one-to-many relationships. This can also reduce the number of database queries that need to be made, and can improve performance. For example:

    Field.objects.prefetch_related("fieldattribute")
    

I hope these suggestions help improve the performance of your function.

Back to Top