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

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

Use Array Indexing on stores or producteGtins not both (only array index key is allowed)
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

Hi @vsr1

Thanks for the info. But unfortunately it didn’t help. Have tried with 2 seperate indexes and tested independently with out load. Still the query takes more than 1.5 seconds with only 30k documents in the bucket.

Here are the updated indexes,

Compund Array Index with productGtin this didn’t work:
CREATE INDEX xxxxx_yyyyy_Index ON
AAAAAA(effectiveDateTime,entity.stores,DISTINCT ARRAY gtin.productGtins FOR gtin IN entity.condition END)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };

Droped the above index and created below index which also didn’t help:
Compund Array Index with stores
CREATE INDEX xxxxx_yyyyy_Index ON
AAAAAA(effectiveDateTime,DISTINCT ARRAY str.stores FOR str IN entity END,entity.condition.productGtins)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };

Can you confirm if index creation is proper or any modifications are required??

Regards,
Venkat

CREATE INDEX ix1 ON default(effectiveDateTime,
                            DISTINCT ARRAY s FOR s IN entity.stores END,
                            entity.condition.productGtins)
WHERE _class = "com.xxxx.yyyy.zzzz";

CREATE INDEX ix2 ON default(effectiveDateTime,
                            DISTINCT ARRAY g FOR g IN entity.condition.productGtins END,
                            entity.stores)
WHERE _class = "com.xxxx.yyyy.zzzz";

SELECT d1.* FROM (SELECT RAW META(s).id
                 FROM default AS s USE INDEX (ix1)
                 WHERE s._class="com.xxxx.yyyy.zzzz"
                       AND s.effectiveDateTime <= NOW_MILLIS()
                       AND ANY s IN s.entity.stores SATISFIES s = "GB:SSSSS" END
                       AND ANY gtin IN s.entity.condition.productGtins SATISFIES gtin = "111111111111111" END) AS d
JOIN default AS d1 ON KEYS d;

Replace ix1 and ix2 and see which performs better and use that index.

1 Like

Also try this. Do EXPLAIN on subquery and make sure it is covered.

CREATE INDEX ix1 ON default(effectiveDateTime,
                            DISTINCT ARRAY s FOR s IN entity.stores END)
WHERE _class = "com.xxxx.yyyy.zzzz";

CREATE INDEX ix2 ON default(effectiveDateTime,
                            DISTINCT ARRAY g FOR g IN entity.condition.productGtins END)
WHERE _class = "com.xxxx.yyyy.zzzz";

SELECT d1.* FROM (SELECT META(s).id
                 FROM default AS s
                 WHERE s._class="com.xxxx.yyyy.zzzz"
                       AND s.effectiveDateTime <= NOW_MILLIS()
                       AND ANY s IN s.entity.stores SATISFIES s = "GB:SSSSS" END
                 INTERSECT
                 SELECT META(s).id
                 FROM default AS s
                 WHERE s._class="com.xxxx.yyyy.zzzz"
                       AND s.effectiveDateTime <= NOW_MILLIS()
                       AND ANY gtin IN s.entity.condition.productGtins SATISFIES gtin = "111111111111111" END) AS d
JOIN default AS d1 ON KEYS d.id;
1 Like

@vsr1 Many thanks :slight_smile:
ix2 did work .
With out modifying our select query the execution time was ~ 39 ms.
With JOIN query the execution time was ~ 42 ms

However, the INTERSECT and JOIN query was taking ~ 290ms.