Config context API queries still slow for large installations despite DISTINCT optimization

[jnovinger]

NetBox Edition

NetBox Community

NetBox Version

v4.4.0

Python Version

3.10

Steps to Reproduce

1. Have a large installation (10k+ devices, 100+ config contexts)
2. Make API requests like /api/dcim/devices/?limit=10
3. Observe response times are still slower than expected for a 10-device response

While PR #20346 provided significant improvement (15x performance gain) for the config context query performance issue reported in #20327, users with large installations are still experiencing slower-than-acceptable API response times.

Expected Behavior

API requests for small result sets should return in reasonable time (<1s) regardless of total database size.

Observed Behavior

Even after the DISTINCT optimization in #20346 , API responses are still slower than expected for large installations.

[rfdrake]

I was curious about this because I ran a Netbox instance with 6000 devices and it was slow at times. I was never able to troubleshoot it because I couldn't think of a way to isolate the problem. I ended up running "manage.py reindex" sometimes to improve speeds, and I thought that was just the way of things.

I'm running 4.4.1 in a lab and just tried to replicate this. I created 22000 devices and 200 config contexts. My queries are around 140ms.

Can you try running a "VACUUM FULL ANALYZE" on the database? Since I'm in a lab there isn't much churn in my data, so I'm wondering if that is making it faster. It's also possible that tags or custom fields, or other things I don't have linked might be affecting the query time.

Can you post an "EXPLAIN" for the select statement that is >1s? With that we should be able to see if one of the row counts is much higher than mine.

One thing I'm noting is that there is a sequential scan on dcim_site, and I only have 173 sites to scan. When loading these devices I put them all in the same site, so that might be another reason why it's faster for me.

