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":{}}}}}}