Dual index in analytics not working

Analytics
,
1

I have the query:

SELECT VALUE {'en': col_doc.data.en, 'lang': IFMISSING(col_doc.data.en, col_doc.data.en), 'doc_id': col_doc.id, 

'game_count': (
  
  SELECT VALUE COUNT(*)
  FROM deploy_dev.game.rollingslots col_doc_game
  JOIN deploy_dev.providers_game.rollingslots col_doc_prov ON col_doc_prov.data.en.alias /*+ indexnl */  = to_string(col_doc_game.data.en.prvider.alias)
  
  WHERE SOME category IN col_doc_game.data.en.categories SATISFIES category.alias /*+ indexnl */ = TO_STRING(col_doc.data.en.alias)
    AND (ARRAY_LENGTH(col_doc_game.data.en.allowed_countries) = 0
      OR ARRAY_CONTAINS(col_doc_game.data.en.allowed_countries, 'CA'))
    AND (ARRAY_LENGTH(col_doc_game.data.en.restricted_countries) = 0
      OR NOT ARRAY_CONTAINS(col_doc_game.data.en.restricted_countries, 'CA')) )[0],
      
      
      
'orderBy': ARRAY_LENGTH(OBJECT_VALUES(col_doc._orderBy)) > 0}
FROM deploy_dev.categories_game.rollingslots col_doc
WHERE (col_doc.data.en.category_view=TRUE)
ORDER BY orderBy DESC,
         col_doc._orderBy.base ASC,
         col_doc.updated_at DESC
LIMIT 25

For it I created index

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (UNNEST data.en.categories SELECT alias:STRING, data.en.provider.alias:STRING)
EXCLUDE UNKNOWN KEY;

But in query plan the query don`t use it. Why?
If I use only

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (UNNEST data.en.categories SELECT alias:STRING)
EXCLUDE UNKNOWN KEY;

without

 JOIN deploy_dev.providers_game.rollingslots col_doc_prov ON col_doc_prov.data.en.alias /*+ indexnl */  = to_string(col_doc_game.data.en.prvider.alias)

The query used index correctly

Please help me!

2

Hi @Lenty_Me,
What are you trying to achieve with the below index?

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (
UNNEST data.en.categories SELECT alias:STRING, data.en.provider.alias:STRING)
EXCLUDE UNKNOWN KEY;

The above index suggests that the categories field is structured this way. Is this correct?:

"categories": [
	  {"alias": "a1", "data": {"en": {"provider": {"alias": "aaa1"}}}},
	  {"alias": "a2", "data": {"en": {"provider": {"alias": "aaa2"}}}},
]

What is the data model for deploy_dev.game.rollingslots? Is it like this?

{
  "data": {
    "en": {
      "provider": {"alias": "p"},
      "categories": [{"alias": "a1"}, {"alias": "a2"}, {"alias": "a3"}]
    }
  }
}

Also, I noticed that you have data.en.prvider.alias in the query while in the index you have data.en.provider.alias (prvider vs. provider). Is that a typo?

3

Thank you for reply!
Yes, your document is like :

{
  "data": {
    "en": {
      "provider": {"alias": "p"},
      "categories": [{"alias": "a1"}, {"alias": "a2"}, {"alias": "a3"}]
    }
  }
}

AND index for it is:

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (
UNNEST data.en.categories SELECT alias:STRING, data.en.provider.alias:STRING)
EXCLUDE UNKNOWN KEY;

But SQL query didn`t use this index. Is it wrong ?

4

Hi @Lenty_Me,

Currently array indexes do not support prefix search. That means a composite array index will be used only when all the indexed fields are specified in the predicate.
For your case, the composite array index is on the two fields alias from the array field data.en.categories and data.en.provider.alias:

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (UNNEST data.en.categories SELECT alias:STRING, data.en.provider.alias:STRING)
EXCLUDE UNKNOWN KEY;

In the query, the WHERE predicate has only a prefix of the composite array index which is alias from the array field data.en.categories:

