title: Are keys-only Datastore queries that return thousands of results unavoidably slow?
body: <p>I have an App Engine app using the <code>go115</code> runtime that interacts with Cloud Datastore via the <code>google.golang.org/appengine/v2/datastore</code> package. The app inserts and queries <code>Song</code> entities with automatically-assigned integer IDs and a <code>Rating</code> field (along with other fields):</p>
  <pre class="lang-golang prettyprint-override"><code>type Song struct {
      // ...
      Rating float64 `json:&quot;rating&quot;`
      // ...
  }
  </code></pre>
  <p>With 19,900 <code>Song</code> entities, <a href="https://console.cloud.google.com/datastore/stats" rel="nofollow noreferrer">https://console.cloud.google.com/datastore/stats</a> reports a data size of 15.53 MB, a built-in index size of 102.27 MB, and a composite index size of 86.31 MB. The app is running in <code>us-central</code> and Datastore is in <code>nam5 (us-central)</code>.</p>
  <p>The following keys-only query consistently takes 2-4 seconds to return 13,571 keys when run from App Engine:</p>
  <pre class="lang-golang prettyprint-override"><code>q := datastore.NewQuery(&quot;Song&quot;).KeysOnly().Filter(&quot;Rating &gt;=&quot;, 0.75)
  keys, err := q.GetAll(ctx, nil)
  </code></pre>
  <p>Using <code>Run</code> instead of <code>GetAll</code> and collecting the IDs while iterating seems to be a tiny bit faster. I also tried using the <code>cloud.google.com/go/datastore</code> package from my laptop just to make sure that the older <code>appengine</code> package isn't particularly slow, but the results were far worse (7-9 seconds).</p>
  <p>My understanding (based on things like the <a href="https://cloud.google.com/datastore/docs/concepts/indexes" rel="nofollow noreferrer">Indexes document</a> and <a href="https://www.youtube.com/watch?v=ofhEyDBpngM" rel="nofollow noreferrer">the I/O 2010 &quot;Next gen queries&quot; talk</a>) is that Datastore automatically maintains an index for the <code>Rating</code> field. I would expect it to be trivial to satisfy this query using that index. The integer IDs in the result set seem like they should consume about 100 KB of space.</p>
  <p>I haven't found any official guidance from Google about what type of performance to expect from Datastore, but this is far slower than I'd hoped for. When I perform a brute-force approximation of this query on my laptop by running the <a href="https://stedolan.github.io/jq/" rel="nofollow noreferrer">jq</a> tool and <code>grep</code> against a JSON dump of the data, it only takes 400 ms.</p>
  <p><strong>Is it expected that a trivial keys-only Datastore query will take multiple seconds to return thousands of keys?</strong></p>
  <p>When I run keys-only queries that return smaller result sets, the performance is better: queries that return 300-400 keys often take 100-200 ms, while queries that return fewer than 200 keys complete in 40-70 ms.</p>
  <p>I'm unable to modify many of my queries to limit the number of results they return:</p>
  <ul>
  <li><p>I sometimes perform multiple queries in parallel and then manually intersect or subtract their results to get around the Datastore limitation that <a href="https://cloud.google.com/datastore/docs/concepts/queries#inequality_filters_are_limited_to_at_most_one_property" rel="nofollow noreferrer">inequality filters are limited to at most one property</a>.</p>
  </li>
  <li><p>I sometimes get the full result set in order to return a random subset of it.  I've read about <a href="https://stackoverflow.com/questions/3002999/fetching-a-random-record-from-the-google-app-engine-datastore">approaches people take to simulate randomized results when  using  Datastore</a>,  but I don't think they'll work here since my queries already use inequality  filters.</p>
  </li>
  </ul>
  <p>I can think of hacks that may help in some cases (e.g. sharding/partitioning my entities so I can run multiple queries in parallel), but I'd prefer to avoid that if possible.</p>