# Why is IDs query slow?

**URL:** <https://discuss.elastic.co/t/why-is-ids-query-slow/29075>\
**Category:** Elasticsearch\
**Created:** [September 10, 2015, 9:57pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075 "2015-09-10T21:57:31Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 10, 2015, 9:57pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/1 "2015-09-10T21:57:31Z")

</div>

Have an index of ~ 9M docs, of which ~ 20K are "programs". This IDs query (with 20 IDs) takes 600ms on a local server, running on MBP with SSD. If I halve number of IDs, query latency cuts in half too. Same query takes 2000ms on an EC2 instance. Seems like this should be executed in \<\< 10ms locall, given that we're checking \_uid and the entire type has only 20K entries...

Should I care about unassigned\_shards (probably from deleted indices)?

```
GET /admin/programs/_search
    {
      "query": {
        "filtered": {
          "filter": {
            "ids": {
              "values": [
                 18536,
                  18537,
                  18538,
                  18539,
                  18552,
                  18553,
                  18554,
                  18555,
                  18556,
                  18557,
                  18558,
                  18559,
                  18560,
                  18561,
                  18562,
                  18563,
                  18564,
                  18565,
                  18652,
                  18653
              ]
            }
          }
        }
      }
    }

```

> GET /\_cluster/health  
> {  
> "cluster\_name": "ecr-elasticsearch-local",  
> "status": "yellow",  
> "timed\_out": false,  
> "number\_of\_nodes": 1,  
> "number\_of\_data\_nodes": 1,  
> "active\_primary\_shards": 75,  
> "active\_shards": 75,  
> "relocating\_shards": 0,  
> "initializing\_shards": 0,  
> "unassigned\_shards": 74  
> }

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [September 11, 2015, 5:46am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/2 "2015-09-11T05:46:11Z")

</div>

> Should I care about unassigned\_shards (probably from deleted indices)?

This is because you have configured the indexes to have one replica but you only have a single-node cluster so no replicas can actually be allocated. This should be fixed but it's not resulting in reduced performance or anything.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [September 11, 2015, 6:12am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/3 "2015-09-11T06:12:27Z")

</div>

Is that the initial run of the query or everyone?

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 11, 2015, 6:36am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/4 "2015-09-11T06:36:01Z")

</div>

Both cases

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [September 11, 2015, 7:15am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/5 "2015-09-11T07:15:00Z")

</div>

This should be pretty quick thanks to filter caching, are you getting/monitoring any cache evictions?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 11, 2015, 8:33am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/6 "2015-09-11T08:33:13Z")

</div>

More queried IDs=more matching hits to retrieve=more disk access

Can you provide the number of hits you retrieve in each case?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 11, 2015, 11:06am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/7 "2015-09-11T11:06:49Z")

</div>

Another idea to help separate the query vs retrieve costs:

try a match\_all query and get size 10 then size 20 hits and contrast response times with your ID based query for the same volumes. Should help isolate the costs of the ID query.

---

<div class="post-metadata">

**Author:** ![roytmana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/roytmana/32/44855_2.png) [@roytmana](https://discuss.elastic.co/u/roytmana)\
**Post date:** [September 11, 2015, 4:50pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/8 "2015-09-11T16:50:27Z")

</div>

I wonder what would be query performance if you try terms filter on these IDs

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 11, 2015, 5:47pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/9 "2015-09-11T17:47:26Z")

</div>

Thanks for all the suggestions thus far, folks. I think the issue lies in the cost of doing [source filtering](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-source-filtering.html) on matching docs - either via \_source= or fields= The challenge now is figuring out the best way to minimize that cost.

Let me step back:

- `/programs` has many properties, amongst them `id`, `name`, and `areas`. The latter is an array and is large - on the order of 40K entries for some docs
- I am interested in retrieving only id, name (and a few other small properties) of docs matching the IDs query. I achieve that via source filtering, ie `_source: ["id", "name"]`
- source filtering appears to be quite expensive and (AFAIK) does not benefit from filter caching - even if that were employed - since caches, as I understand, store references to whole docs as values (not post-source-filter contents, right?)
- **if I omit source (via `_source: false`) the query latency drops from 600ms to 4ms**

If I am right, I am surprised that source filtering is so expensive in this case, given that I am filtering by exact property names (as opposed to wildcards), and we're only filtering 20 docs. is the bulk of cost in constructing a key-value map from \_source on each query before applying source filtering?

Any way to lower cost of source filtering? Or do I need to create a separate type mapping - that contains only a subset of programs' properties relevant to this query?

Thanks,  
-nikita

---

<div class="post-metadata">

