Increase performance with prefetch and select related

I’m trying to export a csv with information from the model order which has a relation 1 to 1 with delivery channel and restaurant and a 1 to many relationship with orderlines. It is taking way too much time for download it (around 20 seconds for 10k lines).

This is my code:

orderlines = OrderLine.objects.select_related("product").only(
                "product__display_name", "quantity", "paid_amount", "discount_amount"
            )
return (
    Order.objects.prefetch_related(Prefetch("orderlines", queryset=orderlines, to_attr="orderlines_list"))
    .select_related("delivery_channel")
    .select_related("restaurant")
)

These are my models:

class Order(TimeStampedModel, TenantModel):

    id = models.AutoField
    objects = OrderManager()

    restaurant = models.ForeignKey(
        Restaurant,
        blank=False,
        null=False,
        on_delete=models.PROTECT,
        help_text=_("References the restaurant the order is for"),
    )

    delivery_channel = models.ForeignKey(
        DeliveryChannel,
        blank=False,
        null=False,
        on_delete=models.PROTECT,
    )

class Restaurant(TenantModel):
    id = models.AutoField
    name = models.CharField(max_length=255, blank=False, null=False, default="", unique=False)

class DeliveryChannel(models.Model):
    id = models.AutoField
    name = models.CharField(
        max_length=255, blank=False, null=False, unique=True, default="", 
    )

class OrderLine(TimeStampedModel, TenantModel):
    id = models.AutoField
    order = models.ForeignKey(
        Order,
        blank=False,
        null=False,
        on_delete=models.CASCADE,
        related_name="orderlines",
    )

    product = models.ForeignKey(
        Product, blank=False, null=False, on_delete=models.CASCADE, 
    )
    unit_price = models.DecimalField(
        blank=False,
        null=False,
        max_digits=8,
        decimal_places=2,
    )
    quantity = models.IntegerField(blank=False, null=False, unique=False)
    paid_amount = models.DecimalField(
        blank=False,
        null=False,
        max_digits=8,
        decimal_places=2,
    )
    discount_amount = models.DecimalField(
        blank=False,
        null=False,
        max_digits=8,
        decimal_places=2,
        help_text=_("Amount of the discount applied to the product"),
    )


class Product(TenantModel):
    id = models.AutoField
    objects = ProductManager()
    display_name = models.CharField(max_length=255, blank=False, null=False, unique=False)

I thought about using only in the end but I can’t use it on orderlines as it is a 1 to many relationship. I’m stuck on how to improve the performance. Many thanks.

Answer

You can use .only(…) [Django-doc], but you need to add the foreign key to the model itself, otherwise this will result in another N+1 problem: where it will each time have to make a query to determine to what Order it belongs, so:

orderlines = OrderLine.objects.select_related('product').only(
    'product__display_name',
    #             add ForeignKey to the Order object ↓
    'quantity', 'paid_amount', 'discount_amount', 'order'
)

return Order.objects.prefetch_related(
    Prefetch('orderlines', queryset=orderlines, to_attr='orderlines_list')
).select_related(
    'delivery_channel'
    'restaurant'
)