Thiết kế website giá rẻ

Question

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 Daily and 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?

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 daily in queryset_daily_product:
        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'])
        daily['mtd_pnl'] = matching_mtd['mtd_pnl']
        daily['qtd_pnl'] = matching_qtd['qtd_pnl']
        daily['ytd_pnl'] = matching_ytd['ytd_pnl']
        daily['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

Thiết kế website giá rẻ

Danh mục

Django Postgresql ORM optimisation