[Python] What is the most optimize way to use annotate with Count function with many filter in...

For example in school, there is many data to store example "Class", "Student", "Book", etc. Also, the model used softdelete(softdelete refers to action when delete just change the is_deleted to True in the model). In each model, there are create_time, update_time, is_delete, etc. In my code now I use the method below, it spent at least 30s to run sql.

So what is the most optimised way to query Count with the filter that contains many conditions? Also sometimes we need to count the same model but using the different filter

Here is an example of code in Django (Check count data for each school).

# In this code, each model already implements softdelete manager query.
School.objects.annotate(
number_of_class=Count(
"class", filter=Q(class__is_deleted=False, class__create_time__lte=datetime, ...more filter), distinct=True
),
number_of_student=Count(
"student", filter=Q(student__is_deleted=False, student__create_time__lte=datetime, ...more filter), distinct=True
),
number_of_public_book=Count(
"book", filter=Q(book__is_deleted=False, book__create_time__lte=datetime, book__create_time__gte=datetime, book__type=1, ...more filter), distinct=True
),
number_of_private_book=Count(
"book", filter=Q(book__is_deleted=False, book__create_time__lte=datetime, book__create_time__gte=datetime, book__type__in=[2,3], ...more filter), distinct=True
)
more_count...
)


Please help give all code queries that you know and what you think can be optimised for counting with many counts and filters.

Continue reading...