Query using Indexing (composite) for a value which is deep inside a document taking more time

SQL++
1

Hi

We have documents which look like below.

{
“_class”: “com.xxxx.yyyy.zzzz”,
“effectiveDateTime”: 1554055815572,
“entity”: {
…
** …,**
“stores”: [
“GB:SSSSS”
],
…
*…,
,
“condition”: {
…
**…,*,
“productGtins”: [
“111111111111111”
],
…
*…,
}
}
}

We have query that is like

select * from AAAAAA where _class=‘com.xxxx.yyyy.zzzz’ AND
effectiveDateTime <= STR_TO_MILLIS(now_str()) AND ANY store IN entity.stores SATISFIES store = ‘GB:SSSSS’ END AND ANY gtin IN entity.condition.productGtins SATISFIES gtin = ‘111111111111111’ END

Supporting index created is

CREATE INDEX xxxxx_yyyyy_Index ON
AAAAAA(effectiveDateTime,entity.stores,entity.condition.productGtins)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };

productGtins are quite deep inside the document(root->entity->condition->productGtins).

During load test this query is taking lot of time for execution.

Is the index creation correct??

If index creation is correct where we would have gone wrong?

Can this be replaced by view?

Any other solution available?

Regards,
Venkat