COUNT condition: INDEX Speed-up improvements

SQL++
19

I was waiting for the results to come through. Both queries timeout

cbq> SELECT SUBSTR(t, 0, 10), {did : {
                "foull_entries" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
   >    >               "total_entries" : COUNT(*),
   >            "nulld_entries" : COUNT(CASE WHEN ARRAY_LENGTH(bcn)==0 THEN 1 ELSE NULL END)
        }
}
FROM sync_gateway
WHERE
    type="bcn_scan"
    AND SUBSTR(t, 0, 10) BETWEEN "2016-11-01" AND "2016-12-15"
    AND did IS NOT NULL
GROUP BY SUBSTR(t, 0, 10), did
ORDER BY S   >    >    >    >    >    >    >    >    > UBSTR(t, 0, 10) ASC, did ASC;
{
    "requestID": "d059f667-4b30-41cc-a2a2-56e89ad4c0dd",
    "signature": {
        "$1": "string",
        "$2": "object"
    },
    "results": [
        {}
    ],
    "errors": [
        {
            "code": 12015,
            "msg": "Index scan timed out - cause: Index scan timed out"
        }
    ],
    "status": "errors",
    "metrics": {
        "elapsedTime": "2m0.103019842s",
        "executionTime": "2m0.102792644s",
        "resultCount": 1,
        "resultSize": 2,
        "sortCount": 1,
        "errorCount": 1
    }
}
cbq> 

SELECT SUBSTR(t, 0, 10), {did : {
        "foull_entries" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
        "total_entries" : COUNT(*),
        "nulld_entries" : COUNT(CASE WHEN ARRAY_LENGTH(bcn)==0 THEN 1 ELSE NULL END)
    }
}
FROM sync_gateway
WHERE
    type="bcn_scan"
    AND SUBSTR(t, 0, 10) BETWEEN "2016-11-01" AND "2016-12-15"
GROUP BY SUBSTR(t, 0, 10), did
ORDER BY SUBSTR(t, 0, 10) ASC, did ASC;

"errors": [
    {
        "code": 12015,
        "msg": "Index scan timed out - cause: Index scan timed out"
    }
],
"status": "errors",
"metrics": {
    "elapsedTime": "2m3.604967639s",
    "executionTime": "2m3.604919887s",
    "resultCount": 96,
    "resultSize": 26328,
    "sortCount": 96,
    "errorCount": 1

The primary index queries average at about 2m57 to 4m49, so I could increase the time - but I don’t know it it’s worth it after all