Query
SELECT did, ARRAY_AGG(DISTINCT SUBSTR(t, 0, 10)) AS days, {
"sums" : COUNT(*),
"null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
"full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0))
} AS entries
FROM agora_bucket
WHERE type="bcn_scan"
GROUP BY did
ORDER BY did ASC;
returns
],
"status": "success",
"metrics": {
"elapsedTime": "18m21.700100793s",
"executionTime": "18m21.700054826s",
"resultCount": 17,
"resultSize": 4864,
"sortCount": 17
}
while
SELECT did, ARRAY_AGG(DISTINCT SUBSTR(t, 0, 10)) AS days, {
"sums" : COUNT(*),
"null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
"full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0))
} AS entries
FROM agora_bucket
WHERE type="bcn_scan" AND SUBSTR(t, 0, 10) >= "2017-03-03"
GROUP BY did
ORDER BY did ASC;
returns
],
"errors": [
{
"code": 12015,
"msg": "Index scan timed out - cause: Index scan timed out"
}
],
"status": "errors",
"metrics": {
"elapsedTime": "4m28.956528875s",
"executionTime": "4m28.956473392s",
"resultCount": 9,
"resultSize": 2140,
"sortCount": 9,
"errorCount": 1
}
I was constantly running the second query (SUBSTR(t, 0, 10) >= "2017-03-03"), then I randomly run the first query, then again the second.
Server resources are limited (and I completely understand it being a limiting factor); moreover data from 9 devices are constantly streamed in the database 24/7.
However, it’s weird to me why would that happen.