starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

Add functional index to ipam.ip_address model/table #7526

New Issue
Closed
opened 2025-12-29 20:24:45 +01:00 by adam · 4 comments
adam commented 2025-12-29 20:24:45 +01:00
Owner
Copy Link

Originally created by @tyler-8 on GitHub (Jan 16, 2023).

Originally assigned to: @jeremystretch on GitHub.

NetBox version

v3.4.2

Feature type

Change to existing functionality

Proposed functionality

Create a functional index on the ipam.ip_address table. Which indexes the output of CAST(HOST(ipam.ipaddress)) so it isn't called on every execution of the query - which can be computationally expensive.

Use case

I've detailed my findings in this discussion thread.

Queries/searches for IPAddress filtered by parent Prefixes can be CPU intensive to the database and, depending on the parent prefix and IP addresses present, can be very inefficient when searching through hundreds of thousands of IPs (~260K) and only returning a few. Query time itself isn't terrible, but there is a noticeable uptick in CPU & DB worker threads/processes generated by this type of query compared to others - and that can be of concern to larger/high-volume instances.

The culprit is mostly with calling CAST(HOST("ipam_ipaddress"."address") AS INET) d7c37d9dd6/netbox/ipam/lookups.py (L154) and the resulting sequential scans it must perform. Indexing address with btree or inet_ops makes little difference in performance and query cost.

But the above functional index is 1/3 the query cost (2264 vs 6632) and the query time is <=2ms (vs ~70ms). I observed a ~5MB increase (+8%) in table disk size (260K IPs) with this index, and new data insertion time seemed un-impacted in any noticeable way (Using the REST API: (0.0185s without index, 0.0166s with index)).

Additional metrics

Querying the /ipam/ip-addresses/ endpoint ~2K times with different prefixes as parent and 3 separate client processes:

  • No index: 6m18s
  • With index: 4m37s

Database changes

New index on address field.

CREATE INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress (cast(host(address) as inet));

One possible way is in a manually-defined migrations file:

operations = [
        migrations.RunSQL(
            sql="CREATE INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress (cast(host(address) as inet))",
            reverse_sql='DROP INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress'
        )
    ]

or it might be possible on the model itself via defining an index expression.

External dependencies

N/A

And thanks & credit to @candlerb & @kkthxbye-code for setting me down the path

Originally created by @tyler-8 on GitHub (Jan 16, 2023). Originally assigned to: @jeremystretch on GitHub. ### NetBox version v3.4.2 ### Feature type Change to existing functionality ### Proposed functionality Create a [functional index](https://www.postgresql.org/docs/current/indexes-expressional.html) on the `ipam.ip_address` table. Which indexes the output of `CAST(HOST(ipam.ipaddress))` so it isn't called on every execution of the query - which can be computationally expensive. ### Use case I've detailed my findings in this [discussion thread](https://github.com/netbox-community/netbox/discussions/11503). Queries/searches for `IPAddress` filtered by `parent` Prefixes can be CPU intensive to the database and, depending on the parent prefix and IP addresses present, can be very inefficient when searching through hundreds of thousands of IPs (~260K) and only returning a few. Query time itself isn't terrible, but there is a noticeable uptick in CPU & DB worker threads/processes generated by this type of query compared to others - and that can be of concern to larger/high-volume instances. The culprit is mostly with calling `CAST(HOST("ipam_ipaddress"."address") AS INET)` https://github.com/netbox-community/netbox/blob/d7c37d9dd6556654dcfa9ec940c3f1af9d4d3c6e/netbox/ipam/lookups.py#L154 and the resulting sequential scans it must perform. Indexing `address` with `btree` or `inet_ops` makes little difference in performance and query cost. But the above functional index is 1/3 the query cost (2264 vs 6632) and the query time is `<=2ms` (vs `~70ms`). I observed a ~5MB increase (+8%) in table disk size (260K IPs) with this index, and new data insertion time seemed un-impacted in any noticeable way (Using the REST API: `(0.0185s without index, 0.0166s with index)`). #### Additional metrics Querying the `/ipam/ip-addresses/` endpoint ~2K times with different prefixes as `parent` and 3 separate client processes: - No index: `6m18s` - With index: `4m37s` ### Database changes New index on `address` field. ```sql CREATE INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress (cast(host(address) as inet)); ``` One possible way is in a manually-defined migrations file: ```python operations = [ migrations.RunSQL( sql="CREATE INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress (cast(host(address) as inet))", reverse_sql='DROP INDEX ipam_ipaddress_address_host_idx ON ipam_ipaddress' ) ] ``` or it might be possible on the model itself via defining an index [expression](https://docs.djangoproject.com/en/4.1/ref/models/indexes/#expressions). ### External dependencies N/A And thanks & credit to @candlerb & @kkthxbye-code for setting me down the path
adam added the status: acceptedtype: feature labels 2025-12-29 20:24:45 +01:00
adam closed this issue 2025-12-29 20:24:45 +01:00
adam commented 2025-12-29 20:24:46 +01:00
Author
Owner
Copy Link

