Django perform queryset so slow

I'm making a django app that calculates popularity from google trends. I tried to annotate the calculated values from graph data. However, it took about 5 seconds to execute this queryset. (Using sqlite3, postgresql) I have a few questions here.

  1. I wonder why it is so slow.
  2. I wonder if it can be improved.

annotate code

class DefaultCalculator(BaseCalculator):
    def annotate_score(self, queryset, name):
        qs = super().annotate_score(queryset, 'self_score')
        qs = self.annotate_std_score(qs, 'std_score')
        qs = self.annotate_std2_score(qs, 'std2_score')
        score = (
            F('self_score') /
            (F('std_score') + F('std2_score') + F('self_score'))
            * 100
        )
        return qs.annotate(**{name: score})

    def annotate_std_score(self, queryset, name):
        std_score = Avg(
            'standard__graph__date_points__value',
            filter=Q(standard__graph__date_points__date__gte=now() - timedelta(days=self.base_days))
        )
        return queryset.annotate(**{name: std_score})

    def annotate_std2_score(self, queryset, name):
        std2_score = Avg(
            'standard2__graph__date_points__value',
            filter=Q(standard2__graph__date_points__date__gte=now() - timedelta(days=self.base_days))
        )
        return queryset.annotate(**{name: std2_score})

query

SELECT 
"django_popularity_popularity"."id", 
"django_popularity_popularity"."mid", 
"django_popularity_popularity"."created", 
"django_popularity_popularity"."updated", 
"django_popularity_popularity"."title", 
"django_popularity_popularity"."type", 
"django_popularity_popularity"."geo", 
"django_popularity_popularity"."standard_id", 
"django_popularity_popularity"."standard2_id", 
"django_popularity_popularity"."graph_id", 
1080 AS "base_days", 
AVG(
    "django_popularity_dategraphpoint"."value"
) FILTER (
    WHERE 
    "django_popularity_dategraphpoint"."date" >= 2019 - 09 - 30
) AS "score", 
AVG(T6."value") FILTER (
    WHERE 
    T6."date" >= 2019 - 09 - 30
) AS "std_score", 
AVG(T9."value") FILTER (
    WHERE 
    T9."date" >= 2019 - 09 - 30
) AS "std2_score" 
FROM 
"django_popularity_popularity" 
INNER JOIN "django_popularity_graph" ON (
    "django_popularity_popularity"."graph_id" = "django_popularity_graph"."id"
) 
LEFT OUTER JOIN "django_popularity_dategraphpoint" ON (
    "django_popularity_graph"."id" = "django_popularity_dategraphpoint"."graph_id"
) 
INNER JOIN "django_popularity_standard" ON (
    "django_popularity_popularity"."standard_id" = "django_popularity_standard"."id"
) 
INNER JOIN "django_popularity_graph" T5 ON (
    "django_popularity_standard"."graph_id" = T5."id"
) 
LEFT OUTER JOIN "django_popularity_dategraphpoint" T6 ON (T5."id" = T6."graph_id") 
INNER JOIN "django_popularity_standard" T7 ON (
    "django_popularity_popularity"."standard2_id" = T7."id"
) 
INNER JOIN "django_popularity_graph" T8 ON (T7."graph_id" = T8."id") 
LEFT OUTER JOIN "django_popularity_dategraphpoint" T9 ON (T8."id" = T9."graph_id") 
GROUP BY 
"django_popularity_popularity"."id", 
"django_popularity_popularity"."mid", 
"django_popularity_popularity"."created", 
"django_popularity_popularity"."updated", 
"django_popularity_popularity"."title", 
"django_popularity_popularity"."type", 
"django_popularity_popularity"."geo", 
"django_popularity_popularity"."standard_id", 
"django_popularity_popularity"."standard2_id", 
"django_popularity_popularity"."graph_id"

full source

Back to Top