Anyway, here is my EXPLAIN for the 140ms query:

 Limit  (cost=3546.66..4340.44 rows=10 width=530)
   ->  Result  (cost=3546.66..1768036.52 rows=22229 width=530)
         ->  Sort  (cost=3546.66..3602.24 rows=22229 width=586)
               Sort Key: dcim_device.name COLLATE natural_sort, dcim_device.id
               ->  Hash Join  (cost=62.02..3066.30 rows=22229 width=586)
                     Hash Cond: (dcim_device.role_id = dcim_devicerole.id)
                     ->  Hash Left Join  (cost=60.82..2979.27 rows=22229 width=570)
                           Hash Cond: (dcim_site.group_id = dcim_sitegroup.id)
                           ->  Hash Left Join  (cost=59.33..2909.16 rows=22229 width=562)
                                 Hash Cond: (dcim_site.region_id = dcim_region.id)
                                 ->  Hash Left Join  (cost=56.07..2843.18 rows=22229 width=554)
                                       Hash Cond: (dcim_device.location_id = dcim_location.id)
                                       ->  Hash Join  (cost=44.72..2773.48 rows=22229 width=538)
                                             Hash Cond: (dcim_device.site_id = dcim_site.id)
                                             ->  Hash Left Join  (cost=12.82..2681.81 rows=22229 width=522)
                                                   Hash Cond: (dcim_device.tenant_id = tenancy_tenant.id)
                                                   ->  Hash Left Join  (cost=11.80..2622.44 rows=22229 width=514)
                                                         Hash Cond: (dcim_device.cluster_id = virtualization_cluster.id)
                                                         ->  Seq Scan on dcim_device  (cost=0.00..2552.29 rows=22229 width=498)
                                                         ->  Hash  (cost=10.80..10.80 rows=80 width=24)
                                                               ->  Seq Scan on virtualization_cluster  (cost=0.00..10.80 rows=80 width=24)
                                                   ->  Hash  (cost=1.01..1.01 rows=1 width=16)
                                                         ->  Seq Scan on tenancy_tenant  (cost=0.00..1.01 rows=1 width=16)
                                             ->  Hash  (cost=29.73..29.73 rows=173 width=24)
                                                   ->  Seq Scan on dcim_site  (cost=0.00..29.73 rows=173 width=24)
                                       ->  Hash  (cost=10.60..10.60 rows=60 width=24)
                                             ->  Seq Scan on dcim_location  (cost=0.00..10.60 rows=60 width=24)
                                 ->  Hash  (cost=2.56..2.56 rows=56 width=24)
                                       ->  Seq Scan on dcim_region  (cost=0.00..2.56 rows=56 width=24)
                           ->  Hash  (cost=1.22..1.22 rows=22 width=24)
                                 ->  Seq Scan on dcim_sitegroup  (cost=0.00..1.22 rows=22 width=24)
                     ->  Hash  (cost=1.09..1.09 rows=9 width=24)
                           ->  Seq Scan on dcim_devicerole  (cost=0.00..1.09 rows=9 width=24)
         SubPlan 2
           ->  Aggregate  (cost=79.36..79.37 rows=1 width=32)
                 ->  Sort  (cost=79.35..79.35 rows=1 width=268)
                       Sort Key: v0.weight, v0.name
                       ->  Nested Loop Left Join  (cost=30.89..79.34 rows=1 width=268)
                             Filter: (((v26.level <= dcim_devicerole.level) AND (v26.lft <= dcim_devicerole.lft) AND (v26.rght >= dcim_devicerole.rght) AND (v26.tree_id = dcim_devicerole.tree_id)) OR (v25.devicerole_id IS NULL))
                             ->  Nested Loop Left Join  (cost=30.75..77.89 rows=8 width=276)
                                   ->  Nested Loop Left Join  (cost=30.60..77.30 rows=1 width=276)
                                         Filter: (((v24.level <= dcim_sitegroup.level) AND (v24.lft <= dcim_sitegroup.lft) AND (v24.rght >= dcim_sitegroup.rght) AND (v24.tree_id = dcim_sitegroup.tree_id)) OR (v23.sitegroup_id IS NULL))
                                         ->  Nested Loop Left Join  (cost=30.46..75.84 rows=8 width=284)
                                               ->  Nested Loop Left Join  (cost=30.31..75.25 rows=1 width=276)
                                                     Filter: ((v19.devicetype_id = dcim_device.device_type_id) OR (v19.devicetype_id IS NULL))
                                                     ->  Nested Loop Left Join  (cost=30.16..74.65 rows=1 width=276)
                                                           Filter: (((v18.level <= dcim_location.level) AND (v18.lft <= dcim_location.lft) AND (v18.rght >= dcim_location.rght) AND (v18.tree_id = dcim_location.tree_id)) OR (v17.location_id IS NULL))
                                                           ->  Nested Loop Left Join  (cost=30.02..74.44 rows=1 width=284)
                                                                 ->  Nested Loop Left Join  (cost=29.86..73.85 rows=1 width=276)
                                                                       Filter: ((ANY (v15.tag_id = (hashed SubPlan 1).col1)) OR (v15.tag_id IS NULL))
                                                                       ->  Nested Loop Left Join  (cost=16.78..60.30 rows=1 width=276)
                                                                             Filter: (((v22.level <= dcim_region.level) AND (v22.lft <= dcim_region.lft) AND (v22.rght >= dcim_region.rght) AND (v22.tree_id = dcim_region.tree_id)) OR (v21.region_id IS NULL))
                                                                             ->  Nested Loop Left Join  (cost=16.48..58.29 rows=1 width=276)
                                                                                   Filter: ((v13.site_id = dcim_device.site_id) OR (v13.site_id IS NULL))
                                                                                   ->  Nested Loop Left Join  (cost=16.33..57.68 rows=1 width=276)
                                                                                         Filter: ((v11.tenant_id = dcim_device.tenant_id) OR (v11.tenant_id IS NULL))
                                                                                         ->  Nested Loop Left Join  (cost=16.18..57.07 rows=1 width=276)
                                                                                               Filter: ((v9.tenantgroup_id = tenancy_tenant.group_id) OR (v9.tenantgroup_id IS NULL))
                                                                                               ->  Nested Loop Left Join  (cost=16.03..56.46 rows=1 width=276)
                                                                                                     Filter: ((v7.cluster_id = dcim_device.cluster_id) OR (v7.cluster_id IS NULL))
                                                                                                     ->  Nested Loop Left Join  (cost=15.87..55.85 rows=1 width=276)
                                                                                                           Filter: ((v5.clustergroup_id = virtualization_cluster.group_id) OR (v5.clustergroup_id IS NULL))
                                                                                                           ->  Nested Loop Left Join  (cost=15.72..55.25 rows=1 width=276)
                                                                                                                 Filter: ((v3.clustertype_id = virtualization_cluster.type_id) OR (v3.clustertype_id IS NULL))
                                                                                                                 ->  Hash Right Join  (cost=15.57..45.50 rows=16 width=276)
                                                                                                                       Hash Cond: (v1.configcontext_id = v0.id)
                                                                                                                       Filter: ((v1.platform_id = dcim_device.platform_id) OR (v1.platform_id IS NULL))
                                                                                                                       ->  Seq Scan on extras_configcontext_platforms v1  (cost=0.00..25.70 rows=1570 width=16)
                                                                                                                       ->  Hash  (cost=13.03..13.03 rows=203 width=276)
                                                                                                                             ->  Seq Scan on extras_configcontext v0  (cost=0.00..13.03 rows=203 width=276)
                                                                                                                                   Filter: is_active
                                                                                                                 ->  Index Scan using extras_configcontext_cluster_types_configcontext_id_d549b6f2 on extras_configcontext_cluster_types v3  (cost=0.15..0.51 rows=8 width=16)
                                                                                                                       Index Cond: (configcontext_id = v0.id)
                                                                                                           ->  Index Scan using extras_configcontext_cluster_groups_configcontext_id_8f50b794 on extras_configcontext_cluster_groups v5  (cost=0.15..0.51 rows=8 width=16)
                                                                                                                 Index Cond: (configcontext_id = v0.id)
                                                                                                     ->  Index Scan using extras_configcontext_clusters_configcontext_id_ed579a40 on extras_configcontext_clusters v7  (cost=0.15..0.51 rows=8 width=16)
                                                                                                           Index Cond: (configcontext_id = v0.id)
                                                                                               ->  Index Scan using extras_configcontext_tenant_groups_configcontext_id_92f68345 on extras_configcontext_tenant_groups v9  (cost=0.15..0.51 rows=8 width=16)
                                                                                                     Index Cond: (configcontext_id = v0.id)
                                                                                         ->  Index Scan using extras_configcontext_tenants_configcontext_id_b53552a6 on extras_configcontext_tenants v11  (cost=0.15..0.51 rows=8 width=16)
                                                                                               Index Cond: (configcontext_id = v0.id)
                                                                                   ->  Index Scan using extras_configcontext_sites_configcontext_id_8c54feb9 on extras_configcontext_sites v13  (cost=0.15..0.51 rows=8 width=16)
                                                                                         Index Cond: (configcontext_id = v0.id)
                                                                             ->  Nested Loop Left Join  (cost=0.29..1.85 rows=8 width=32)
                                                                                   ->  Index Scan using extras_configcontext_regions_configcontext_id_73003dbc on extras_configcontext_regions v21  (cost=0.15..0.51 rows=8 width=16)
                                                                                         Index Cond: (configcontext_id = v0.id)
                                                                                   ->  Index Scan using dcim_region_pkey on dcim_region v22  (cost=0.14..0.17 rows=1 width=24)
                                                                                         Index Cond: (id = v21.region_id)
                                                                       ->  Index Scan using extras_configcontext_tags_configcontext_id_64a392b1 on extras_configcontext_tags v15  (cost=0.15..0.51 rows=8 width=16)
                                                                             Index Cond: (configcontext_id = v0.id)
                                                                       SubPlan 1
                                                                         ->  Unique  (cost=12.92..12.93 rows=1 width=8)
                                                                               ->  Sort  (cost=12.92..12.93 rows=1 width=8)
                                                                                     Sort Key: u0.tag_id
                                                                                     ->  Nested Loop  (cost=0.28..12.91 rows=1 width=8)
                                                                                           ->  Seq Scan on django_content_type u1  (cost=0.00..4.61 rows=1 width=4)
                                                                                                 Filter: (((app_label)::text = 'dcim'::text) AND ((model)::text = 'device'::text))
                                                                                           ->  Index Scan using extras_tagg_content_717743_idx on extras_taggeditem u0  (cost=0.28..8.29 rows=1 width=12)
                                                                                                 Index Cond: ((content_type_id = u1.id) AND (object_id = dcim_device.id))
                                                                 ->  Index Scan using extras_configcontext_locations_configcontext_id_cc629ec1 on extras_configcontext_locations v17  (cost=0.15..0.51 rows=8 width=16)
                                                                       Index Cond: (configcontext_id = v0.id)
                                                           ->  Index Scan using dcim_location_pkey on dcim_location v18  (cost=0.14..0.19 rows=1 width=24)
                                                                 Index Cond: (id = v17.location_id)
                                                     ->  Index Scan using extras_configcontext_device_types_configcontext_id_55632923 on extras_configcontext_device_types v19  (cost=0.15..0.51 rows=8 width=16)
                                                           Index Cond: (configcontext_id = v0.id)
                                               ->  Index Scan using extras_configcontext_site_groups_configcontext_id_2e0f43cb on extras_configcontext_site_groups v23  (cost=0.15..0.51 rows=8 width=16)
                                                     Index Cond: (configcontext_id = v0.id)
                                         ->  Index Scan using dcim_sitegroup_pkey on dcim_sitegroup v24  (cost=0.14..0.16 rows=1 width=24)
                                               Index Cond: (id = v23.sitegroup_id)
                                   ->  Index Scan using extras_configcontext_roles_configcontext_id_59b67386 on extras_configcontext_roles v25  (cost=0.15..0.51 rows=8 width=16)
                                         Index Cond: (configcontext_id = v0.id)
                             ->  Index Scan using dcim_devicerole_pkey on dcim_devicerole v26  (cost=0.14..0.16 rows=1 width=24)
                                   Index Cond: (id = v25.devicerole_id)