Aliasing in queries causes indexes to be ignored


#1

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


#2

Hi,

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

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


#3

Fixed with the new DP2 release.