#5 Utilize PostgreSQL ip4r contrib module


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

    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 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 $$
        RETURN ($1 - $2);
        WHEN numeric_value_out_of_range THEN
          -- TODO: fix when $1 < $2
          RETURN ((2^63) - 1);
    $$ 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
        inetrange(set_masklen(network(net),32), set_masklen(broadcast(net),32), '[]')
        inetrange(set_masklen(network(net),128), set_masklen(broadcast(net),128), '[]')
    --- 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
           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
    • 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/

  • Oliver Gorwits

    Oliver Gorwits - 2017-02-02
    • Status: new --> moved-to-github

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks