Repository: netbox-community/netbox
Pull request: Fixes #19309: N+1 problem on /interfaces, /ip-addresses and /prefixes requests
### Fixes: #19309
In case of requesting `/interfaces`, `/ip-addresses` and `/prefixes` some of the fields are selected separately for each model. We should prefetch them.


Problem statement:
N+1 problem in dcim/ipam API calls
### Deployment Type

Self-hosted

### NetBox Version

v4.2.8

### Python Version

3.10

### Steps to Reproduce

1. Enable SQL logging
2. Do API requests:
* api/ipam/ip-addresses/
* api/ipam/prefixes
* api/dcim/interfaces


### Expected Behavior

All required data is loaded by batch SQL requests

### Observed Behavior

In the output you will see repeated SQL calls for each object in the response set:
* `circuits_virtualcircuittermination` for interfaces
* `dcim_interface` folowing by `dcim_device` for ip-address
* `dcim_site` for prefixes

Hints from issue discussion:

Commit message: Fixes N+1 problem on /interfaces, /ip-addresses and /prefixes requests

Patch to review:
diff --git a/netbox/dcim/api/views.py b/netbox/dcim/api/views.py
index 5ca851bcaa9..3d9c49b0861 100644
--- a/netbox/dcim/api/views.py
+++ b/netbox/dcim/api/views.py
@@ -455,6 +455,7 @@ class InterfaceViewSet(PathEndpointMixin, NetBoxModelViewSet):
                 Interface.objects.select_related("device", "cable"),
             ],
         ),
+        'virtual_circuit_termination',
         'l2vpn_terminations',  # Referenced by InterfaceSerializer.l2vpn_termination
         'ip_addresses',  # Referenced by Interface.count_ipaddresses()
         'fhrp_group_assignments',  # Referenced by Interface.count_fhrp_groups()
diff --git a/netbox/ipam/api/views.py b/netbox/ipam/api/views.py
index 783d13523e0..1b815c5db7b 100644
--- a/netbox/ipam/api/views.py
+++ b/netbox/ipam/api/views.py
@@ -1,5 +1,6 @@
 from copy import deepcopy
 
+from django.contrib.contenttypes.prefetch import GenericPrefetch
 from django.core.exceptions import ObjectDoesNotExist, PermissionDenied
 from django.db import transaction
 from django.shortcuts import get_object_or_404
@@ -13,6 +14,7 @@
 from rest_framework.routers import APIRootView
 from rest_framework.views import APIView
 
+from dcim.models import Interface
 from ipam import filtersets
 from ipam.models import *
 from ipam.utils import get_next_available_prefix
@@ -79,7 +81,9 @@ class RoleViewSet(NetBoxModelViewSet):
 
 
 class PrefixViewSet(NetBoxModelViewSet):
-    queryset = Prefix.objects.all()
+    queryset = Prefix.objects.prefetch_related(
+        "scope",
+    ).all()
     serializer_class = serializers.PrefixSerializer
     filterset_class = filtersets.PrefixFilterSet
 
@@ -100,7 +104,14 @@ class IPRangeViewSet(NetBoxModelViewSet):
 
 
 class IPAddressViewSet(NetBoxModelViewSet):
-    queryset = IPAddress.objects.all()
+    queryset = IPAddress.objects.prefetch_related(
+        GenericPrefetch(
+            "assigned_object",
+            [
+                Interface.objects.select_related("device"),
+            ],
+        ),
+    ).all()
     serializer_class = serializers.IPAddressSerializer
     filterset_class = filtersets.IPAddressFilterSet
 


Code review comments:
diff_hunk: @@ -79,7 +81,9 @@ class RoleViewSet(NetBoxModelViewSet):
 
 
 class PrefixViewSet(NetBoxModelViewSet):
-    queryset = Prefix.objects.all()
+    queryset = Prefix.objects.prefetch_related(
+        "scope",
+    ).all()
path: netbox/ipam/api/views.py
text: @user1:
The trailing `.all()` is unnecessary here (and below on `IPAddressViewSet.queryset`).

@author:
removed

@user1:
Thanks, I'll get the review finished at some point today.