#5 Utilize PostgreSQL ip4r contrib module

[Eric A. Miller]

ip4r supports index lookups of the form (column >>= parameter), i.e. where you have a table of IP address ranges and wish to find which ones include a given IP address.

The PostgreSQL built-in types 'inet' and 'cidr' do not support index based lookups on ranges, as a result full table sequential scans are performed on many common Netdisco operations.

Use of this module should improve the database performance especially in large deployments.

This would require both installation of the module and modifications to the schema to use the new types and indices.

The module is available for Debian and Ubuntu: postgresql-9.x-ip4r however a quick scan did not indicate ip4r 2.x available as a RPM for CentOS / Redhat. 2.x needed for ipv4 and ipv6 support.

This may be something we consider as a fork or variant for large deployments that need the speed?

[Eric A. Miller]

Results of limited testing with Subnet Utilization Report reduced run-time from approximately 10 minutes down to 20 seconds on a database with 500K+ IP's and 6K+ subnets using a 0.0.0.0/0 query.

[Eric A. Miller]

Appears that RPM's for ip4r 2.01 are available at http://yum.postgresql.org

[Oliver Gorwits]

Other links I read whilst researching ip4r (nothing implied, I'm just bookmarking):

discussion about bringing ip4r into Pg core
range type mentioned in above link
range operators mentioned in above link

a post reporting the ip4r failing to build for Pg9.2
a post reporting successful port to Pg9.3?

[Eric A. Miller]

Adding some additional links of interest for the discussion:

Why so few built-in range types?

GiST support for inet datatypes

The second is link is a current discussion (3/3/2014). These links give the impression that index support will be non-core, so require the installation of an extension and that you cannot simply use CREATE TYPE to define an inet / cidr range type.

[Oliver Gorwits]

As a fun exercise, I had a go at using the range types. I don't know whether it makes any difference. NB this is not fully v6 compat (but could be made so). I also ditched the date comparison just to force all records to be included.

CREATE OR REPLACE FUNCTION inet_diff (inet, inet) RETURNS float8 AS $$
  BEGIN
    RETURN ($1 - $2);
  EXCEPTION
    WHEN numeric_value_out_of_range THEN
      -- TODO: fix when $1 < $2
      RETURN ((2^63) - 1);
  END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TYPE inetrange AS RANGE ( subtype = inet, subtype_diff = inet_diff );

ALTER TABLE subnets ADD COLUMN net_range inetrange;

CREATE INDEX subnets_net_range_idx ON subnets USING gist (net_range);

UPDATE subnets SET net_range = 
  (CASE WHEN family(net) = 4
  THEN
    inetrange(set_masklen(network(net),32), set_masklen(broadcast(net),32), '[]')
  ELSE
    inetrange(set_masklen(network(net),128), set_masklen(broadcast(net),128), '[]')
  END);

--- a/Netdisco/lib/App/Netdisco/DB/Result/Virtual/SubnetUtilization.pm
+++ b/Netdisco/lib/App/Netdisco/DB/Result/Virtual/SubnetUtilization.pm
@@ -18,23 +18,23 @@ __PACKAGE__->result_source_instance->view_definition(<<'ENDSQL');
     FROM (
       SELECT DISTINCT net, ni.ip
         FROM subnets s1, node_ip ni
-        WHERE s1.net <<= ?::cidr
-              AND ni.ip <<= s1.net
-              AND ni.time_last >= ?
-              AND ni.time_last <= ?
-              AND s1.last_discover >= ?
+        WHERE s1.net_range <@ inetrange(set_masklen(network(?::inet),32), set_masklen(broadcast(?::inet),32), '[]')
+              AND ni.ip::inet <@ s1.net_range
       UNION
       SELECT DISTINCT net, di.alias as ip
         FROM subnets s2, device_ip di JOIN device d USING (ip)
-        WHERE s2.net <<= ?::cidr
-              AND di.alias <<= s2.net
-              AND s2.last_discover >= ?
-              AND d.last_discover >= ?
+        WHERE s2.net_range <@ inetrange(set_masklen(network(?::inet),32), set_masklen(broadcast(?::inet),32), '[]')
+              AND di.alias <@ s2.net_range
     ) as joined
     GROUP BY net
     ORDER BY percent ASC
 ENDSQL

[Oliver Gorwits]

Oh, and the reason for the numeric_value_out_of_range check is that the minus op for inet returns bigint so only works for two inets within 2^63 of each other(!).

[Oliver Gorwits]

Ticket moved from /p/netdisco/netdisco2/93/