Optimising count query

query
#1

I have a document that looks like this
{
“startTime”: 123,
“endTime”: 932345235,
“type”: “results”,
“validationResults”: [
{
“name”: “xyz”,
“ruleViolated”: 1,
“details”: [
{
“timeStamp”: 132345235,
“place”: “abc”,
“acknowledged”: false
},
{
“timeStamp”: 7658,
“place”: “bcd”,
“acknowledged”: true
}
]
},

    {
      "name": "xyz",
      "ruleViolated": 2,
      "details": [
        {
          "timeStamp": 132345235,
          "place": "abc",
          "acknowledged": false
        },
        {
          "timeStamp": 7658,
          "place": "bcd",
          "acknowledged": true
        }
      ]
    },

    {
      "name": "random",
      "ruleViolated": 1,
      "details": [
        {
          "timeStamp": 9679,
          "place": "abc",
          "acknowledged": false
        },
        {
          "timeStamp": 12412,
          "place": "bcd",
          "acknowledged": true
        }
      ]
    }

  ]
}

Now I want to have a query that counts unique name within a given time range that may or maynot cover the entire document time range. So I have used the following query
SELECT count(distinct(b.name)) as count
FROM resultBucket UNNEST a.validationResults as b where
( (a.startTime <= 1459470600000 AND a.endTime >= 1459470600000) OR (a.startTime >= 1459470600000 AND a.startTime <= 1459503600276) )
AND ( any tag in b.results satisfies tag.timeStamp BETWEEN 1459470600000 and 1459503600276 end )
AND a.type = “results”

But the query is taking too long than expected. Around 2 mins.

#2

The first thing I would try is an index like this:

create index times_idx on resultBucket(startTime, endTime)where type = "results"

Does that help? Does the index get used in the query? You can check by using EXPLAIN [the query] and examining the results.

#3

Yes I have the exact same index on my bucket and yet I am facing this issue. The index is being used in explain statement too.