WHERE SOME category IN col_doc_game.data.en.categories SATISFIES category.alias /*+ indexnl */ = TO_STRING(col_doc.data.en.alias)

That is why the composite array index is not used because it is considered a prefix search on the index. However, the below index is used because all the fields in the WHERE predicate match the the fields of the index, and hence it is not a prefix search.

CREATE INDEX categoriesIdx
ON deploy_dev.game.rollingslots (UNNEST data.en.categories SELECT alias:STRING)
EXCLUDE UNKNOWN KEY;

Of course, if you want to use the above single-valued array index when the query has the JOIN, then this is the query that you should use:

SELECT VALUE {'en': col_doc.data.en, 'lang': IFMISSING(col_doc.data.en, col_doc.data.en), 'doc_id': col_doc.id, 

'game_count': (
  
  SELECT VALUE COUNT(*)
  FROM (SELECT data.en.provider.alias FROM deploy_dev.game.rollingslots col_doc_game
  WHERE SOME category IN col_doc_game.data.en.categories SATISFIES category.alias /*+ indexnl */ = TO_STRING(col_doc.data.en.alias)
    AND (ARRAY_LENGTH(col_doc_game.data.en.allowed_countries) = 0
      OR ARRAY_CONTAINS(col_doc_game.data.en.allowed_countries, 'CA'))
    AND (ARRAY_LENGTH(col_doc_game.data.en.restricted_countries) = 0
      OR NOT ARRAY_CONTAINS(col_doc_game.data.en.restricted_countries, 'CA'))) AS col_game
  JOIN deploy_dev.providers_game.rollingslots col_doc_prov ON col_doc_prov.data.en.alias = col_game.data.en.provider.alias
  
   )[0],

'orderBy': ARRAY_LENGTH(OBJECT_VALUES(col_doc._orderBy)) > 0}

FROM deploy_dev.categories_game.rollingslots col_doc
WHERE (col_doc.data.en.category_view=TRUE)
ORDER BY orderBy DESC, col_doc._orderBy.base ASC, col_doc.updated_at DESC
LIMIT 25;

I am not sure about what you are trying to achieve with the composite array index, but for the JOIN part, you can create an index on the field data.en.alias of the collection deploy_dev.providers_game.rollingslots:

CREATE INDEX data_en_alias_idx ON deploy_dev.providers_game.rollingslots(data.en.alias: string);

Then try the following query:

SELECT VALUE {'en': col_doc.data.en, 'lang': IFMISSING(col_doc.data.en, col_doc.data.en), 'doc_id': col_doc.id, 

'game_count': (
  
  SELECT VALUE COUNT(*)
  FROM (SELECT data.en.provider.alias FROM deploy_dev.game.rollingslots col_doc_game
  WHERE SOME category IN col_doc_game.data.en.categories SATISFIES category.alias /*+ indexnl */ = TO_STRING(col_doc.data.en.alias)
    AND (ARRAY_LENGTH(col_doc_game.data.en.allowed_countries) = 0
      OR ARRAY_CONTAINS(col_doc_game.data.en.allowed_countries, 'CA'))
    AND (ARRAY_LENGTH(col_doc_game.data.en.restricted_countries) = 0
      OR NOT ARRAY_CONTAINS(col_doc_game.data.en.restricted_countries, 'CA'))) AS col_game
  JOIN deploy_dev.providers_game.rollingslots col_doc_prov ON col_doc_prov.data.en.alias /*+ indexnl */ = TO_STRING(col_game.data.en.provider.alias)
  
   )[0],

'orderBy': ARRAY_LENGTH(OBJECT_VALUES(col_doc._orderBy)) > 0}

FROM deploy_dev.categories_game.rollingslots col_doc
WHERE (col_doc.data.en.category_view=TRUE)
ORDER BY orderBy DESC, col_doc._orderBy.base ASC, col_doc.updated_at DESC
LIMIT 25;

The usefulness of those secondary indexes will depend on the cardinalities of the involved collections and the selectivities of the secondary indexes.

5

Thank you very much. It works perfect

6

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