#5 Utilize PostgreSQL ip4r contrib module

None
new
nobody
None
2014-08-02
2014-02-24
Eric A. Miller
No

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?

Discussion

  • Eric A. Miller
    Eric A. Miller
    2014-02-25

    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
    Eric A. Miller
    2014-02-25

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

     
  • Eric A. Miller
    Eric A. Miller
    2014-03-05

    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
    Oliver Gorwits
    2014-04-09

    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
      Oliver Gorwits
      2014-04-09

      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
    Oliver Gorwits
    2014-08-02

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