Count(indexedfield) doesnt use index


#1

We tried to perform a count(*) on an indexed field today and it seems to be using a full #alldocs scan instead.

create index objectid on entities_service(entity.objectid)
explain select count(entities_service.entity.objectid) from entities_service

{ "resultset": [ { "input": { "aggregates": [ { "distinct": false, "name": "COUNT", "operands": [ { "expr": { "left": { "left": { "path": "entities_service", "type": "property" }, "right": { "path": "entity", "type": "property" }, "type": "dot_member" }, "right": { "path": "objectid", "type": "property" }, "type": "dot_member" }, "star": false } ], "type": "function" } ], "group": [], "input": { "as": "entities_service", "bucket": "entities_service", "input": { "bucket": "entities_service", "index": "#alldocs", "pool": "default", "ranges": null, "type": "scan" }, "pool": "default", "projection": null, "type": "fetch" }, "type": "grouper" }, "result": [ { "as": "$1", "expr": { "distinct": false, "name": "COUNT", "operands": [ { "expr": { "left": { "left": { "path": "entities_service", "type": "property" }, "right": { "path": "entity", "type": "property" }, "type": "dot_member" }, "right": { "path": "objectid", "type": "property" }, "type": "dot_member" }, "star": false } ], "type": "function" }, "star": false } ], "type": "projector" } ] }

#2

Hi,

Could you try:

create index objectid on entities_service(entity.objectid)

explain select count(entity.objectid) from entities_service

I changed the argument to COUNT() above.


#3

I tried that - no change. However, I did find something:

When you execute A it does a table scan, however in B, when you add a where clause, it uses the index. However, B is considerably slower so does not actually help at all.

Query A - Indexes Not Used ~6s to return for 2000 rows.
explain select count(entities_service.entity.objectid) from entities_service

Query B - Indexes Used ~10s to return for 2000 rows.
explain select count(entities_service.entity.objectid) from entities_service where entities_service.entity.objectid IS NOT NULL

Explain A - Indexes not used
{
“resultset”: [
{
“input”: {
“aggregates”: [
{
“distinct”: false,
“name”: “COUNT”,
“operands”: [
{
“expr”: {
“left”: {
“left”: {
“path”: “entities_service”,
“type”: “property”
},
“right”: {
“path”: “entity”,
“type”: “property”
},
“type”: “dot_member”
},
“right”: {
“path”: “objectid”,
“type”: “property”
},
“type”: “dot_member”
},
“star”: false
}
],
“type”: “function”
}
],
“group”: [],
“input”: {
“as”: “entities_service”,
“bucket”: “entities_service”,
“input”: {
“bucket”: “entities_service”,
“index”: “#alldocs”,
“pool”: “default”,
“ranges”: null,
“type”: “scan”
},
“pool”: “default”,
“projection”: null,
“type”: “fetch”
},
“type”: “grouper”
},
“result”: [
{
“as”: “$1”,
“expr”: {
“distinct”: false,
“name”: “COUNT”,
“operands”: [
{
“expr”: {
“left”: {
“left”: {
“path”: “entities_service”,
“type”: “property”
},
“right”: {
“path”: “entity”,
“type”: “property”
},
“type”: “dot_member”
},
“right”: {
“path”: “objectid”,
“type”: “property”
},
“type”: “dot_member”
},
“star”: false
}
],
“type”: “function”
},
“star”: false
}
],
“type”: “projector”
}
]
}

Explain B - Indexes Used
{
“resultset”: [
{
“input”: {
“aggregates”: [
{
“distinct”: false,
“name”: “COUNT”,
“operands”: [
{
“expr”: {
“left”: {
“left”: {
“path”: “entities_service”,
“type”: “property”
},
“right”: {
“path”: “entity”,
“type”: “property”
},
“type”: “dot_member”
},
“right”: {
“path”: “objectid”,
“type”: “property”
},
“type”: “dot_member”
},
“star”: false
}
],
“type”: “function”
}
],
“group”: [],
“input”: {
“expr”: {
“operand”: {
“left”: {
“left”: {
“path”: “entities_service”,
“type”: “property”
},
“right”: {
“path”: “entity”,
“type”: “property”
},
“type”: “dot_member”
},
“right”: {
“path”: “objectid”,
“type”: “property”
},
“type”: “dot_member”
},
“type”: “is_not_null”
},
“input”: {
“as”: “entities_service”,
“bucket”: “entities_service”,
“input”: {
“bucket”: “entities_service”,
“index”: “objectid2”,
“pool”: “default”,
“ranges”: [
{
“inclusion”: “high”,
“low”: [
null
]
}
],
“type”: “scan”
},
“pool”: “default”,
“projection”: null,
“type”: “fetch”
},
“type”: “filter”
},
“type”: “grouper”
},
“result”: [
{
“as”: “$1”,
“expr”: {
“distinct”: false,
“name”: “COUNT”,
“operands”: [
{
“expr”: {
“left”: {
“left”: {
“path”: “entities_service”,
“type”: “property”
},
“right”: {
“path”: “entity”,
“type”: “property”
},
“type”: “dot_member”
},
“right”: {
“path”: “objectid”,
“type”: “property”
},
“type”: “dot_member”
},
“star”: false
}
],
“type”: “function”
},
“star”: false
}
],
“type”: “projector”
}
]
}


#4

Just saw it in the backlog - thanks.


#5

This will be fixed in DP2 in the November timeframe.