django postgresql django-views django-orm django-tables2

Django Postgresql ORM optimisation

I have a PostgreSQL view called view_sales_dashboard - this consists of several millions rows of daily sales data.

In the Django view I want to present a table grouped by the products, with the columns as the total base_daily_pnl of different time periods - Daily, Month to date (MTD), Quarter to date (QTD), Year to date (YTD) and Inception to date (ITD)

In order to try and limit the number of SQL queries I am creating 5 querysets to then generate the table. To improve the efficiency of this I investigated the logs and expected to see 5 SQL queries. However the logging shows 20 queries (5 product types * the 4 aggregate groupings + the daily series request).

See below the Django code, ORM model and the logs.

Can anyone advise 1.) why so many SQL queries are being triggered 2.) how to optimise?

NB - queryset_daily_products is a queryset of daily sales data, grouped by product. It looks like the below. I am then looping through trying to append the MTD, QTD, YTD, ITD base_daily_pnl total for each product

[{'product': 'sweets', 'base_daily_pnl':3000}, 
 {'product': 'car', 'base_daily_pnl':3000}, 
etc .....
]

The result I want (which that loop provides but with loads of unnecessary sql requests is)

[{'product': 'sweets', 'base_daily_pnl':3000, 'mtd_pnl': 5000, 'qtd_pnl':6000,'ytd_pnl':8000, 'itd_pnl':10000},
 {'product': 'car', 'base_daily_pnl':4000, 'mtd_pnl': 5100, 'qtd_pnl':6300,'ytd_pnl':8600, 'itd_pnl':12000},
etc .....
]

queryset_sales_all = SalesDashboard.objects.all()

queryset_daily_products = queryset_pnl_all.filter(position_date__range=[latest_pnl_date_str, latest_pnl_date_str]).values('product').annotate(base_daily_pnl=Sum('base_daily_pnl'),base_lmv=Sum('base_lmv'))  
    
    for product in queryset_daily_products:
        matching_mtd = queryset_pnl_all.filter(position_date__range=[start_mth_str,latest_pnl_date_str]).values('product').annotate(mtd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_qtd = queryset_pnl_all.filter(position_date__range=[start_qtd_str, latest_pnl_date_str]).values('product').annotate(qtd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_ytd = queryset_pnl_all.filter(position_date__range=[start_year_str, latest_pnl_date_str]).values('product').annotate(ytd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_itd = queryset_pnl_all.filter(position_date__range=[start_itd_str, latest_pnl_date_str]).values('product').annotate(itd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        product['mtd_pnl'] = matching_mtd['mtd_pnl']
        product['qtd_pnl'] = matching_qtd['qtd_pnl']
        product['ytd_pnl'] = matching_ytd['ytd_pnl']
        product['itd_pnl'] = matching_itd['itd_pnl'] 

pnl_product = SummaryPnlProductTable(queryset_daily_product)

Below is the ORM model:

class SalesDashboard(models.Model):    

    unqiue_id = models.IntegerField(primary_key=True)    
    sales_id = models.CharField(max_length=50)
    base_daily_pnl = models.FloatField(default=0)
    position_date = models.DateField()
    book_id = models.IntegerField()
    book = models.CharField(max_length=100, blank=True, null=True)
    product = models.CharField(max_length=100, blank=True, null=True)
    customer = models.CharField(max_length=100)
    base_lmv = models.FloatField(default=0)

   
    class Meta: 
        managed = False       
        db_table = 'view_sales_dashboard

Logging:

2.109) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "base_daily_pnl", SUM("view_sales_dashboard"."base_lmv") AS "base_lmv" FROM "view_sales_dashboard" WHERE "view_sales_dashboard"."position_date" BETWEEN '2024-06-30'::date AND '2024-06-30'::date GROUP BY "view_sales_dashboard"."product"; args=(datetime.date(2024, 6, 30), datetime.date(2024, 6, 30)); alias=default
(2.078) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.047) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.094) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.250) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'car'); alias=default
(2.156) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.093) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.172) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.875) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.110) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.156) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.203) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.516) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.281) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.125) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.250) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.594) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.265) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.125) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.188) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.407) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'sundaries'); alias=default

Solution

You can easily "batch" the queries such that we only need four for all products.

First we need to make the dictionaries easier accessible, we can do that with a:

lut = {item['products']: item for item in queryset_daily_products}


def update_lut(qs, start, target, lut):
    for item in (
        qs.filter(
            position_date__range=[start, latest_pnl_date_str], product__in=lut
        )
        .values('product')
        .annotate(result=Sum('base_daily_pnl'))
    ):
        lut[item['product']][target] = item['result']


update_lut(queryset_pnl_all, start_mth_str, 'mtd_pnl', lut)
update_lut(queryset_pnl_all, start_qtd_str, 'qtd_pnl', lut)
update_lut(queryset_pnl_all, start_year_str, 'ytd_pnl', lut)
update_lut(queryset_pnl_all, start_itd_str, 'itd_pnl', lut)

We can in fact rewrite this to do it with a single query, by using a filter=… parameter ^[Django-doc], but that is likely not necessary.