title: postgresql cidr find child blocks
body: <p>I'm looking for a way to get direct children networks from cidr blocks stored in postgresql using the builtin cidr type.</p>
  
  <p>Example database</p>
  
  <pre><code>CREATE TABLE nets (
      id serial primary key,
      net cidr
  );
  INSERT INTO nets (net) VALUES 
      ('10.1.0.0/16'),
      ('10.1.0.0/20'),
      ('10.1.1.0/24'),
      ('10.1.1.8/29'),
      ('10.1.1.32/28'),
      ('10.2.15.0/24'),
      ('10.2.15.64/27')
  </code></pre>
  
  <p>The desired query should f.e. </p>
  
  <ul>
  <li>return 10.1.0.0/20 for searchkey 10.1.0.0/16</li>
  <li>10.1.1.8/29 and 10.1.1.32/28 for searchkey 10.1.1.0/24</li>
  </ul>
  
  <p>What I came up with is (<a href="http://sqlfiddle.com/#!15/2b4b5/1" rel="nofollow">http://sqlfiddle.com/#!15/2b4b5/1</a>):</p>
  
  <pre><code>SELECT
    id,
    net
  FROM 
    nets n
  WHERE 
    net &lt;&lt; '10.1.1.0/24' AND
    '10.1.1.0/24' IN (
      SELECT 
        net 
      FROM
        nets
      WHERE
        net &gt;&gt; n.net
      ORDER BY
        net DESC
      LIMIT 1
    )
  ORDER BY 
    net
  </code></pre>
  
  <p>This gives the desired result, but it does not scale. Even with only a few thousand entries in the database this gets terribly slow.</p>
  
  <p>Is there another way to achieve this, without adding an explicit parent/child relationship to the database model?</p>