title: PostgreSQL: proper way to delete 'orphaned' records
body: <p>In my current project, a have a few cases where, within data pump operations, I have to execute queries like this (it's not a real example, but it should give you some idea):</p>
  <pre><code>DELETE FROM notification
  WHERE user_id NOT IN (SELECT id FROM user)
  </code></pre>
  <p>On big tables, such construction performs poorly, I believe it's because of <code>NOT IN</code> construction, which makes it impossible to use indexes.</p>
  <p>Such approach should perform better:</p>
  <pre><code>DELETE FROM notification
  USING (
      SELECT n.user_id, u.id FROM notification
      LEFT OUTER JOIN user u ON n.user_id = u.id
  ) i
  WHERE 
          notification.user_id = i.user_id 
      AND i.id IS NULL
  </code></pre>
  <p>... but it looks a bit overcomplicated.</p>
  <p>Is there a better way / best practice for such operations?</p>