# Query with function\_score and aggregation is too slow

**URL:** <https://discuss.elastic.co/t/query-with-function-score-and-aggregation-is-too-slow/252654>\
**Category:** Elasticsearch\
**Created:** [October 20, 2020, 9:43am UTC](https://discuss.elastic.co/t/query-with-function-score-and-aggregation-is-too-slow/252654 "2020-10-20T09:43:41Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![badreedine](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badreedine/32/77504_2.png) [@badreedine](https://discuss.elastic.co/u/badreedine)\
**Post date:** [October 20, 2020, 9:43am UTC](https://discuss.elastic.co/t/query-with-function-score-and-aggregation-is-too-slow/252654/1 "2020-10-20T09:43:41Z")

</div>

I have an index of about 15M items, I am using version 6.7.0 installed in AWS. We adopted denormalisation approach in our design, each project has many items. A project has a title, description, tags, popularity, project\_score ... and each item has its own fields: height, width, overall\_score, likes\_count, category ... Here is a snippet of item index mapping:

```auto
{
  "item_v1": {
    "mappings": {
      "_doc": {
        "properties": {
          "project_description": {
            "type": "text"
          },
          "project_title": {
            "type": "text"
          },
          "project_tags": {
            "type": "keyword"
          },
          "project_popularity": {
            "type": "long"
          },
          "project_score": {
            "type": "long"
          },
          "project_id": {
            "type": "long"
          },
          "project_location": {
            "type": "geo_point"
          },
          "height": {
            "type": "short"
          },
          "width": {
            "type": "short"
          },
          "likes_count": {
            "type": "long"
          }
        }
      }
    }
  }
}

```

We are issuing a query using a little complex function\_score. because we have the same project.title for every item, when I search for example a keyword word1, and imagine word1 exists in project title of project\_1, project\_2 and project\_3. In the result set I am getting all project\_1 items first, then all project\_2 items, and finally all project\_3 items. To avoid this duplication, I tried to aggregate results with project\_id, but This is taking too much time (more than 600ms). Here is a snippet of our query:

```auto
    {
    "from": 0,
    "size": 15,
    "query": {
        "function_score": {
            "query": {
                "bool": {
                    "filter": [
                        
                    ],
                    "should": [
                        {
                            "multi_match": {
                                "query": "word1",
                                "fields": [
                                    "description^0.5",
                                    "tags^12.0",
                                    "title^5.0"
                                ],
                                "type": "phrase",
                                "operator": "AND",
                                "slop": 0,
                                "prefix_length": 0,
                                "max_expansions": 50,
                                "zero_terms_query": "NONE",
                                "auto_generate_synonyms_phrase_query": true,
                                "fuzzy_transpositions": true,
                                "boost": 8.0
                            }
                        },
                        {
                            "multi_match": {
                                "query": "word1",
                                "fields": [
                                    "description^0.5",
                                    "tags^12.0",
                                    "title^5.0"
                                ],
                                "type": "cross_fields",
                                "operator": "AND",
                                "slop": 0,
                                "prefix_length": 0,
                                "max_expansions": 50,
                                "zero_terms_query": "NONE",
                                "auto_generate_synonyms_phrase_query": true,
                                "fuzzy_transpositions": true,
                                "boost": 1.0
                            }
                        }
                    ],
                    "adjust_pure_negative": true,
                    "minimum_should_match": "1",
                    "boost": 1.0
                }
            },
            "functions": [
                {
                    "filter": {
                        "match_all": {
                            "boost": 1.0
                        }
                    },
                    "weight": 000,
                    "script_score": {
                        "script": {
                            "source": "0.99 * _score / (1 + _score)",
                            "lang": "painless"
                        }
                    }
                },
                {
                    "filter": {
                        "term": {
                            "xxxx": {
                                "value": true,
                                "boost": 1.0
                            }
                        }
                    },
                    "weight": 0000,
                    "linear": {
                        "project_location": {
                            "origin": {
                                "lat": 0000,
                                "lon": 0000
                            },
                            "scale": "75km",
                            "offset": "10km",
                            "decay": 0.3
                        },
                        "multi_value_mode": "MIN"
                    }
                },
                {
                    "filter": {
                        "term": {
                            "xxxxx": {
                                "value": false,
                                "boost": 1.0
                            }
                        }
                    },
                    "weight": 0000,
                    "exp": {
                        "location": {
                            "origin": {
                                "lat": 00000,
                                "lon": 00000
                            },
                            "scale": "10km",
                            "offset": "10km",
                            "decay": 0.5
                        },
                        "multi_value_mode": "MIN"
                    }
                },
                {
                    "filter": {
                        "exists": {
                            "field": "xxxxxx",
                            "boost": 1.0
                        }
                    },
                    "weight": 1.0
                },
                {
                    "filter": {
                        "term": {
                            "xxxxx": {
                                "value": "yy",
                                "boost": 1.0
                            }
                        }
                    },
                    "weight": 0000
                },
                {
                    "filter": {
                        "match_all": {
                            "boost": 1.0
                        }
                    },
                    "weight": 0000,
                    "exp": {
                        "created_at": {
                            "origin": "2020-10-19",
                            "scale": "100d",
                            "offset": "7d",
                            "decay": 0.6
                        },
                        "multi_value_mode": "MIN"
                    }
                },
                {
                    "filter": {
                        "range": {
                            "overall_score": {
                                "from": 1,
                                "to": null,
                                "include_lower": true,
                                "include_upper": true,
                                "boost": 1.0
                            }
                        }
                    },
                    "weight": 0000,
                    "script_score": {
                        "script": {
                            "source": "Math.log(1 + doc['overall_score'].value)",
                            "lang": "painless"
                        }
                    }
                }
            ],
            "score_mode": "sum",
            "boost_mode": "replace",
            "max_boost": 3.4028235E38,
            "min_score": 0.0,
            "boost": 1.0
        }
    },
    "aggs": {
        "users": {
            "terms": {
                "field": "project_id"
            }
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 17, 2020, 9:43am UTC](https://discuss.elastic.co/t/query-with-function-score-and-aggregation-is-too-slow/252654/2 "2020-11-17T09:43:46Z")

</div>

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