title: Index to find records where the foreign key does not exist
body: <pre><code>table products
  id primary_key
  
  table transactions
  product_id foreign_key references products
  </code></pre>
  
  <p>The below SQL query is very slow:</p>
  
  <pre><code>SELECT products.* 
  FROM   products 
         LEFT JOIN transactions 
                ON ( products.id = transactions.product_id ) 
  WHERE  transactions.product_id IS NULL; 
  </code></pre>
  
  <p>Out of 100 hundred million products records, there might be only 100 records where a product has no corresponding transactions.</p>
  
  <p>This query is very slow as I suspect it is doing a full table scan to find those null foreign key product records.</p>
  
  <p>I want to create a partial index like this:</p>
  
  <pre><code>CREATE INDEX products_with_no_transactions_index 
  ON (Left JOIN TABLE 
      BETWEEN products AND transactions) 
  WHERE transactions.product_id IS NULL;
  </code></pre>
  
  <p>Is the above possible and how would I go about it?</p>
  
  <p>Note: 
  Some characteristics of this data set:</p>
  
  <ol>
  <li><p>Transactions are never deleted and only added. </p></li>
  <li><p>Products are never deleted but added at a rate of 100s per minute (obviously this is a made up example behind a much more complex actual use case). A small perchange of those are temporarily orphaned</p></li>
  <li><p>I need to frequently query (up to once per minute) and need to always know what the current set of orphaned products are</p></li>
  </ol>