Как ускорить агрегирование в Django

У меня есть довольно сложный запрос, который я выполняю для отчета Trial Balance, который собирает текущие и предыдущие суммы дебета и кредита и агрегирует сумму.

В Django я использую ORM для создания запроса, и его загрузка занимает около 5-6 секунд. Я решил, что это дело в ORM, и попытался использовать чистый SQL в PGAdmin, и время выполнения запроса такое же, я пытаюсь понять, как я могу улучшить скорость.

Примечание У меня есть индексы на date_entered, property и is_voided, так что это не должно быть узким местом. Кроме того, в запрашиваемой таблице более миллиона строк.

Вот SQL:

SELECT "accounting_glaccount"."deleted",
       "accounting_glaccount"."date_entered",
       "accounting_glaccount"."last_update",
       "accounting_glaccount"."uuid",
       "accounting_glaccount"."deleted_by_cascade",
       "accounting_glaccount"."id",
       "accounting_glaccount"."account_name",
       "accounting_glaccount"."account_identifier",
       "accounting_glaccount"."sortable_account_identifier",
       "accounting_glaccount"."account_type",
       "accounting_glaccount"."account_designation",
       "accounting_glaccount"."balance_type",
       "accounting_glaccount"."report_type",
       "accounting_glaccount"."report_designation",
       "accounting_glaccount"."description",
       "accounting_glaccount"."last_edited_by_id",
       "accounting_glaccount"."submitted_by_id",
       (
        SELECT SUM(U0."credit_amount") AS "aggregation"
          FROM "accounting_generalledger" U0
         WHERE ((U0."date_entered")::date >= '2024-02-06'::date AND (U0."date_entered")::date <= '2024-02-06'::date AND U0."property_id" = 20 AND NOT U0."is_voided" AND U0."account_id" = ("accounting_glaccount"."id") AND U0."deleted" IS NULL)
         GROUP BY U0."account_id"
       ) AS "current_credit",
       (
        SELECT SUM(U0."debit_amount") AS "aggregation"
          FROM "accounting_generalledger" U0
         WHERE ((U0."date_entered")::date >= '2024-02-06'::date AND (U0."date_entered")::date <= '2024-02-06'::date AND U0."property_id" = 20 AND NOT U0."is_voided" AND U0."account_id" = ("accounting_glaccount"."id") AND U0."deleted" IS NULL)
         GROUP BY U0."account_id"
       ) AS "current_debit",
       (
        SELECT SUM(U0."credit_amount") AS "aggregation"
          FROM "accounting_generalledger" U0
         WHERE ((U0."date_entered")::date < '2024-02-06'::date AND U0."property_id" = 20 AND NOT U0."is_voided" AND U0."account_id" = ("accounting_glaccount"."id") AND U0."deleted" IS NULL)
         GROUP BY U0."account_id"
       ) AS "prior_credit",
       (
        SELECT SUM(U0."debit_amount") AS "aggregation"
          FROM "accounting_generalledger" U0
         WHERE ((U0."date_entered")::date < '2024-02-06'::date AND U0."property_id" = 20 AND NOT U0."is_voided" AND U0."account_id" = ("accounting_glaccount"."id") AND U0."deleted" IS NULL)
         GROUP BY U0."account_id"
       ) AS "prior_debit"
  FROM "accounting_glaccount"
 WHERE (UPPER("accounting_glaccount"."account_type"::text) = UPPER('Regular') AND "accounting_glaccount"."deleted" IS NULL)
 ORDER BY "accounting_glaccount"."sortable_account_identifier" ASC

Вот оригинальный запрос к Django ORM:

gl_accounts = GLAccount.objects.order_by('sortable_account_identifier').filter(account_type__iexact='Regular').annotate(
                current_credit=SubqueryAggregate(
                    'general_ledger__credit_amount',
                    filter=(Q(date_entered__date__gte=start_date,
                              date_entered__date__lte=end_date) & Q(property=property) & Q(is_voided=False)),
                    aggregate=Sum),
                current_debit=SubqueryAggregate(
                    'general_ledger__debit_amount',
                    filter=(Q(date_entered__date__gte=start_date,
                              date_entered__date__lte=end_date) & Q(property=property) & Q(is_voided=False)),
                    aggregate=Sum),
                prior_credit=SubqueryAggregate(
                    'general_ledger__credit_amount',
                    filter=(Q(date_entered__date__lt=start_date) & Q(property=property) & Q(is_voided=False)),
                    aggregate=Sum),
                prior_debit=SubqueryAggregate(
                    'general_ledger__debit_amount',
                    filter=(Q(date_entered__date__lt=start_date) & Q(property=property) & Q(is_voided=False)),
                    aggregate=Sum)
            ).iterator()

Как я могу ускорить это?

НОТА

Я нашел онлайн-оптимизатор запросов, который предлагает добавить эти индексы, но я бы предпочел сделать это на Django, а не на чистом SQL, как я могу это сделать?

CREATE INDEX accounting_general_idx_prope_delet_accou_credi_date_is_vo ON "accounting_generalledger" ("property_id","deleted","account_id","credit_amount","date_entered","is_voided");
CREATE INDEX accounting_general_idx_prope_delet_accou_debit_date_is_vo ON "accounting_generalledger" ("property_id","deleted","account_id","debit_amount","date_entered","is_voided");
CREATE INDEX accounting_glaccou_idx_upperacc_deleted_sortable ON "accounting_glaccount" ((UPPER("account_type"::text)),"deleted","sortable_account_identifier");

Вернуться на верх