Script filter very slow

Elastic Stack Elasticsearch
1

Dear,
I have a query with script, and it took me very slow time to execute(15s). Are there anything to improve the performance?

{  
   "size":0,
   "query":{  
      "bool":{  
         "must":[  
            {  
               "range":{  
                  "date_sec":{  
                     "gte":1423155600,
                     "lte":1543165200
                  }
               }
            },
            {  
               "range":{  
                  "time_sec":{  
                     "gte":32400,
                     "lte":82800
                  }
               }
            },
            {  
               "terms":{  
                  "id_station":[  
                     2317,
                     2210,
                     2211,
                     2209,
                     2226,
                     2345,
                     2225,
                     2347,
                     2212,
                     2319,
                     2349,
                     2351,
                     2353,
                     2321,
                     2355,
                     2357,
                     2359,
                     2361,
                     2323,
                     2325,
                     2327,
                     2329,
                     2363,
                     2365,
                     2331,
                     2333,
                     2367,
                     2343,
                     2341,
                     2369,
                     2371,
                     2335,
                     2337,
                     2373,
                     2339
                  ]
               }
            }
         ]
      }
   },
   "aggs":{  
      "current_period3":{  
         "filter":{  
            "bool":{  
               "must":[  
                  {  
                     "terms":{  
                        "table_type":[  
                           3
                        ]
                     }
                  },
                  {  
                     "range":{  
                        "date_sec":{  
                           "gte":1483203600,
                           "lte":1543165200
                        }
                     }
                  }
               ]
            }
         },
         "aggs":{  
            "id_station":{  
               "terms":{  
                  "field":"id_station",
                  "size":1000
               },
               "aggs":{  
                  "area_type":{  
                     "terms":{  
                        "field":"area_type"
                     },
                     "aggs":{  
                        "ex_z1z2z3_numbuy_420to540":{  
                           "filter":{  
                              "script":{  
                                 "script":{  
                                    "inline":"(doc.zone1.value+doc.zone2.value+doc.zone3.value) >= 420 && (doc.zone1.value+doc.zone2.value+doc.zone3.value) < 540 && doc.buy.value == 1 && doc.sale_time.value != ''",
                                    "lang":"painless"
                                 }
                              }
                           }
                        },
                        "ex_z1z2z3_timebuy":{  
                           "filter":{  
                              "script":{  
                                 "script":{  
                                    "inline":"doc.goinside.value == 1 && doc.buy.value == 1 && doc.sale_time.value != '' && (doc.zone1.value+doc.zone2.value+doc.zone3.value) > 0",
                                    "lang":"painless"
                                 }
                              }
                           },
                           "aggs":{  
                              "ex_z1z2z3_timebuy":{  
                                 "sum":{  
                                    "script":{  
                                       "inline":"doc.zone1.value+doc.zone2.value+doc.zone3.value",
                                       "lang":"painless"
                                    }
                                 }
                              }
                           }
                        },
                        "ex_z1z2z3_numbuy":{  
                           "filter":{  
                              "script":{  
                                 "script":{  
                                    "inline":"doc.goinside.value == 1 && doc.buy.value == 1 && doc.sale_time.value != '' && (doc.zone1.value+doc.zone2.value+doc.zone3.value) > 0",
                                    "lang":"painless"
                                 }
                              }
                           }
                        }
						.............. over 1000 filter more ...................
                     }
                  }
               }
            }
         }
      }
   }
}

The result are:

{
    "took": 14980,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 45869315,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "previous_period3": {
            "doc_count": 0,
            "id_station": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
            }
        },
        "current_period3": {
            "doc_count": 3128225,
            "id_station": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
}
]
2

Your query returns 45_869_315 results, which means that for each of those 45 million documents script calculations need to be executed, which takes time, as this is done at the time of the query.

You should try and maybe do some calculations before you index, so that instead of script aggs/queries you could go with simple filters, which would speed up execution tremendously.

1 Like
3

I just make new index for all records needed, it's around 3mil but still the speed are same. so the problem coming from script, are there any replacement?

4

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