Analytics Index with IN

Analytics
1

Hello,

I am seeing some weird behavior with my Analytics indexes and am struggling to figure out why is it behaving the way it is…

Here is my index definition:
create index tran_docNum_ix on tranData(DocNum:STRING,`$MdfdTmstmp`:STRING);

Query:

SELECT DISTINCT RAW DocNum
FROM tranData
WHERE DocNum IN ["10112746"]
  AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"

This works as expected and uses the tran_docNum_ix index.

But if I put multiple DocNum in the IN array, it does not pick up the index!

SELECT DISTINCT RAW DocNum
FROM tranData
WHERE DocNum IN ["10112746","12345"]
  AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"

Index was not used!

Taking this even further the following queries does not pick the index either:

Using LET but with just 1 DocNum

SELECT DISTINCT RAW DocNum
FROM tranData
LET available_daily_docs = ["10112746"]
WHERE DocNum IN available_daily_docs 
  AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"

Note that the DocNum field is not an array in the document. So I don’t think it would make sense to create an index with (unnest DocNum:STRING) unless I am misunderstanding array indexes in Analytics.

Thanks for your help with this…

2

Hi Amrish,

You could try splitting the IN clause using UNION ALL as follows:

SELECT DISTINCT RAW DocNum 
FROM ( 
  SELECT DocNum FROM tranData WHERE DocNum = "10112746" AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"
  UNION ALL
  SELECT DocNum FROM tranData WHERE DocNum = "12345" AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"
) t

Alternatively, try dropping your index and create one only on $MdfdTmstmp field
create index tran_ts_ix on tranData($MdfdTmstmp:STRING);
Then your original query should use that index for the BETWEEN predicate:

SELECT DISTINCT RAW DocNum
FROM tranData
WHERE DocNum IN ["10112746", "10112747"]
AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"

The LET clause problem requires further investigation on our side.