# Count query is returning inaccurate results

**URL:** <https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436>\
**Category:** Elasticsearch\
**Created:** [May 30, 2021, 3:33pm UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436 "2021-05-30T15:33:00Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 30, 2021, 3:33pm UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/1 "2021-05-30T15:33:00Z")

</div>

Count query (filter term) is returning inaccurate results - `*/_count`

We have tried to get the count using a regular search query using this approach - `"size":0,"track_total_hits":true` and the count is still inaccurate.

Basically, the counts are the same in both the above queries.

In both the above cases, this is the **\_shards** response -  
`"_shards":{"total":1,"successful":1,"skipped":0,"failed":0}`

We are running v7.13 on the Elastic Cloud. Our index is about ~35m documents. We are running a Staging & Production server on the Cloud (with the same data) and the counts are invalid on both the servers.

We don't do any real-time continuous updates to the index - no updates, no deletes. We add all the ~35m documents via bulk updates using background jobs initially, and no more updates after that.

How do we know the counts are inaccurate - well we were so confused by the inaccuracies, we looped through each document using scroll API to run a loop and do a manual count, and indeed our index/data looks ok, but Elasticsearch, unfortunately, is returning the wrong counts.

How do we solve this issue?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2021, 7:09am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/2 "2021-05-31T07:09:55Z")

</div>

What does the output of the [cat indices API](https://www.elastic.co/guide/en/elasticsearch/reference/7.13/cat-indices.html) look like? Have you refreshed after you finished bulk loading, especially if you optimized by changing the refresh interval during loading? What does the full queries and results look like?

What do you get if you run `GET /<PUT INDEX PATTERN HERE>/_count?q=*:*` ?

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 31, 2021, 8:04am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/3 "2021-05-31T08:04:56Z")

</div>

@Christian_Dahlqvist Thanks for your response.

- We did not change or optimize the refresh interval during loading

- Just ran `POST /_refresh` and it came back under a second with the following response (but the counts did not change)

- Following is the output of cat indices  
`yellow open products_20210523141120879 Y76XjK2OTbeE4i_8EI6o6g 1 1 37142644 49 59.4gb 59.4gb`

- Following is the response of the `/_count?q=*:*` ?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2021, 8:08am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/4 "2021-05-31T08:08:31Z")

</div>

> [@kapso](#):
>
> `"count": 37142644`

> [@kapso](#):
>
> yellow open products\_20210523141120879 Y76XjK2OTbeE4i\_8EI6o6g 1 1 37142644 49 59.4gb 59.4gb

This is the number of documents the index contains. Are you expecting a different number?

If you are expecting a different number, how did you index the data? If you used bulk requests, did you check that all writes were successful?

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 31, 2021, 8:10am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/5 "2021-05-31T08:10:27Z")

</div>

@Christian_Dahlqvist that total count `37142644` for the index looks about ok.

Also, we have retries built in our background jobs, so I dont think we run into any problems when doing bulk writes.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2021, 8:11am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/6 "2021-05-31T08:11:13Z")

</div>

What is then giving inaccurate results?

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 31, 2021, 8:12am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/7 "2021-05-31T08:12:57Z")

</div>

The inaccuracies are when we run a filter query like this

`{"query":{"bool":{"filter":[{"terms":{"level_2_classification_ids":[261243]}}]}},"size":0,"track_total_hits":true}`

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2021, 8:15am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/8 "2021-05-31T08:15:15Z")

</div>

What result do you get and what are you expecting? What is the mapping for the field you are searching on?

If you are looking for the count of documents that match a filter criteria I would recommend to use an aggregation.

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 31, 2021, 8:20am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/9 "2021-05-31T08:20:42Z")

</div>

- What we get is `199,421` and what we are expecting is `202,164`, and some of the filter queries on other ids are even way off

- This is the mapping for that field...

```auto
    "level_2_classification_ids": {
              "type": "long"
            },

```

- We are using v7.13 (its the latest & greatest)

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2021, 8:29am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/10 "2021-05-31T08:29:02Z")

</div>

I vaguely recall there was some optimization introduced recently that improved performance by cutting the query short, which could result in inaccurate counts. Haven't found the blog post though yet, so could very well be mistaken. I would recommend trying an aggregation with a filter and see if that gives accurate results.

I wonder if the `track_total_hits` parameter mentioned [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.13/search-search.html#search-search-api-query-params) makes a difference. Can yoiu try setting this to `true`?

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [May 31, 2021, 8:42am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/11 "2021-05-31T08:42:19Z")

</div>

Yea I am running the search query with track\_total\_hits `true`, you need that for queries that return counts greater than `10,000`

> [@kapso](#):
>
> {"query":{"bool":{"filter":[{"terms":{"level\_2\_classification\_ids":[261243]}}]}},"size":0,"track\_total\_hits":true}

Also, `/_count` query is returning same inaccurate results.

Hmm, ok will try with aggregations.

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [June 1, 2021, 8:51am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/12 "2021-06-01T08:51:25Z")

</div>

So apparently, aggregations also return the same inaccurate counts. Here's the aggs query...

```
{
  "aggs": {
    "my-agg-name": {
      "terms": {
        "field": "level_2_classification_ids",
        "size": 1,
        "include": [
          261243
        ]
      }
    }
  },
  "size": 0
}
```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [June 1, 2021, 8:57am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/13 "2021-06-01T08:57:12Z")

</div>

How are you deducing that the result is wrong?

---

<div class="post-metadata">

**Author:** ![kapso](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kapso/32/89472_2.png) [@kapso](https://discuss.elastic.co/u/kapso)\
**Post date:** [June 1, 2021, 9:12am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/14 "2021-06-01T09:12:47Z")

</div>

> [@kapso](#):
>
> How do we know the counts are inaccurate - well we were so confused by the inaccuracies, we looped through each document using scroll API to run a loop and do a manual count, and indeed our index/data looks ok, but Elasticsearch, unfortunately, is returning the wrong counts.

Using the above approach.

---

<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:** [June 29, 2021, 9:13am UTC](https://discuss.elastic.co/t/count-query-is-returning-inaccurate-results/274436/15 "2021-06-29T09:13:20Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.