this is the EXPLAIN before and after the index created.
As further investigation I define te index without the ‘Extent1’ part because this property does not exist in any document, but linq2couchbase defines in generated query.
CREATE INDEX index_odc_pr
ON H2H
(upper((((document
.payload
).H2H_EQUIP
).PERSONRESPONSIBLE
)))
and the query returns correct result.
EXPLAIN SELECT Extent1
.document
.payload
as result
FROM H2H
as Extent1
WHERE (Extent1
.type
= ‘ATCore.ReadModel.ODC_RM’)
AND ((upper((((Extent1
.document
).payload
).H2H_EQUIP
).PERSONRESPONSIBLE
) = ‘FOO’))
{
“requestID”: “df71d79c-05d1-4abf-8c60-9a7dbdc3061d”,
“clientContextID”: “c5010a8e-15e6-4d45-80a2-53fa551eae5f”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “H2H”,
“keyspace”: “H2H”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((Extent1
.type
) = “ATCore.ReadModel.ODC_RM”) and (upper(((((Extent1
.document
).payload
).H2H_EQUIP
).PERSONRESPONSIBLE
)) = “DBROCCHIAD”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1
.document
).payload
)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1
.document
.payload
as result
FROM H2H
as Extent1
\nWHERE (Extent1
.type
= ‘ATCore.ReadModel.ODC_RM’) \nAND ((upper((((Extent1
.document
).payload
).H2H_EQUIP
).PERSONRESPONSIBLE
) = ‘DBROCCHIAD’))”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “2ms”,
“executionTime”: “2ms”,
“resultCount”: 1,
“resultSize”: 2086
}
}
after the index created:
{
“requestID”: “66062f57-b6e9-4f0b-ab22-38d573bea0ff”,
“clientContextID”: “ce865c11-4f0b-420c-8e70-e8a128b27345”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “index_odc_pr”,
“index_id”: “dc02462f9de70905”,
“keyspace”: “H2H”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“FOO”"
],
“Inclusion”: 3,
“Low”: [
"“FOO”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((Extent1
.type
) = “ATCore.ReadModel.ODC_RM”) and (upper(((((Extent1
.document
).payload
).H2H_EQUIP
).PERSONRESPONSIBLE
)) = “FOO”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1
.document
).payload
)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1
.document
.payload
as result
FROM H2H
as Extent1
\nWHERE (Extent1
.type
= ‘ATCore.ReadModel.ODC_RM’) \nAND ((upper((((Extent1
.document
).payload
).H2H_EQUIP
).PERSONRESPONSIBLE
) = ‘FOO’))”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “7.994ms”,
“executionTime”: “6.9821ms”,
“resultCount”: 1,
“resultSize”: 2682
}
}