Aliasing in queries causes indexes to be ignored

Hi There – I'm wondering if you can clarify something. When I run a query and use aliasing it seems to ignore indexes, and of course runs incredibly slowly. See the two queries below – essentially the same query, but the first does not use aliases, while the second one does. You can see from the explain plan that the un-aliased version uses indexes. Is this by design, and do you plan to change it in the future? I guess as long as we know about it we can work around it.

Runs in 3.26ms
SELECT entities_service.entity FROM entities_service WHERE entities_service.entity.id = 552394365 and entities_service.entity.JSONType="Account" AND ANY property.propertyid=10347 OVER property in entities_service.entity.data.properties END AND ANY producergroups.producergroupid=444 OVER producergroups IN entities_service.entity.security.producergroups END

EXPLAIN:

{
  "resultset": [
    {
      "input": {
        "expr": {
          "operands": [
            {
              "operands": [
                {
                  "left": {
                    "left": {
                      "left": {
                        "path": "entities_service",
                        "type": "property"
                      },
                      "right": {
                        "path": "entity",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "path": "id",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "right": {
                    "type": "literal_number",
                    "value": 552394365
                  },
                  "type": "equals"
                },
                {
                  "left": {
                    "left": {
                      "left": {
                        "path": "entities_service",
                        "type": "property"
                      },
                      "right": {
                        "path": "entity",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "path": "JSONType",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "right": {
                    "type": "literal_string",
                    "value": "Account"
                  },
                  "type": "equals"
                },
                {
                  "as": "property",
                  "condition": {
                    "left": {
                      "left": {
                        "path": "property",
                        "type": "property"
                      },
                      "right": {
                        "path": "propertyid",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "type": "literal_number",
                      "value": 10347
                    },
                    "type": "equals"
                  },
                  "output": null,
                  "over": {
                    "left": {
                      "left": {
                        "left": {
                          "path": "entities_service",
                          "type": "property"
                        },
                        "right": {
                          "path": "entity",
                          "type": "property"
                        },
                        "type": "dot_member"
                      },
                      "right": {
                        "path": "data",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "path": "properties",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "type": "any"
                }
              ],
              "type": "and"
            },
            {
              "as": "producergroups",
              "condition": {
                "left": {
                  "left": {
                    "path": "producergroups",
                    "type": "property"
                  },
                  "right": {
                    "path": "producergroupid",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "type": "literal_number",
                  "value": 444
                },
                "type": "equals"
              },
              "output": null,
              "over": {
                "left": {
                  "left": {
                    "left": {
                      "path": "entities_service",
                      "type": "property"
                    },
                    "right": {
                      "path": "entity",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "right": {
                    "path": "security",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "path": "producergroups",
                  "type": "property"
                },
                "type": "dot_member"
              },
              "type": "any"
            }
          ],
          "type": "and"
        },
        "input": {
          "as": "entities_service",
          "bucket": "entities_service",
          "input": {
            "bucket": "entities_service",
            "index": "entityid",
            "pool": "default",
            "ranges": [
              {
                "high": [
                  552394365
                ],
                "inclusion": "both",
                "low": [
                  552394365
                ]
              }
            ],
            "type": "scan"
          },
          "pool": "default",
          "projection": null,
          "type": "fetch"
        },
        "type": "filter"
      },
      "result": [
        {
          "as": "entity",
          "expr": {
            "left": {
              "path": "entities_service",
              "type": "property"
            },
            "right": {
              "path": "entity",
              "type": "property"
            },
            "type": "dot_member"
          },
          "star": false
        }
      ],
      "type": "projector"
    }
  ]
}

Essentially the same query but using aliasing

Runs in several seconds
SELECT e FROM entities_service.entity AS e WHERE e.id = 552394365 and e.JSONType="Account" AND ANY property.propertyid=10347 OVER property in e.data.properties END AND ANY producergroups.producergroupid=444 OVER producergroups IN e.security.producergroups END

EXPLAIN

{
  "resultset": [
    {
      "input": {
        "expr": {
          "operands": [
            {
              "operands": [
                {
                  "left": {
                    "left": {
                      "path": "e",
                      "type": "property"
                    },
                    "right": {
                      "path": "id",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "right": {
                    "type": "literal_number",
                    "value": 552394365
                  },
                  "type": "equals"
                },
                {
                  "left": {
                    "left": {
                      "path": "e",
                      "type": "property"
                    },
                    "right": {
                      "path": "JSONType",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "right": {
                    "type": "literal_string",
                    "value": "Account"
                  },
                  "type": "equals"
                },
                {
                  "as": "property",
                  "condition": {
                    "left": {
                      "left": {
                        "path": "property",
                        "type": "property"
                      },
                      "right": {
                        "path": "propertyid",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "type": "literal_number",
                      "value": 10347
                    },
                    "type": "equals"
                  },
                  "output": null,
                  "over": {
                    "left": {
                      "left": {
                        "path": "e",
                        "type": "property"
                      },
                      "right": {
                        "path": "data",
                        "type": "property"
                      },
                      "type": "dot_member"
                    },
                    "right": {
                      "path": "properties",
                      "type": "property"
                    },
                    "type": "dot_member"
                  },
                  "type": "any"
                }
              ],
              "type": "and"
            },
            {
              "as": "producergroups",
              "condition": {
                "left": {
                  "left": {
                    "path": "producergroups",
                    "type": "property"
                  },
                  "right": {
                    "path": "producergroupid",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "type": "literal_number",
                  "value": 444
                },
                "type": "equals"
              },
              "output": null,
              "over": {
                "left": {
                  "left": {
                    "path": "e",
                    "type": "property"
                  },
                  "right": {
                    "path": "security",
                    "type": "property"
                  },
                  "type": "dot_member"
                },
                "right": {
                  "path": "producergroups",
                  "type": "property"
                },
                "type": "dot_member"
              },
              "type": "any"
            }
          ],
          "type": "and"
        },
        "input": {
          "as": "e",
          "bucket": "entities_service",
          "input": {
            "bucket": "entities_service",
            "index": "#alldocs",
            "pool": "default",
            "ranges": null,
            "type": "scan"
          },
          "pool": "default",
          "projection": {
            "path": "entity",
            "type": "property"
          },
          "type": "fetch"
        },
        "type": "filter"
      },
      "result": [
        {
          "as": "e",
          "expr": {
            "path": "e",
            "type": "property"
          },
          "star": false
        }
      ],
      "type": "projector"
    }
  ]
}

1 Answer

« Back to question.

Hi,

Thanks for submitting this. We've created the following issue:

http://www.couchbase.com/issues/browse/MB-9316