Performance tuning for aggregators

Hello,
I have a below query which is doing mulitple aggregations and then returning the data. I am doing a filter on date greater than 7am this morning. The elasticsearch has got millions of rows and I expected the below query to be much faster than it really is. ANy performance or dos and donts in the below query will be really appreciated. I am searching on securityID field. This is using nodejs.
Number of replica -1
Single cluster
4gb heap size
The below query for one securityID takes 6 seconds to return. The document is a json

`
var d = new Date();

d.setHours(7,0,0,0);
return await client.search({

index:'axes_db',
size:0,
body: { 
  aggs: {
    "filterAggregator": {
      filter: 
      {
        bool:{     
                  must_not:[
                    { match:{"IOIQty":'0'}} ] ,              

                  must:[
                    {range:{InsertionTime:{'gte':d}}},    
                    {
                      match:{
                      SecurityID:{
                        query:`${a b c d}`,
                        operator:'OR',
                      }
                    }
                    },                         
                  ]
            }
      }   ,
      aggs: {
        "first_by_broker": {
          terms: {
          field: "Broker.keyword",
         // size:90000,
          // shard_size:100000
          },
          aggs: {
          "then_by_side": {
            terms: {
            field: "Side.keyword",
            // sizesize:100000
            },
            aggs: {
            "then_by_symbol": {
              terms: {
              field: "SecurityID.keyword",
              // size:90000,
              // shard_size:100000
              },
              aggs: {
              "get_top_record": {

                top_hits: {

                sort: [

                          {

                              "InsertionTime": {

                                  order: "desc"

                              }

                          }

                      ],

                  size : 1

                }

              }

            }

                }

              }          

            }

          }

        }

      }

    }

  }



}

})`

Here are the stats
{"indices":{"axes_db":{"primaries":{"refresh":{"total":181,"total_time_in_millis":53666,"external_total":176,"external_total_time_in_millis":53826,"listeners":0},"segments":{"count":43,"memory_in_bytes":1232452,"terms_memory_in_bytes":363952,"stored_fields_memory_in_bytes":818408,"term_vectors_memory_in_bytes":0,"norms_memory_in_bytes":46784,"points_memory_in_bytes":0,"doc_values_memory_in_bytes":3308,"index_writer_memory_in_bytes":11276192,"version_map_memory_in_bytes":4893868,"fixed_bit_set_memory_in_bytes":0,"max_unsafe_auto_id_timestamp":-1,"file_sizes":{}}},"total":{"refresh":{"total":181,"total_time_in_millis":53666,"external_total":176,"external_total_time_in_millis":53826,"listeners":0},"segments":{"count":43,"memory_in_bytes":1232452,"terms_memory_in_bytes":363952,"stored_fields_memory_in_bytes":818408,"term_vectors_memory_in_bytes":0,"norms_memory_in_bytes":46784,"points_memory_in_bytes":0,"doc_values_memory_in_bytes":3308,"index_writer_memory_in_bytes":11276192,"version_map_memory_in_bytes":4893868,"fixed_bit_set_memory_in_bytes":0,"max_unsafe_auto_id_timestamp":-1,"file_sizes":{}}}}}}

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