title: Core Data SQLite query exponentially slower when subquery predicate contains only one entity
body: <p>I am using a subquery predicate to perform a fetch on a core data managed object context to fetch entities to put on a map. The entities are filtered by attributes on a related entity, so a subquery is used. The predicate on the subquery varies depending on the user selected filter criteria. I'm finding that one of the simplest of these subquery predicates is causing the fetch to take exponentially longer than it should.</p>
  
  <p>My simplified object graph looks like this:</p>
  
  <pre><code>Entity  &lt;--&gt;&gt;  RelatedEntity  &lt;&lt;--&gt;  FilterRelationship
  -latitude
  -longitude
  </code></pre>
  
  <p>The predicate on the <code>NSFetchRequest</code> looks like this:</p>
  
  <p><code>(latitude &gt; #.# AND latitude &lt; #.# AND longitude &gt; #.# AND longitude &lt; #.#) AND SUBQUERY(relatedEntities, $relatedEntity, $relatedEntity.filterRelationship IN { JUST_ONE_HERE }).@count &gt; 0</code></p>
  
  <p><code>JUST_ONE_HERE</code> represents when there is only a single entity in the set in this portion of the subquery predicate, this is where the query takes exponentially longer. If I have more than one entity in this set, the query executes normally.</p>
  
  <p>Using the launch argument <code>-com.apple.CoreData.SQLDebug 1</code>, I logged the following:</p>
  
  <blockquote>
    <p>CoreData: sql: SELECT  t0.Z_ENT, t0.Z_PK, t0.Z_OPT, ... FROM ZENTITY t0 WHERE (( t0.ZLATITUDE > ? AND  t0.ZLATITUDE &lt; ? AND  t0.ZLONGITUDE > ? AND  t0.ZLONGITUDE &lt; ?) AND (SELECT COUNT(t1.Z_PK) FROM ZRELATEDENTITY t1 WHERE (t0.Z_PK = t1.ZENTITY AND ( t1.ZFILTERRELATIONSHIP IN  <strong>(?)</strong> )) ) > ?) ORDER BY ... LIMIT 50</p>
    
    <p>CoreData: annotation: sql connection fetch time: 903.6430s</p>
    
    <p>CoreData: annotation: total fetch execution time: 903.6458s for 50
    rows.</p>
  </blockquote>
  
  <p>and</p>
  
  <blockquote>
    <p>CoreData: sql: SELECT  t0.Z_ENT, t0.Z_PK, t0.Z_OPT, ... FROM ZENTITY t0 WHERE (( t0.ZLATITUDE > ? AND  t0.ZLATITUDE &lt; ? AND  t0.ZLONGITUDE > ? AND  t0.ZLONGITUDE &lt; ?) AND (SELECT COUNT(t1.Z_PK) FROM ZRELATEDENTITY t1 WHERE (t0.Z_PK = t1.ZENTITY AND ( t1.ZFILTERRELATIONSHIP IN  <strong>(?,?)</strong> )) ) > ?) ORDER BY ... LIMIT 50</p>
    
    <p>CoreData: annotation: sql connection fetch time: 1.7181s</p>
    
    <p>CoreData: annotation: total fetch execution time: 1.7216s for 50 rows.</p>
  </blockquote>
  
  <p>I removed the unimportant parts with <code>...</code> and renamed the entities to be clear what they are. But as you can see, the query is fetching entities of type <code>Entity</code> based on their longitude and latitude on the map as well as a subquery based on a related entity <code>RelatedEntity</code> and a predicate matching a relationship on these related entities to a specified <code>FilterRelationship</code> entity.</p>
  
  <p>In the simple case of only having one <code>FilterRelationship</code> entity in the set in the subquery predicate, the fetch takes ~15 minutes.</p>
  
  <p>The only difference between the two above queries is the second one has two <code>FilterRelationship</code> entities in the set in the subquery predicate. The second example completed in under 2 seconds.</p>
  
  <p>The database contains ~22,000 <code>Entity</code>, ~5,000 <code>RelatedEntity</code>, and 3 <code>FilterRelationship</code> entities.</p>
  
  <p>I also tried this same predicate except for using an equality predicate in place of the <code>IN</code> a set of one object.</p>
  
  <p><code>(latitude &gt; #.# AND latitude &lt; #.# AND longitude &gt; #.# AND longitude &lt; #.#) AND SUBQUERY(relatedEntities, $relatedEntity, $relatedEntity.filterRelationship == JUST_ONE_HERE).@count &gt; 0</code></p>
  
  <p>This predicate had the same slow fetch result.</p>
  
  <p>The smaller the set of <code>Entity</code>s that are searched, the faster the subquery predicate fetch executes. So by zooming in on a small area of the map and having the latitude/longitude predicate filter out the <code>Entity</code>s being searched, the fetch executes faster, despite the slowness of a single <code>FilterRelationship</code>. The ~15 minute numbers were measure when zoomed all the way out on the map, filtering on all the <code>Entity</code>s in the database.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I updated the object graph to reflect that the first relationship, <code>Entity</code> to <code>RelatedEntity</code> is a one to many relationship. The second relationship, <code>RelatedEntity</code> to <code>FilterRelationship</code> is a many to one relationship.</p>