**Author:** ![roytmana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/roytmana/32/44855_2.png) [@roytmana](https://discuss.elastic.co/u/roytmana)\
**Post date:** [September 11, 2015, 5:50pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/10 "2015-09-11T17:50:15Z")

</div>

what if you declare few key fields you need as stored in mappings and then omit source but include those fields in your query. this should get it from the index rather than source

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 11, 2015, 5:55pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/11 "2015-09-11T17:55:43Z")

</div>

Sorry, Alex, not sure I follow. In \_mapping I have:

```
      "id": {
          "type": "long",
          "fields": {
             "str": {
                "type": "string",
                "index": "not_analyzed"
             }
          }
       }

```

and query contains `"_source": "id"`. What should I change and to what?

---

<div class="post-metadata">

**Author:** ![roytmana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/roytmana/32/44855_2.png) [@roytmana](https://discuss.elastic.co/u/roytmana)\
**Post date:** [September 11, 2015, 6:00pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/12 "2015-09-11T18:00:22Z")

</div>

take a look here [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-fields.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-fields.html)  
for stored fields it should be much faster than filtering of large source I think  
I am not clear if it is deprecated/removed for 2.x though

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 11, 2015, 6:13pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/13 "2015-09-11T18:13:55Z")

</div>

yes, I think you're right. two things I didn't realize:

- `fields` is not the same as source filtering via `_source` (rather falls back on latter when field in question is not stored separately)
- individual fields in a doc are [**not** stored (just indexed)](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-put-mapping.html) by default - need to amend property mapping to say `"store": true`

I will update my type mapping and retry the query. Thanks!

---

<div class="post-metadata">

**Author:** ![roytmana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/roytmana/32/44855_2.png) [@roytmana](https://discuss.elastic.co/u/roytmana)\
**Post date:** [September 11, 2015, 6:17pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/14 "2015-09-11T18:17:08Z")

</div>

no problem. unfortunately it will require reindexing if they are not stored

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 11, 2015, 6:44pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/15 "2015-09-11T18:44:53Z")

</div>

with fields `id`, and `name` mapped as stored (`store: true`) query latency is now 90ms vs 600ms before. So definitely better (and I think CPU load is predictably lower) but not as good as returning `_id` only:

1. `fields: []` - 4ms
2. `fields: [id,name]` - 90ms

...when retrieving 20 hits. #2 still seems a bit high. Anything else I can do to bring its' latency closer to no-fields scenario?

I did notice that latency of 2nd query:

- scales linearly with number of hits
- does not differ between id-only and id + name.

---

<div class="post-metadata">

**Author:** ![roytmana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/roytmana/32/44855_2.png) [@roytmana](https://discuss.elastic.co/u/roytmana)\
**Post date:** [September 11, 2015, 6:53pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/16 "2015-09-11T18:53:15Z")

</div>

have you reindexed your huge dataset that quick to test and positive that id and name are now stored fields an delastick is not falling back onto extracting it from \_source?

Not sure what else could be done but I am not an expert in this and rarely use fields:[]  
Empty fields:[] should return \_id and \_type and it runs 20 faster than [id, name] I assume just \_id would not be sufficient for your purpose?

also is it 90 ms every time or repeat query is faster?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 12, 2015, 8:22am UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/17 "2015-09-12T08:22:11Z")

</div>

The linear-with-hits behaviour comes down to disk access at the end of the day.

If you have the RAM available and can load the name and id field values into field data you can use Aggs to make this lookup an in-memory access. You'd need a root terms agg for the id and nest a terms agg for the associated name. Set size=0 on the query request to avoid a network round trip to return matching docs as all the info you need is in the Aggs tree

---

<div class="post-metadata">

**Author:** ![Nikita\_Tovstoles](https://avatars.discourse-cdn.com/v4/letter/n/f17d59/32.png) [@Nikita\_Tovstoles](https://discuss.elastic.co/u/Nikita_Tovstoles)\
**Post date:** [September 16, 2015, 2:23pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/18 "2015-09-16T14:23:18Z")

</div>

Actually, I ended up getting far better performance via excluding the huge `areas[]` from `_source` at index time (see [excluding fields from source](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-source-field.html#include-exclude)) and using source filtering in query. Latency of that query was 4ms (equal to that of no-source-query). Recall that `[storing individual fields][2]` (and using `fields` in query) yielded 90ms latency.

**I still would like to understand why I am paying a latency penalty when getting a stored field if that doc contains another field that is huge.**

ie given doc type with (stored) `name` and huge `areas[]`, a query with `fields: "name"` will take multiple times longer if `areas[]` is part of `_source` vs if it is not. Why is that? I was assuming that by querying for stored fields only, I am bypassing `_source` deser. and thus size/presence of `areas[]` is irrelevant... Would appreciate any feedback.

Thanks again, all, especially Alex and Mark.

-nikita

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 11:50pm UTC](https://discuss.elastic.co/t/why-is-ids-query-slow/29075/19 "2017-07-05T23:50:01Z")

</div>