title: Improve SQLite anti-join performance
body: <p><strong>Check out the update at the bottom of this question, the cause of the unexpected variance in query times noted below has been identified as the result of a sqliteman quirk</strong></p>
  
  <p>I have the following two tables in a SQLite DB (The structure might seem pointless I know but bear with me)</p>
  
  <pre><code>+-----------------------+
  | source                |
  +-----------------------+
  | item_id | time | data |
  +-----------------------+
  
  +----------------+
  | target         |
  +----------------+
  | item_id | time |
  +----------------+
  
  --Both tables have a multi column index on item_id and time
  </code></pre>
  
  <p>The source table contains around 500,000 rows, there will never more than one matching record in the target table, in practise it is likely almost all source rows will have a matching target row.</p>
  
  <p>I am attempting to a perform a fairly standard anti-join to find all records in source without corresponding rows in target, but am finding it difficult to create a query with an acceptable execution time.</p>
  
  <p>The query I am using is:</p>
  
  <pre><code>SELECT
      source.item_id,
      source.time,
      source.data
  FROM source
  LEFT JOIN target USING (item_id, time)
  WHERE target.item_id IS NULL;
  </code></pre>
  
  <p>Just the LEFT JOIN without the WHERE clause takes around 200ms to complete, with it this increases to 5000ms.</p>
  
  <p>While I originally noticed the slow query from within my consuming application the timings above were obtained by executing the statements directly from within sqliteman.</p>
  
  <p>Is there a particular reason why this seemingly simple clause so dramatically increases execution time and is there some way I can restructure this query to improve it?</p>
  
  <p>I have also tried the following with the same result. (I imagine the underlying query plan is the same)</p>
  
  <pre><code>SELECT 
      source.item_id,
      source.time,
      source.data
  FROM source
  WHERE NOT EXISTS (
      SELECT 1 FROM target
      WHERE target.item_id = source.item_id
      AND target.time = source.time
  );
  </code></pre>
  
  <p>Thanks very much!</p>
  
  <h2>Update</h2>
  
  <p>Terribly sorry, it turns out that these apparent results are actually due to a quirk with sqliteman.</p>
  
  <p>It seems sqliteman arbitrarily applies a limit to the number of rows returned to 256, and will load more dynamically as you scroll through them. This will make a query over a large dataset appear much quicker then actually is, making it a poor choice for estimating query performance.</p>
  
  <p>Nonetheless is their any obvious way to improve the performance of this query or am I simply hitting limits of what SQLite is capable of?</p>