IPv4 longest prefix match using Postgres

Question

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?

score 2 · Accepted Answer · edited Jun 11 '14 at 23:50

2

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.

edited Jun 11 '14 at 23:50

tartar

688
4
16

answered Jun 10 '14 at 12:11

Sander Steffann

9,509
35
40

1

Thanks, after edit, now it covers multiple equivalent matches. Only problem may be the order by should be descending for longest prefix match, is that right? – tartar Jun 10 '14 at 20:30
I made a small edit on removing a few syntax issues. I think this solution is close enough to make a lookup similar to what routers do while passing packets around. – tartar Jun 10 '14 at 21:14
Do you think we can eliminate order by statement by just getting the select max(masklen(next_hop_subnet::inet)) .....? I am just trying to improve the query time in a huge table (300K) – tartar Jun 10 '14 at 22:01
1

I'm not sure whether max or order by is faster in this case. Worth a try though. Indexing on the right columns will probably help most. – Sander Steffann Jun 10 '14 at 22:36

score 0 · Answer 2 · answered Jul 07 '23 at 09:31

0

why not this?

select * from routing_table where next_hop_subnet::inet >>= '192.168.0.1'::inet order by masklen(next_hop_subnet::inet) desc limit 1

answered Jul 07 '23 at 09:31

Alex Yang

1

IPv4 longest prefix match using Postgres

2 Answers2