Joining Postgres tables on CIDR

Question

I have two tables. The first table (events) looks like this:

id int8
timestamp datetime
ip cidr

I have a second table (networks) that looks like this:

id int8
network cidr
name text

In the first table, ip is an IP address (e.g., the IP address of an incoming network connection in a log file). In the second table, network represents ranges of IP addresses (e.g., blocks of IPs for an ISP).

I want to join the tables such that I can retrieve networks.name for each row in events. My SQL looks like this:

select
  e.ip
  n.name
from
  events e
  inner join networks n on e.ip <<= n.network
limit 1000

This, however, is extremely slow (>1s per returned row). I have a unique BTREE index defined on networks.network, but that seems to make no difference. Both tables contain >10M records. I'm (unfortunately) running Postgres 9.6.12.

Is there an efficient way to compute this? It would seem to me that the bottleneck is on the "is contained within or equals" operation on the JOIN, but it's unclear how to get Postgres to use the index here. Is it possible?

Answer 1

For INET operations, you can define a GIST inet_ops index:

CREATE INDEX ON networks USING GIST (network inet_ops);

Docs on 9.6, but it's applicable to any later versions of postgres.