Add index on extras_cachedvalue.object_id for better update performance with many interfaces #9164

New Issue

adam · 2025-12-29T20:46:25+01:00

adam commented

2025-12-29 20:46:25 +01:00

Originally created by @arjanhak on GitHub (Jan 29, 2024).

Originally assigned to: @abhi1693 on GitHub.

NetBox version

v3.7.0

Feature type

Data model extension

Proposed functionality

Add an index to the object_id column on the extras_cachedvalue table.

This was suggested before in #11775 but for inserting new interfaces, and that was solved in #11785 by skipping the clearing of the cache on inserts, but that logic does of course not apply to updating interfaces.
Then there was an implementation of indexes for all generic foreign keys in #11790, but that did not include this object_id as it is something that can refer to multiple tables and is therefore not a formal foreign key.

Use case

When there are many interfaces (we are currently working with 300k+, times 7 attributes gives 2.2 million rows in the extras_cachedvalue table), updating (many of) them takes a lot of time. Looking at the PostgreSQL stats, this is the query that takes a lot of time:

DELETE FROM "extras_cachedvalue" WHERE ("extras_cachedvalue"."object_id" = 268011 AND "extras_cachedvalue"."object_type_id" = 38)

where in this instance 38 is the ID of dcim.interface, and that object_id is just an example.

Adding the suggested index made a big improvement in the time it takes to process the updates.

Database changes

CREATE INDEX extras_cachedvalue_object_id_TBD ON extras_cachedvalue USING btree(object_id);

External dependencies

No response

Originally created by @arjanhak on GitHub (Jan 29, 2024). Originally assigned to: @abhi1693 on GitHub. ### NetBox version v3.7.0 ### Feature type Data model extension ### Proposed functionality Add an index to the object_id column on the extras_cachedvalue table. This was suggested before in #11775 but for inserting new interfaces, and that was solved in #11785 by skipping the clearing of the cache on inserts, but that logic does of course not apply to updating interfaces. Then there was an implementation of indexes for all generic foreign keys in #11790, but that did not include this object_id as it is something that can refer to multiple tables and is therefore not a formal foreign key. ### Use case When there are many interfaces (we are currently working with 300k+, times 7 attributes gives 2.2 million rows in the extras_cachedvalue table), updating (many of) them takes a lot of time. Looking at the PostgreSQL stats, this is the query that takes a lot of time: ``` DELETE FROM "extras_cachedvalue" WHERE ("extras_cachedvalue"."object_id" = 268011 AND "extras_cachedvalue"."object_type_id" = 38) ``` where in this instance 38 is the ID of dcim.interface, and that object_id is just an example. Adding the suggested index made a big improvement in the time it takes to process the updates. ### Database changes CREATE INDEX extras_cachedvalue_object_id_TBD ON extras_cachedvalue USING btree(object_id); ### External dependencies _No response_

adam added the status: accepted type: feature labels 2025-12-29 20:46:25 +01:00

adam closed this issue

2025-12-29 20:46:25 +01:00

adam commented

2025-12-29 20:46:27 +01:00

@jeremystretch commented on GitHub (Jan 29, 2024):

Then there was an implementation of indexes for all generic foreign keys in https://github.com/netbox-community/netbox/issues/11790, but that did not include this object_id as it is something that can refer to multiple tables and is therefore not a formal foreign key.

That's what "generic" foreign key means in Django vernacular: a foreign key that can refer to multiple object types. This should have been implemented under #11790 as you point out, and more recently under #14436 for v3.7. I suspect it was missed because CachedValue is using our custom RestrictedGenericForeignKey field instead of the normal GenericForeignKey, so it escaped both audits.

CREATE INDEX extras_cachedvalue_object_id_TBD ON extras_cachedvalue USING btree(object_id);

We should create an index for both the object type and object ID fields, as we've done for other GFKs, because both are needed to query a record by a related object. This is done under the model's Meta class:

class Meta:
    indexes = (
        models.Index(fields=('object_type', 'object_id')),
    )

Thanks for the report!

@jeremystretch commented on GitHub (Jan 29, 2024): > Then there was an implementation of indexes for all generic foreign keys in https://github.com/netbox-community/netbox/issues/11790, but that did not include this object_id as it is something that can refer to multiple tables and is therefore not a formal foreign key. That's what ["generic" foreign key](https://docs.djangoproject.com/en/5.0/ref/contrib/contenttypes/#generic-relations) means in Django vernacular: a foreign key that can refer to multiple object types. This should have been implemented under #11790 as you point out, and more recently under #14436 for v3.7. I suspect it was missed because CachedValue is using our custom `RestrictedGenericForeignKey` field instead of the normal `GenericForeignKey`, so it escaped both audits. > `CREATE INDEX extras_cachedvalue_object_id_TBD ON extras_cachedvalue USING btree(object_id);` We should create an index for both the object type and object ID fields, as we've done for other GFKs, because both are needed to query a record by a related object. This is done under the model's Meta class: ```python class Meta: indexes = ( models.Index(fields=('object_type', 'object_id')), ) ``` Thanks for the report!

Sign in to join this conversation.

Branches Tags

main

update-changelog-comments-docs

feature-removal-issue-type

20911-dropdown

20239-plugin-menu-classes-mutable-state

21097-graphql-id-lookups

feature

fix_module_substitution

20923-dcim-templates

20044-elevation-stuck-lightmode

feature-ip-prefix-link

v4.5-beta1-release

20068-import-moduletype-attrs

20766-fix-german-translation-code-literals

20378-del-script

7604-filter-modifiers-v3

circuit-swap

12318-case-insensitive-uniqueness

20637-improve-device-q-filter

20660-script-load

19724-graphql

20614-update-ruff

14884-script

02496-max-page

19720-macaddress-interface-generic-relation

19408-circuit-terminations-export-templates

20203-openapi-check

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

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/netbox#9164