Indexes not used in OR query

I found another instance where indexes are not used in N1QL

When I run query A, indexes are used. In query B where I use an OR, the indexes are not used.

Query A

select  entities_service.entity.clientid
from    entities_service 
where   entities_service.entity.objectid = 552909938 

Query B

select  entities_service.entity.clientid
from    entities_service 
where   entities_service.entity.objectid = 552909938  
or entities_service.entity.objectid = 552909939 

Explain A

{
  "resultset": [
    {
      "input": {
        "expr": {
          "left": {
            "left": {
              "left": {
                "path": "entities_service",
                "type": "property"
              },
              "right": {
                "path": "entity",
                "type": "property"
              },
              "type": "dot_member"
            },
            "right": {
              "path": "objectid",
              "type": "property"
            },
            "type": "dot_member"
          },
          "right": {
            "type": "literal_number",
            "value": 552909938
          },
          "type": "equals"
        },
        "input": {
          "as": "entities_service",
          "bucket": "entities_service",
          "input": {
            "bucket": "entities_service",
            "index": "objectid",
            "pool": "default",
            "ranges": [
              {
                "high": [
                  552909938
                ],
                "inclusion": "both",
                "low": [
                  552909938
                ]
              }
            ],
            "type": "scan"
          },
          "pool": "default",
          "projection": null,
          "type": "fetch"
        },
        "type": "filter"
      },
      "result": [
        {
          "as": "clientid",
          "expr": {
            "left": {
              "left": {
                "path": "entities_service",
                "type": "property"
              },
              "right": {
                "path": "entity",
                "type": "property"
              },
              "type": "dot_member"
            },
            "right": {
              "path": "clientid",
              "type": "property"
            },
            "type": "dot_member"
          },
          "star": false
        }
      ],
      "type": "projector"
    }
  ]
}

Explain B

{
  "resultset": [
    {
      "input": {
        "expr": {
          "operands": [
            {
              "left": {
                "left": {
                  "left": {
                    "path": "entities_service",
                    "type": "property"
                  },
                  "right": {
                    "path": "entity",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "path": "objectid",
                  "type": "property"
                },
                "type": "dot_member"
              },
              "right": {
                "type": "literal_number",
                "value": 552909938
              },
              "type": "equals"
            },
            {
              "left": {
                "left": {
                  "left": {
                    "path": "entities_service",
                    "type": "property"
                  },
                  "right": {
                    "path": "entity",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "path": "objectid",
                  "type": "property"
                },
                "type": "dot_member"
              },
              "right": {
                "type": "literal_number",
                "value": 552909939
              },
              "type": "equals"
            }
          ],
          "type": "or"
        },
        "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": "filter"
      },
      "result": [
        {
          "as": "clientid",
          "expr": {
            "left": {
              "left": {
                "path": "entities_service",
                "type": "property"
              },
              "right": {
                "path": "entity",
                "type": "property"
              },
              "type": "dot_member"
            },
            "right": {
              "path": "clientid",
              "type": "property"
            },
            "type": "dot_member"
          },
          "star": false
        }
      ],
      "type": "projector"
    }
  ]
}

1 Answer

« Back to question.

Thanks for the feedback. At the moment, we match indexes in very basic cases and don't use an index in the case of multiple predicates in the WHERE clause. Something we will look into into future DPs.