Improving the performance of regex match for private IP addresses in PostgreSQL

Question

I am using an MPP version of PostgreSQL forked off 8.3.

I am trying to optimize a select statement using a where clause to select only rows that have a private source IP address and a public destination IP address. I have two columns of type inet called source_ip and destination_ip. I feel like the following operation is not the most efficient way to do things because I am doing a regex match to determine when an IP is public or private:

where  (text(source_ip) like '10.%'  
    or text(source_ip) like '192.168.%'
    or text(source_ip) ~ E'^172\.((1[6-9])|(2[0-9])|(3[0-1]))\..+')
    and text(destination_ip) not like '10.%'  
    and text(destination_ip) not like '192.168.%'
    and text(destination_ip) !~ E'^172\.((1[6-9])|(2[0-9])|(3[0-1]))\..+';

How can I make the above where clause more efficient? Is there a way to not use regex and use builtin postgresql funcitons to operate on the inet type in a faster way?

Answer 1

where
    (
        inet '10/8' >> source_ip
        or inet '192.168/16' >> source_ip
        or sourceip >= inet '172.16/16' and sourceip < inet '172.32/16'
    )
    and not inet '10/8' >> destination_ip
    and not inet '192.168/16' >> destination_ip
    and not (destination_ip >= inet '172.16/16' and destination_ip < inet '172.32/16')