title: Arangodb AQL Filter NOT IN collection, very slow
body: <p>I want to find the set of users not having a profile. </p>
  
  <pre><code>ArangoDB 2.4.3
  
  LENGTH(users) -&gt; 130k
  LENGTH(profiles) -&gt; 110k
  
  users.userId -&gt; unique hash index
  profiles.userId -&gt; unique hash index
  </code></pre>
  
  <p>This AQL snippet I made is slower than a snail crossing the Grand Canyon in mid-summer. </p>
  
  <pre><code>LET usersWithProfiles = ( /* This part is ok */
  FOR i IN users
      FOR j IN profiles
          FILTER i.userId == j.userId
  RETURN i
  )
  
  LET usersWithoutProfiles = ( /* This is not */
  FOR i IN usersWithProfiles
      FILTER i NOT IN users
  RETURN i
  )
  
  RETURN LENGTH(usersWithoutProfiles)
  </code></pre>
  
  <p>I'm pretty sure there is a perfectly sane way of doing it right, but I'm missing it. Any ideas?</p>
  
  <p><strong>Edit 1</strong> (After @<strong>dothebart</strong> 's response):</p>
  
  <p>This is the new query, but it is still very slow</p>
  
  <pre><code>LET userIds_usersWithProfile = (
  FOR i IN users
      FOR j IN profile
          FILTER i.userId == j.userId
  RETURN i.userId
  )
  
  LET usersWithoutProfiles = (
  FOR i IN users 
      FILTER i.userId NOT IN userIds_usersWithProfile
  RETURN i
  )
  
  RETURN LENGTH(usersWithoutProfiles)
  </code></pre>