Count(indexedfield) doesnt use index

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"
    }
  ]
}

1 Answer

« Back to question.

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.

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"
    }
  ]
}

This will be fixed in DP2 in the November timeframe.

Just saw it in the backlog - thanks.