I don’t think changing the name should make any difference.
I deleted the primary index on ‘stats’ and also the ‘day_utc_index’ on ‘stats’ using the Web UI. When I dropped the primary index on ‘stats’ from N1QL it seemed to succeed, but the Web UI still showed it. I then used the delete button to also delete it in the Web UI. I was not able to use DROP INDEX to delete the ‘day_utc_index’ from N1QL. It gave the error that the index did not exist. However, I was able to use the delete button in the Web UI to delete it.
I then created only the ‘day_utc_index’ from N1QL:
create index day_utc_index on stats (day_utc) using view;
without creating the primary index. When I select system:indexes it shows up now. The explain now shows it is using the ‘day_utc_index’
n1ql> explain select day_utc, count(*) as count from stats where day_utc is not null group by day_utc;
{
"requestID": "406f7f9b-7d6c-4c3e-9cb7-fdfaa2da3e93",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "day_utc_index",
"keyspace": "stats",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"High": null,
"Inclusion": 0,
"Low": [
"null"
]
},
"Seek": null
}
],
"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": "19.233442ms",
"executionTime": "18.911526ms",
"resultCount": 1,
"resultSize": 3366
}
}
but it still takes 8 seconds to complete the query, while being very fast (seemingly under a second) when I use the index through the Web UI.
BTW, if I don’t have the WHERE clause in the SELECT query I get the message: “No primary index on keyspace stats. Use CREATE PRIMARY INDEX to create one.”. So it seems that the WHERE clause somehow causes it to use the ‘day_utc_index’ and not complain.