N.B.: for clarity purposes I mininimized the query
EXPLAIN SELECT query.did, ARRAY_AGG(DISTINCT SUBSTR(query.t, 0, 10)) AS days, {
"sums" : COUNT(*),
"null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0)),
"full" : COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0))
} AS entries
FROM (
SELECT did, SUBSTR(t, 0, 10), bcn
FROM sync_gateway
WHERE type="bcn_scan" AND NOT (
SUBSTR(t, 0, 10) IN ["2015-01-03", "2015-01-04", "2015-01-05",
"2015-01-06", "2015-01-07", "2015-01-08", "2015-01-09", "2015-01-10",
"2015-01-11", "2015-01-12", "2015-01-13", "2015-01-14", "2015-01-15",
"2015-01-16", "2015-01-17", "2015-01-18", "2015-01-19", "2015-01-20",
"2015-01-21", "2015-01-22", "2015-01-23", "2015-01-24", "2015-01-25",
"2015-01-26", "2015-01-27", "2015-01-28", "2015-01-29", "2015-01-30",
"2015-01-31", "2015-02-01", "2015-02-02", "2015-02-03", "2015-02-04",
"2015-02-05", "2015-02-06", "2015-02-07", "2015-02-08", "2015-02-09",
"2015-02-10", "2015-02-11", "2015-02-12", "2015-02-13", "2015-02-14",
"2015-02-15", "2015-02-16", "2015-02-17", "2015-02-18", "2015-02-19",
"2015-02-20", "2015-02-21", "2015-02-22", "2015-02-23", "2015-02-24",
"2015-02-25", "2015-02-26", "2015-02-27", "2015-02-28", "2015-03-01",
"2015-03-02", "2015-03-03", "2015-03-04", "2015-03-05", "2015-03-06",
"2015-03-07", "2015-03-08", "2015-03-09", "2015-03-10", "2015-03-11",
"2015-03-12", "2015-03-13", "2015-03-14", "2015-03-15", "2015-03-16",
"2015-03-17", "2015-03-18", "2015-03-19", "2015-03-20", "2015-03-21",
"2015-03-22", "2015-03-23", "2015-03-24", "2015-03-25", "2015-03-26",
"2015-03-27", "2015-03-28", "2015-03-29", "2015-03-30", "2015-03-31",
"2015-04-01", "2015-04-02", "2015-04-03", "2015-04-04", "2015-04-05",
"2015-04-06", "2015-04-07", "2015-04-08", "2015-04-09", "2015-04-10",
"2015-04-11", "2015-04-12", "2015-04-13", "2015-04-14", "2015-04-15",
"2015-04-16", "2015-04-17", "2015-04-18", "2015-04-19", "2015-04-20",
"2015-04-21", "2015-04-22", "2015-04-23", "2015-04-24", "2015-04-25",
"2015-04-26", "2015-04-27", "2015-04-28", "2015-04-29", "2015-04-30"]
AND did IN [
"63AF154DBDAF245176753E36EB0AE78F", "9D5E0987B0A26F08697407C5EFD83E50",
"5486C01B2EE5A30FBD342A6D49933DF9", "DA2D16DA2AB37D61A706F0C9BD808E00",
"066889F99301F4B0C6BE94F87FE582AC", "D19C9727FDCBE0764FDFCBFCE5893135"]
)
UNION
SELECT did, SUBSTR(t, 0, 10), bcn
FROM sync_gateway
WHERE type="bcn_scan" AND NOT (
SUBSTR(t, 0, 10) IN ["2015-06-18", "2015-06-19", "2015-06-20",
"2015-06-21", "2015-06-22", "2015-06-23", "2015-06-24", "2015-06-25",
"2015-06-26", "2015-06-27", "2015-06-28", "2015-06-29", "2015-06-30"]
AND did IN [
"DF0EA83DA465D7A34FC8BDCF8BD54B1D", "64CE31AB85A61002EC4A5DF7A609A1C6"]
)
) AS query
GROUP BY did
ORDER BY did ASC;
Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.
Path to history file for the shell : /root/.cbq_history
{
"requestID": "e073b551-6569-4fe4-9980-c14ae763d122",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionAll",
"children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "sync_gateway",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "sync_gateway",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`sync_gateway`.`type`) = \"bcn_scan\") and (not ((substr((`sync_gateway`.`t`), 0, 10) in [\"2015-01-03\", \"2015-01-04\", \"2015-01-05\", \"2015-01-06\", \"2015-01-07\", \"2015-01-08\", \"2015-01-09\", \"2015-01-10\", \"2015-01-11\", \"2015-01-12\", \"2015-01-13\", \"2015-01-14\", \"2015-01-15\", \"2015-01-16\", \"2015-01-17\", \"2015-01-18\", \"2015-01-19\", \"2015-01-20\", \"2015-01-21\", \"2015-01-22\", \"2015-01-23\", \"2015-01-24\", \"2015-01-25\", \"2015-01-26\", \"2015-01-27\", \"2015-01-28\", \"2015-01-29\", \"2015-01-30\", \"2015-01-31\", \"2015-02-01\", \"2015-02-02\", \"2015-02-03\", \"2015-02-04\", \"2015-02-05\", \"2015-02-06\", \"2015-02-07\", \"2015-02-08\", \"2015-02-09\", \"2015-02-10\", \"2015-02-11\", \"2015-02-12\", \"2015-02-13\", \"2015-02-14\", \"2015-02-15\", \"2015-02-16\", \"2015-02-17\", \"2015-02-18\", \"2015-02-19\", \"2015-02-20\", \"2015-02-21\", \"2015-02-22\", \"2015-02-23\", \"2015-02-24\", \"2015-02-25\", \"2015-02-26\", \"2015-02-27\", \"2015-02-28\", \"2015-03-01\", \"2015-03-02\", \"2015-03-03\", \"2015-03-04\", \"2015-03-05\", \"2015-03-06\", \"2015-03-07\", \"2015-03-08\", \"2015-03-09\", \"2015-03-10\", \"2015-03-11\", \"2015-03-12\", \"2015-03-13\", \"2015-03-14\", \"2015-03-15\", \"2015-03-16\", \"2015-03-17\", \"2015-03-18\", \"2015-03-19\", \"2015-03-20\", \"2015-03-21\", \"2015-03-22\", \"2015-03-23\", \"2015-03-24\", \"2015-03-25\", \"2015-03-26\", \"2015-03-27\", \"2015-03-28\", \"2015-03-29\", \"2015-03-30\", \"2015-03-31\", \"2015-04-01\", \"2015-04-02\", \"2015-04-03\", \"2015-04-04\", \"2015-04-05\", \"2015-04-06\", \"2015-04-07\", \"2015-04-08\", \"2015-04-09\", \"2015-04-10\", \"2015-04-11\", \"2015-04-12\", \"2015-04-13\", \"2015-04-14\", \"2015-04-15\", \"2015-04-16\", \"2015-04-17\", \"2015-04-18\", \"2015-04-19\", \"2015-04-20\", \"2015-04-21\", \"2015-04-22\", \"2015-04-23\", \"2015-04-24\", \"2015-04-25\", \"2015-04-26\", \"2015-04-27\", \"2015-04-28\", \"2015-04-29\", \"2015-04-30\"]) and ((`sync_gateway`.`did`) in [\"63AF154DBDAF245176753E36EB0AE78F\", \"9D5E0987B0A26F08697407C5EFD83E50\", \"5486C01B2EE5A30FBD342A6D49933DF9\", \"DA2D16DA2AB37D61A706F0C9BD808E00\", \"066889F99301F4B0C6BE94F87FE582AC\", \"D19C9727FDCBE0764FDFCBFCE5893135\"]))))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`sync_gateway`.`did`)"
},
{
"expr": "substr((`sync_gateway`.`t`), 0, 10)"
},
{
"expr": "(`sync_gateway`.`bcn`)"
}
]
},
{
"#operator": "Distinct"
},
{
"#operator": "FinalProject"
}
]
}
},
{
"#operator": "Distinct"
}
]
},
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "sync_gateway",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "sync_gateway",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`sync_gateway`.`type`) = \"bcn_scan\") and (not ((substr((`sync_gateway`.`t`), 0, 10) in [\"2015-06-18\", \"2015-06-19\", \"2015-06-20\", \"2015-06-21\", \"2015-06-22\", \"2015-06-23\", \"2015-06-24\", \"2015-06-25\", \"2015-06-26\", \"2015-06-27\", \"2015-06-28\", \"2015-06-29\", \"2015-06-30\"]) and ((`sync_gateway`.`did`) in [\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\", \"64CE31AB85A61002EC4A5DF7A609A1C6\"]))))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`sync_gateway`.`did`)"
},
{
"expr": "substr((`sync_gateway`.`t`), 0, 10)"
},
{
"expr": "(`sync_gateway`.`bcn`)"
}
]
},
{
"#operator": "Distinct"
},
{
"#operator": "FinalProject"
}
]
}
},
{
"#operator": "Distinct"
}
]
}
]
},
{
"#operator": "Distinct"
}
]
},
{
"#operator": "Alias",
"as": "query"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialGroup",
"aggregates": [
"array_agg(distinct substr((`query`.`t`), 0, 10))",
"count(*)",
"count(nullif(array_length((`query`.`bcn`)), 0))"
],
"group_keys": [
"(`query`.`did`)"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"array_agg(distinct substr((`query`.`t`), 0, 10))",
"count(*)",
"count(nullif(array_length((`query`.`bcn`)), 0))"
],
"group_keys": [
"(`query`.`did`)"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"array_agg(distinct substr((`query`.`t`), 0, 10))",
"count(*)",
"count(nullif(array_length((`query`.`bcn`)), 0))"
],
"group_keys": [
"(`query`.`did`)"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`query`.`did`)"
},
{
"as": "days",
"expr": "array_agg(distinct substr((`query`.`t`), 0, 10))"
},
{
"as": "entries",
"expr": "{\"\\\"full\\\"\": count(nullif(array_length((`query`.`bcn`)), 0)), \"\\\"null\\\"\": (count(*) - count(nullif(array_length((`query`.`bcn`)), 0))), \"\\\"sums\\\"\": count(*)}"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "(`query`.`did`)"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT query.did, ARRAY_AGG(DISTINCT SUBSTR(query.t, 0, 10)) AS days, {\n \"sums\" : COUNT(*),\n \"null\" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0)),\n \"full\" : COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0))\n} AS entries\nFROM (\n SELECT did, SUBSTR(t, 0, 10), bcn\n FROM sync_gateway\n WHERE type=\"bcn_scan\" AND NOT (\n SUBSTR(t, 0, 10) IN [\"2015-01-03\", \"2015-01-04\", \"2015-01-05\",\n \"2015-01-06\", \"2015-01-07\", \"2015-01-08\", \"2015-01-09\", \"2015-01-10\",\n \"2015-01-11\", \"2015-01-12\", \"2015-01-13\", \"2015-01-14\", \"2015-01-15\",\n \"2015-01-16\", \"2015-01-17\", \"2015-01-18\", \"2015-01-19\", \"2015-01-20\",\n \"2015-01-21\", \"2015-01-22\", \"2015-01-23\", \"2015-01-24\", \"2015-01-25\",\n \"2015-01-26\", \"2015-01-27\", \"2015-01-28\", \"2015-01-29\", \"2015-01-30\",\n \"2015-01-31\", \"2015-02-01\", \"2015-02-02\", \"2015-02-03\", \"2015-02-04\",\n \"2015-02-05\", \"2015-02-06\", \"2015-02-07\", \"2015-02-08\", \"2015-02-09\",\n \"2015-02-10\", \"2015-02-11\", \"2015-02-12\", \"2015-02-13\", \"2015-02-14\",\n \"2015-02-15\", \"2015-02-16\", \"2015-02-17\", \"2015-02-18\", \"2015-02-19\",\n \"2015-02-20\", \"2015-02-21\", \"2015-02-22\", \"2015-02-23\", \"2015-02-24\",\n \"2015-02-25\", \"2015-02-26\", \"2015-02-27\", \"2015-02-28\", \"2015-03-01\",\n \"2015-03-02\", \"2015-03-03\", \"2015-03-04\", \"2015-03-05\", \"2015-03-06\",\n \"2015-03-07\", \"2015-03-08\", \"2015-03-09\", \"2015-03-10\", \"2015-03-11\",\n \"2015-03-12\", \"2015-03-13\", \"2015-03-14\", \"2015-03-15\", \"2015-03-16\",\n \"2015-03-17\", \"2015-03-18\", \"2015-03-19\", \"2015-03-20\", \"2015-03-21\",\n \"2015-03-22\", \"2015-03-23\", \"2015-03-24\", \"2015-03-25\", \"2015-03-26\",\n \"2015-03-27\", \"2015-03-28\", \"2015-03-29\", \"2015-03-30\", \"2015-03-31\",\n \"2015-04-01\", \"2015-04-02\", \"2015-04-03\", \"2015-04-04\", \"2015-04-05\",\n \"2015-04-06\", \"2015-04-07\", \"2015-04-08\", \"2015-04-09\", \"2015-04-10\",\n \"2015-04-11\", \"2015-04-12\", \"2015-04-13\", \"2015-04-14\", \"2015-04-15\",\n \"2015-04-16\", \"2015-04-17\", \"2015-04-18\", \"2015-04-19\", \"2015-04-20\",\n \"2015-04-21\", \"2015-04-22\", \"2015-04-23\", \"2015-04-24\", \"2015-04-25\",\n \"2015-04-26\", \"2015-04-27\", \"2015-04-28\", \"2015-04-29\", \"2015-04-30\"]\n AND did IN [\n \"63AF154DBDAF245176753E36EB0AE78F\", \"9D5E0987B0A26F08697407C5EFD83E50\",\n \"5486C01B2EE5A30FBD342A6D49933DF9\", \"DA2D16DA2AB37D61A706F0C9BD808E00\",\n \"066889F99301F4B0C6BE94F87FE582AC\", \"D19C9727FDCBE0764FDFCBFCE5893135\"]\n )\n\n UNION\n\n SELECT did, SUBSTR(t, 0, 10), bcn\n FROM sync_gateway\n WHERE type=\"bcn_scan\" AND NOT (\n SUBSTR(t, 0, 10) IN [\"2015-06-18\", \"2015-06-19\", \"2015-06-20\",\n \"2015-06-21\", \"2015-06-22\", \"2015-06-23\", \"2015-06-24\", \"2015-06-25\",\n \"2015-06-26\", \"2015-06-27\", \"2015-06-28\", \"2015-06-29\", \"2015-06-30\"]\n AND did IN [\n \"DF0EA83DA465D7A34FC8BDCF8BD54B1D\", \"64CE31AB85A61002EC4A5DF7A609A1C6\"]\n )\n) AS query\nGROUP BY did\nORDER BY did ASC;"
}
],
"status": "success",
"metrics": {
"elapsedTime": "47.290783ms",
"executionTime": "47.206988ms",
"resultCount": 1,
"resultSize": 18601
}
}
However, this query is perfectly fine for the index:
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 sync_gateway
WHERE type="bcn_scan" AND
SUBSTR(t, 0, 10) IN ["2015-06-18", "2015-06-19", "2015-06-20",
"2015-06-21", "2015-06-22", "2015-06-23", "2015-06-24", "2015-06-25",
"2015-06-26", "2015-06-27", "2015-06-28", "2015-06-29", "2015-06-30"]
AND did IN ["DF0EA83DA465D7A34FC8BDCF8BD54B1D"]
GROUP BY did
ORDER BY did ASC;
As this is as well:
EXPLAIN SELECT * FROM sync_gateway WHERE type="bcn_scan" AND (
SUBSTR(t, 0, 10) BETWEEN "2015-06-18" AND "2015-06-30" OR
SUBSTR(t, 0, 10) BETWEEN "2015-03-01" AND "2015-03-31") AND
did="DF0EA83DA465D7A34FC8BDCF8BD54B1D";
Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.
Path to history file for the shell : /root/.cbq_history
{
"requestID": "a0b5e067-fc10-46de-b89b-68736034381d",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "bcn_scan-date-did",
"index_id": "d37e2d41290035c3",
"keyspace": "sync_gateway",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"2015-06-30\"",
"\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
],
"Inclusion": 3,
"Low": [
"\"2015-06-18\"",
"\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
]
}
},
{
"Range": {
"High": [
"\"2015-03-31\"",
"\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
],
"Inclusion": 3,
"Low": [
"\"2015-03-01\"",
"\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"keyspace": "sync_gateway",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`sync_gateway`.`type`) = \"bcn_scan\") and ((substr((`sync_gateway`.`t`), 0, 10) between \"2015-06-18\" and \"2015-06-30\") or (substr((`sync_gateway`.`t`), 0, 10) between \"2015-03-01\" and \"2015-03-31\"))) and ((`sync_gateway`.`did`) = \"DF0EA83DA465D7A34FC8BDCF8BD54B1D\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT * FROM sync_gateway WHERE type=\"bcn_scan\" AND (\nSUBSTR(t, 0, 10) BETWEEN \"2015-06-18\" AND \"2015-06-30\" OR\nSUBSTR(t, 0, 10) BETWEEN \"2015-03-01\" AND \"2015-03-31\") AND\ndid=\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\";"
}
],
"status": "success",
"metrics": {
"elapsedTime": "26.685666ms",
"executionTime": "26.595777ms",
"resultCount": 1,
"resultSize": 3799
}
}
I use docker which I am not quite familiar with, so I am not sure how to create a container with a dev build.
And I am running real limited on resources, so, it’s not an option to build a scrap build on a different “physical computer”