Terms Aggregation on billions of documents

[bevans88 (Brent Evans)]

Hi,

I'm wondering if anyone has any experience with running terms aggregations, with a sub sum aggregation, on a very large index (~100 billion documents). The documents themselves are relatively small (~20 fields). Some of these fields can be high cardinality (~ 40 million).

The requirement here is that we achieve around a 5 second query response. The worse case query for the aggregation is a match_all.

As far as I'm aware to achieve this scale we'd require a very large cluster, which even then might not achieve the required performance.

Does anyone have any experience/thoughts on this?

Thanks,

Brent

[bevans88 (Brent Evans)]

To give some additional information, the mapping for the documents is currently:

 {
  "_all" : { "enabled": false  },
  "properties" : {
    "field1" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field2" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field3" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field4" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field5" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field6" : {
      "type" : "date",
      "format" : "basic_date_time_no_millis"
    },
    "field7" : {
      "type" : "date",
      "format" : "basic_date_time_no_millis"
    },
    "field8" : {
      "type" : "date",
      "format" : "basic_date_time_no_millis"
    },
    "field9" : {
      "type": "text"
    },
    "field10" : {
      "type": "text"
    },
    "field11" : {
      "type" : "text",
      "fielddata": true,
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "eager_global_ordinals": true
        }
      }
    },
    "field12" : {
      "type" : "long"
    }
  }
}

And an example aggregation would be:

{
  "size" : 0,
  "query" : {
    "match_all" : { }
  },
  "_source" : false,
  "aggregations" : {
    "by_terms" : {
      "terms" : {
        "field" : "field11.keyword",
        "size" : 10000,
        "shard_size" : 1000000,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 0,
        "show_term_doc_count_error" : false,
        "order" : [
          {
            "_count" : "desc"
          },
          {
            "_term" : "asc"
          }
        ]
      },
      "aggregations" : {
        "totalSize" : {
          "sum" : {
            "field" : "size"
          }
        }
      }
    }
  }
}