Top hits query working very slow while retrieving data

[pooja_sanga]

Hi,

I have a nested index with 27627 records. I running the following query and getting the result in 34s if i put size=100. If i run the query for size-2/3 i get the result in about 1s. If i run the same query without the top_hit and includes, i get the result in less than a second. Can you please help me how I can optimize the query to get results faster for size 100 or above?

Query:
{
"_source" : false,
"aggs" : {

           "sub"  : { 
					"terms" : { 
							"field" : "TYPE",
							},
 "aggs": {
            "top_sales_hits": {
                "top_hits": {
				   "size" : 100,
                   "_source": {
                        "includes": [ "xyz_number" ]
                    }
            }
        }         

}}

My problem statement is that i want to perform an aggregation based on the type and then display items(multiple details) that have specific type

Result should look something like this:

"aggregations": {
"sub": {
"doc_count_error_upper_bound": 552,
"sum_other_doc_count": 22844,
"buckets": [
{
"key": "TYPE_65",
"doc_count": 496,
"top_sales_hits": {
"hits": {
"total": 496,
"max_score": 1,
"hits": [
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142100595",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_92"
}
},
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142169605",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_79"
}
},
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142145126",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_55"
}
}
]
}
}
},
{
"key": "TYPE_64",
"doc_count": 495,
"top_sales_hits": {
"hits": {
"total": 495,
"max_score": 1,
"hits": [
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142121994",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_91"
}
},
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142168857",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_91"
}
},
{
"_index": "xyz_index",
"_type": "_doc",
"_id": "1590142143737",
"_score": 1,
"_source": {
"xyz_number": "xyz_number_99"
}
}
]
}
}
}