Thanks @geraldss, but it did not seem to make any difference.
This:
select day_utc, count(*) as count from stats use index(day_utc_index) where day_utc is not null group by day_utc;
still gives the error: “View Index not found day_utc_index”
and this:
select day_utc, count(*) as count from stats where day_utc is not null group by day_utc;
now takes about 9 seconds to execute instead of 8 on the same 75,000 documents.
Here is the output of explain:
n1ql> explain select day_utc, count(*) as count from stats where day_utc is not null group by day_utc;
{
"requestID": "7dc52a76-b87f-4848-a5a6-713b33a4b27a",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "stats",
"namespace": "default",
"using": "view"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "stats",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((`stats`.`day_utc`) is not null)"
},
{
"#operator": "InitialGroup",
"aggregates": [
"count(*)"
],
"group_keys": [
"(`stats`.`day_utc`)"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"count(*)"
],
"group_keys": [
"(`stats`.`day_utc`)"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"count(*)"
],
"group_keys": [
"(`stats`.`day_utc`)"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`stats`.`day_utc`)"
},
{
"as": "count",
"expr": "count(*)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "20.874533ms",
"executionTime": "20.694588ms",
"resultCount": 1,
"resultSize": 2882
}
}
Another data point:
select * from system:indexes;
does not show day_utc_index as one of the indexes.