postgresqlipv4longest-prefix

IPv4 longest prefix match using Postgres


Given an IP address 192.168.0.1, and a table with a column next_hop_subnet storing subnet IP addresses, do you see any problem with the following PostGRESQL logic, accuracy or performance-wise:

minDif := select min(abs(inet '192.168.0.1' -  next_hop_subnet::inet)) 
         from routing_table 
         where next_hop_subnet::inet >>= inet '192.168.0.1';

select * 
from routing_table 
where next_hop_subnet::inet >>= inet '192.168.0.1' 
      AND abs(inet '192.168.0.1' -  next_hop_subnet::inet) = minDif;

Since, there can be multiple equally good matches, I think there is no way but to do this in two steps. Any suggestions?


Solution

  • I would use the masklen(inet) function to order the answers, like:

    SELECT * FROM routing_table
     WHERE next_hop_subnet::inet >>= inet '192.168.0.1'
     AND masklen(next_hop_subnet::inet) = (
         SELECT masklen(next_hop_subnet::inet) FROM routing_table
         WHERE next_hop_subnet::inet >>= inet '192.168.0.1')
         ORDER BY masklen(next_hop_subnet::inet) DESC
         LIMIT 1
     );
    

    That way you get the longest matching prefix from your routing table.