Notes on optimizing IPAddress database queries

[tyler-8]

Overview

I started digging into query performance for IPAddress after a DBA reached out with some concerns. I was going to open an issue but 1) there's not a great solution right now, 2) the only idea I have for resolving it isn't worth the effort without more use cases or a larger impact. So these are just my notes in case the topic is ever re-visited in the future.

[Edit] Better solution proposed #11503 (reply in thread)

• Add an index to the address field on the IPAddress model to reduce lookup/search cost to the database.

• Remove CAST(HOST()) usage as it increases lookup cost quite a bit, even when indexing address.

  netbox/netbox/ipam/lookups.py

  Line 154 in d7c37d9

  return 'CAST(HOST(%s) AS INET) <<= %s' % (lhs, rhs), params

However, CAST(HOST()) can't be removed without affecting this scenario #946 (comment) which definitely has value.

Test results

I tested both gist (address inet_ops) and btree and saw a similar reduction in lookup cost and time - but removing CAST(HOST()) had the biggest impact, indexing makes it even better. With approximately 260K IP addresses, the index only increased the disk size of the table by 4-7 MB (from ~47MB to ~51MB) depending on index type.

Removing CAST(HOST()) doesn't impact this scenario #6632, and all the "nested" prefix/IP layouts all seemed to work fine since Postgres understands IP/mask nested relationships (except as mentioned in #946 (comment))

/32 prefix with /32 IP

/25 prefix with /26 child prefix, which contains a /26 IP

Possible workarounds

As for my workaround idea - one or two "private" fields could be added to the IPAddress model to store the IP address without the mask (and another with just the mask optionally). Those fields would be modified automatically during .save() or similar method that's called anytime a model is modified.

Better workaround

#11503 (reply in thread)

[danner26]

Hello, I wanted to bring up a concern about the proposed workaround. It appears that the proposed workaround would lead to a lot of duplicated data, which is not ideal for managing a efficient database. While currently there may be a small impact on performance, I don't believe that duplicating data is the right approach.

I am hopeful that the community can find a more elegant solution that accomplishes more efficient queries while maintaining the functionality that currently addresses issue #946. Whenever I have some time to think this over, I will try to recall some of my past DBA experience to can come up with a possible solution.

[candlerb]

My point was that a Postgres generated column - or an index on an expression - amounts to much the same thing.

Personally I think IP address objects should not store masks at all: an IP address is just a single address, and the prefix length is an attribute of the enclosing subnet, not the address itself. There certainly is massive data duplication when, for example, a /26 subnet has 60 IP addresses all of which say the prefix length is "/26" (not to mention great scope for data inconsistency). But my opinion is not shared by the Netbox developers, so you can ignore it.

[kkthxbye-code]

Ultimately the proper way to store this data, in my opinion, is to have two completely separate columns, one for the ip address and another for the mask.

All the inet type functions would not work without casting and the overall performance would probably be way worse for a lot of queries.

As an idea, that I have not tested at all, in postgres there is a function called split_part

Calling any function would prevent using an index and require a full table scan. We would need a functional index for any function we needed to call.

All these ideas all share the common issue of increasing complexity to solve a non-problem. To be clear the performance of the query discussed here was 70ms for searching over 200k ips. It doesn't hurt to evaluate options, but my personal opinion is that it is very very unlikely that we will consider increasing complexity unless someone can demonstrate actual problematic query times.

[tyler-8]

As I mentioned in Slack, my DB knowledge only goes so deep, so I wasn't aware of functional indexes until they were mentioned here and I discussed more with a DBA peer who highly recommended them for cases like this. So I went exploring and tested with this

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

And it's... really great!

Query time for this query on ~260K IPs is <=2ms (vs ~70ms) and only increased table disk size by 5MB.

SELECT "ipam_ipaddress"."id",
  "ipam_ipaddress"."created",
  "ipam_ipaddress"."last_updated",
  "ipam_ipaddress"."custom_field_data",
  "ipam_ipaddress"."address",
  "ipam_ipaddress"."vrf_id",
  "ipam_ipaddress"."tenant_id",
  "ipam_ipaddress"."status",
  "ipam_ipaddress"."role",
  "ipam_ipaddress"."assigned_object_type_id",
  "ipam_ipaddress"."assigned_object_id",
  "ipam_ipaddress"."nat_inside_id",
  "ipam_ipaddress"."dns_name",
  "ipam_ipaddress"."description"
FROM "ipam_ipaddress"
WHERE (
    CAST(HOST("ipam_ipaddress"."address") AS INET) <<= '192.0.2.0/24'
    AND "ipam_ipaddress"."vrf_id" IS NULL
  )
ORDER BY INET(HOST("ipam_ipaddress"."address")) asc
;

But more importantly - the query cost is about 1/3 the original (2264 vs 6632), and is doing an index scan, rather than sequential scan across the entire table - all of which has benefits on the disk & CPU utilization - neither of which is well reflected in query analysis, especially query times. The time may be fast but incurring a larger than average CPU/disk impact - which could be important depending on the database environment - and something my DBA peer has observed on their end. The one ipaddress query uses 2 postgres worker processes due to the sequential scan (as a result of the two functions and no index) and if run on a regular basis (which it is in our deployment), easily tops the query list for CPU utilization.

If we were to fit this into a Django migration it would look something like:

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

Given this option, and the relatively low-impact to the DB size and code impact (almost none other than the migration), I think this is worthy of an FR. I realize only certain NetBox deployments will see the benefits of this, or even be concerned by the "current" numbers, but again - implementation seems quite simple and has benefits for high volume/row count environments.

The piece that would need more data for would be impact on insertion speed as any index, functional ones especially, will add a small overhead.

[tyler-8]

I tested creating 460 IP addresses via the REST API (with pynetbox) with and without an index, and the average time for both was almost the same (0.0185s without index, 0.0166s with index) so it would seem there's no significant impact to insertion time (a minuscule improvement even).

[danner26]

Looks like that works really well, I like it.