title: Joining Postgres tables on CIDR
body: <p>I have two tables. The first table (<code>events</code>) looks like this:</p>
  
  <pre><code>id int8
  timestamp datetime
  ip cidr
  </code></pre>
  
  <p>I have a second table (<code>networks</code>) that looks like this:</p>
  
  <pre><code>id int8
  network cidr
  name text
  </code></pre>
  
  <p>In the first table, <code>ip</code> is an IP address (e.g., the IP address of an incoming network connection in a log file). In the second table, <code>network</code> represents ranges of IP addresses (e.g., blocks of IPs for an ISP).</p>
  
  <p>I want to join the tables such that I can retrieve <code>networks.name</code> for each row in <code>events</code>. My SQL looks like this:</p>
  
  <pre><code>select
    e.ip
    n.name
  from
    events e
    inner join networks n on e.ip &lt;&lt;= n.network
  limit 1000
  </code></pre>
  
  <p>This, however, is extremely slow (>1s per returned row). I have a unique BTREE index defined on <code>networks.network</code>, but that seems to make no difference. Both tables contain >10M records. I'm (unfortunately) running Postgres 9.6.12.</p>
  
  <p>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 <code>JOIN</code>, but it's unclear how to get Postgres to use the index here. Is it possible?</p>