Cache attributes of related objects that are used for q filtering #543

New Issue

MrUnknownDE · 2026-04-05T16:41:46+02:00

MrUnknownDE commented

2026-04-05 16:41:46 +02:00

Originally created by @jeremystretch on 1/16/2026

NetBox version

v4.5.0

Feature type

New functionality

Proposed functionality

There are many instances throughout NetBox where the general-purpose q filter for a model references attributes on a related object. For example, the q filter for Device references the name of the assigned virtual chassis (if any) and the primary IPv4 and IPv6 addresses:

def search(self, queryset, name, value):
    if not value.strip():
        return queryset
    return queryset.filter(
        Q(name__icontains=value) |
        Q(virtual_chassis__name__icontains=value) |  # Requires a JOIN
        Q(serial__icontains=value.strip()) |
        Q(asset_tag__icontains=value.strip()) |
        Q(description__icontains=value.strip()) |
        Q(comments__icontains=value) |
        Q(primary_ip4__address__startswith=value) |  # Requires a JOIN
        Q(primary_ip6__address__startswith=value)    # Requires a JOIN
    ).distinct()

Instead of referencing these fields on related objects (which requires SQL JOINs`), we can copy the values of these fields locally on the model for efficient reference. The existing denormalization framework can be leveraged to accomplish this:

denormalized.register(Device, 'virtual_chassis', {
    '_virtual_chassis_name': 'name',
})
denormalized.register(Device, 'primary_ip4', {
    '_primary_ip4_address': 'address',
})
denormalized.register(Device, 'primary_ip6', {
    '_primary_ip6_address': 'address',
})

Use case

Each of these evaluations requires a SQL JOIN, which can significantly degrade performance in large databases as captured in bug #21196. Limiting the query to the primary table will greatly improve filtering performance.

Database changes

Add private IP address fields on the Device model to store the denormalized values:

    # Denormalized fields
    _virtual_chassis_name = models.CharField(
        blank=True,
    )
    _primary_ip4_address = IPAddressField(
        blank=True,
        null=True,
    )
    _primary_ip6_address = IPAddressField(
        blank=True,
        null=True,
    )

A migration is needed to add these fields on the model and to backfill existing IP address assignments.

External dependencies

N/A

*Originally created by @jeremystretch on 1/16/2026* ### NetBox version v4.5.0 ### Feature type New functionality ### Proposed functionality There are many instances throughout NetBox where the general-purpose `q` filter for a model references attributes on a related object. For example, the `q` filter for Device references the name of the assigned virtual chassis (if any) and the primary IPv4 and IPv6 addresses: ```python def search(self, queryset, name, value): if not value.strip(): return queryset return queryset.filter( Q(name__icontains=value) | Q(virtual_chassis__name__icontains=value) | # Requires a JOIN Q(serial__icontains=value.strip()) | Q(asset_tag__icontains=value.strip()) | Q(description__icontains=value.strip()) | Q(comments__icontains=value) | Q(primary_ip4__address__startswith=value) | # Requires a JOIN Q(primary_ip6__address__startswith=value) # Requires a JOIN ).distinct() ``` Instead of referencing these fields on related objects (which requires SQL `JOIN`s`), we can copy the values of these fields locally on the model for efficient reference. The existing denormalization framework can be leveraged to accomplish this: ```python denormalized.register(Device, 'virtual_chassis', { '_virtual_chassis_name': 'name', }) denormalized.register(Device, 'primary_ip4', { '_primary_ip4_address': 'address', }) denormalized.register(Device, 'primary_ip6', { '_primary_ip6_address': 'address', }) ``` ### Use case Each of these evaluations requires a SQL `JOIN`, which can significantly degrade performance in large databases as captured in bug #21196. Limiting the query to the primary table will greatly improve filtering performance. ### Database changes Add private IP address fields on the Device model to store the denormalized values: ```python # Denormalized fields _virtual_chassis_name = models.CharField( blank=True, ) _primary_ip4_address = IPAddressField( blank=True, null=True, ) _primary_ip6_address = IPAddressField( blank=True, null=True, ) ``` A migration is needed to add these fields on the model and to backfill existing IP address assignments. ### External dependencies N/A

MrUnknownDE closed this issue

2026-04-05 16:41:46 +02:00

Sign in to join this conversation.

Branches Tags

main

21455-sql-indexes-audit

21357-register-model-actions

feature

20924-plugin-ui-components

21766-improve-test-coverage-for-sortable-table-columns

21157-export-template-public-models

21025-pre-render-config-contexts

21364-swagger

feature-ip-prefix-link

20911-dropdown-3

fix_module_substitution

21160-filterset

20911-dropdown-2

20044-elevation-stuck-lightmode

7604-filter-modifiers-v3

20660-script-load

19724-graphql

14884-script

19720-macaddress-interface-generic-relation

fix-19669-api-image-download

7604-filter-modifiers

19275-fixes-interface-bulk-edit

fix-17794-get_field_value_return_list

11507-show-aggregate-and-rir-on-api

9583-add_column_specific_search_field_to_tables

No Label complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium complexity: medium netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox netbox status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted status: accepted type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature type: feature

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/netbox#543

Cache attributes of related objects that are used for q filtering #543