# Doing aggregations using flattened\_fields is slow

**URL:** <https://discuss.elastic.co/t/doing-aggregations-using-flattened-fields-is-slow/266832>\
**Category:** Elasticsearch\
**Created:** [March 10, 2021, 3:27pm UTC](https://discuss.elastic.co/t/doing-aggregations-using-flattened-fields-is-slow/266832 "2021-03-10T15:27:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ronald\_de\_Haan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ronald_de_haan/32/83480_2.png) [@Ronald\_de\_Haan](https://discuss.elastic.co/u/Ronald_de_Haan)\
**Post date:** [March 10, 2021, 3:27pm UTC](https://discuss.elastic.co/t/doing-aggregations-using-flattened-fields-is-slow/266832/1 "2021-03-10T15:27:44Z")

</div>

We consume and store data containing a field _filter\_properties_ containing key =\> value fields.  
Up until now we use dynamic mapping to store that data but that's creating a lot of unique fields.  
So I decided to try the _flattened\_field_ type.

I now have a mapping looking like this:

```auto
    {
      "mappings" : {
        "dynamic_templates" : [
          {
            "filterprops" : {
              "path_match" : "filter_properties.*",
              "mapping" : {
                "fields" : {
                  "analyzed" : {
                    "normalizer" : "sort_normalizer",
                    "type" : "keyword"
                  }
                },
                "index" : true,
                "norms" : false,
                "type" : "keyword"
              }
            }
          }
       ],
       "properties": {
          "filters" : {
            "dynamic" : "strict",
            "properties" : {
              "analyzed" : {
                "type" : "flattened",
               "eager_global_ordinals": true
              },
              "raw" : {
                "type" : "flattened",
               "eager_global_ordinals": true
              }
            }
          }
       }
      }
    }

```

But doing aggregations using the new _filters_ fields is "very" slow.  
Doing a query like this (using the flattened\_field) takes between 200/300 ms

```auto
POST my_index/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "foo": {
      "terms": {
        "field": "filters.raw.QBC",
        "size": 300
      }
    }
  }
}

```

Where this query on the same index takes between 20/30 ms

```auto
POST my_index/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "foo": {
      "terms": {
        "field": "filters_propertied.QBC",
        "size": 300
      }
    }
  }
}

```

Clearly I'm missing something here but I don't see it. Anyone have any pointers for me?

---

<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:** [March 10, 2021, 4:41pm UTC](https://discuss.elastic.co/t/doing-aggregations-using-flattened-fields-is-slow/266832/2 "2021-03-10T16:41:31Z")

</div>

When you index concrete fields you have dedicated data structures on disk. e.g. given fields `obj.foo` and `obj.bar` you have 2 different doc value data structures you can effectively index directly into via the Lucene doc id:

```
obj.foo : ["a", , , , , "b", , ,]
obj.bar : ["c", , "a", , , ,]

```

So if your query matches docs 1, 4 and 7 you can index efficiently into the values for field `obj.foo` and retrieve the single values for this field.

With a flattened field the field names and values are combined so the above data structure is logically more like this on disk:

```auto
    obj: [
                  [....]
                  ["obj.baz:other", "obj.foo:a", "obj.z:blah" , ...],
                  [....],
                  ["obj.blah:misc", "obj.foo:b", "obj.z:etc", ...],
                  [...]
                  ["obj.baz:other", "obj.foo:c", "obj.z:blah" , ...],
    ] 

```

You can still index into this structure based on doc ID but the doc values aren't single fields. They are arrays of all fieldnames+values for a doc that you need to iterate across and filter for the `obj.foo` field name you're aggregating on. This is slower.

---

<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:** [April 7, 2021, 4:41pm UTC](https://discuss.elastic.co/t/doing-aggregations-using-flattened-fields-is-slow/266832/3 "2021-04-07T16:41:34Z")

</div>

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