@tyler-8 commented on GitHub (Jan 17, 2023):

Here are the CPU stats of the above 2K API queries against /ipam/ip-addresses/

Output of ps -eo user,pid,pcpu,cmd --sort=-pcpu | head -n 25 on the database server.

with index

user         pid      cpu    command
postgres     <pid>    2.0    postgres: <...> idle
postgres     <pid>    1.8    postgres: <...> idle
postgres     <pid>    1.5    postgres: <...> idle
postgres     <pid>    1.4    postgres: <...> idle
postgres     <pid>    1.3    postgres: <...> idle
postgres     <pid>    0.9    postgres: <...> idle
postgres     <pid>    0.9    postgres: <...> idle
postgres     <pid>    0.9    postgres: <...> idle

without index

user         pid      cpu     command
postgres     <pid>    18.1    postgres:    <...>    idle
postgres     <pid>    16.4    postgres:    <...>    SELECT
postgres     <pid>    14.1    postgres:    <...>    idle
postgres     <pid>    14.0    postgres:    <...>    idle
postgres     <pid>    12.5    postgres:    <...>    idle
postgres     <pid>    11.5    postgres:    <...>    idle
postgres     <pid>     9.4    postgres:    <...>    idle
postgres     <pid>     5.7    postgres:    <...>    idle

and of course this is just while the test activity is going on in a test environment, not accounting for other workflows and uses happening simultaneously in a production environment. This is just to show the elevated CPU impact.

@tyler-8 commented on GitHub (Jan 17, 2023): Here are the CPU stats of the above 2K API queries against `/ipam/ip-addresses/` Output of `ps -eo user,pid,pcpu,cmd --sort=-pcpu | head -n 25` on the database server. ### with index ``` user pid cpu command postgres <pid> 2.0 postgres: <...> idle postgres <pid> 1.8 postgres: <...> idle postgres <pid> 1.5 postgres: <...> idle postgres <pid> 1.4 postgres: <...> idle postgres <pid> 1.3 postgres: <...> idle postgres <pid> 0.9 postgres: <...> idle postgres <pid> 0.9 postgres: <...> idle postgres <pid> 0.9 postgres: <...> idle ``` ### without index ``` user pid cpu command postgres <pid> 18.1 postgres: <...> idle postgres <pid> 16.4 postgres: <...> SELECT postgres <pid> 14.1 postgres: <...> idle postgres <pid> 14.0 postgres: <...> idle postgres <pid> 12.5 postgres: <...> idle postgres <pid> 11.5 postgres: <...> idle postgres <pid> 9.4 postgres: <...> idle postgres <pid> 5.7 postgres: <...> idle ``` and of course this is just while the test activity is going on in a test environment, not accounting for other workflows and uses happening simultaneously in a production environment. This is just to show the elevated CPU impact.
adam commented 2025-12-29 20:24:46 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Feb 18, 2023):

Does adding the index require modifying the NetHostContained lookup? Or will PostgreSQL automatically employ the index as-is?

@jeremystretch commented on GitHub (Feb 18, 2023): Does adding the index require modifying the `NetHostContained` lookup? Or will PostgreSQL automatically employ the index as-is?
adam commented 2025-12-29 20:24:47 +01:00
Author
Owner
Copy Link

@tyler-8 commented on GitHub (Feb 18, 2023):

Does adding the index require modifying the NetHostContained lookup? Or will PostgreSQL automatically employ the index as-is?

In my testing, adding the index (and the subsequent performance benefits) was invisible to NetBox and didn't require any code changes (beyond creating the index itself).

@tyler-8 commented on GitHub (Feb 18, 2023): > Does adding the index require modifying the `NetHostContained` lookup? Or will PostgreSQL automatically employ the index as-is? In my testing, adding the index (and the subsequent performance benefits) was invisible to NetBox and didn't require any code changes (beyond creating the index itself).
adam commented 2025-12-29 20:24:47 +01:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Aug 2, 2023):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

@github-actions[bot] commented on GitHub (Aug 2, 2023): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. **Do not** attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our [contributing guide](https://github.com/netbox-community/netbox/blob/develop/CONTRIBUTING.md).
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label status: accepted type: feature
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#7526
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?