Hello ,
We have a requirement wherein we have to make a contains search on 3 fields "ITEM_NUMBER" , "DESCRIPTION" and "MAJOR_FAMILY_NAME" .
Search should always return 100 records where searching preference is like
- ITEM_NUMBER 2.DESCRIPTION 3. MAJOR_FAMILY_NAME
We need records to be sorted based on the match .
That means records with a matching ITEM_NUMBER should be at top .
We are using Custom score query for it as below .
Query takes almost 4-5 seconds and is very slow .
Index size is close to 34 million records and each record has 40 fields .
We seek expert suggestions on tuning this query .
Appreciate any inputs on performance improvement
{
"filtered" : {
"query" : {
"function_score" : {
"filter" : {
"bool" : {
"should" : [ {
"query" : {
"wildcard" : {
"ITEM_NUMBER" : {
"wildcard" : "searchstring"
}
}
}
}, {
"query" : {
"wildcard" : {
"DESCRIPTION" : {
"wildcard" : "searchstring"
}
}
}
}, {
"query" : {
"wildcard" : {
"MAJOR_FAMILY_NAME" : {
"wildcard" : "searchstring"
}
}
}
} ]
}
},
"functions" : [ {
"filter" : {
"query" : {
"wildcard" : {
"ITEM_NUMBER" : {
"wildcard" : "searchstring"
}
}
}
},
"boost_factor" : 5.0
}, {
"filter" : {
"query" : {
"wildcard" : {
"DESCRIPTION" : {
"wildcard" : "searchstring"
}
}
}
},
"boost_factor" : 2.0
}, {
"filter" : {
"query" : {
"wildcard" : {
"MAJOR_FAMILY_NAME" : {
"wildcard" : "searchstring"
}
}
}
},
"boost_factor" : 1.0
} ],
"score_mode" : "sum",
"boost_mode" : "replace"
}
},
"filter" : {
"and" : {
"filters" : [ {
"bool" : {
"must_not" : {
"query" : {
"term" : {
"IS_NPI_ENABLED" : "Y"
}
}
}
}
}, {
"query" : {
"term" : {
"ERP_PRICE_LIST_ID" : 1109
}
}
}, {
"query" : {
"term" : {
"DISTI_ONLY_SKU" : "N"
}
}
}, {
"query" : {
"term" : {
"SERVICE_FLAG_C" : "N"
}
}
}, {
"bool" : {
"should" : {
"bool" : {
"must" : {
"or" : {
"filters" : [ {
"query" : {
"term" : {
"PUBLISHED_TO_PRICING_FLAG" : "Y"
}
}
}, {
"missing" : {
"field" : "PUBLISHED_TO_PRICING_FLAG"
}
} ]
}
},
"must_not" : {
"query" : {
"term" : {
"VIEW_NAME" : "CO_LIMITED_ITEM_MASTER_VW"
}
}
}
}
}
}
} ]
}
}
}
}