I have a query which is constantly giving the different results. Here it is:
SELECT DISTINCT acc.id, acc.name, acc.locked
FROM users org USE KEYS 'org:11111'
UNNEST org.accounts AS acc
LEFT OUTER JOIN collections c
ON (c.type = 'collection'
OR (c.type = 'project-assignment'
AND ANY module IN org.modules SATISFIES module = 'Projects' END))
AND c.accountId = acc.id
AND (
(c.type = 'collection'
AND ANY owner IN c.owners SATISFIES owner.userId = 2222 END)
OR ANY u IN c.users SATISFIES u.userId = 2222 END)
WHERE ANY a IN acc.assignments SATISFIES a.userId = 2222 END
OR (ANY w IN acc.workspaces SATISFIES (
ANY a IN w.assignments SATISFIES a.userId = 2222 END)
OR c.accountId = acc.id
END)
Sometimes it gives me the correct response. Here’s the result plan for it:
Sometimes it gives me the incorrect response. Here’s the result plan:
The indexes specified in the plan execution are the same. Moreover, the left node that I pointed out on the picture above was fetched by the document’s key and the 145 docs were unnested from the parent doc. Which means that the cause of the problem is not the index nor the query itself.
Where did the other documents go?
- Couchbase version: 6.6
- Plan text for the incorrect result:
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.511µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "3.931µs",
"servTime": "3.528189ms"
},
"privileges": {
"List": [
{
"Target": "default:users",
"Priv": 7
},
{
"Target": "default:collections",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "13.709µs"
},
"~children": [
{
"#operator": "KeyScan",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 3,
"execTime": "6.764µs",
"kernTime": "1.007µs"
},
"keys": "\"org:100\"",
"#time_normal": "00:00.000",
"#time_absolute": 0.000006764
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 9,
"execTime": "14.249µs",
"kernTime": "13.301µs",
"servTime": "1.381727ms"
},
"as": "org",
"keyspace": "users",
"namespace": "default",
"#time_normal": "00:00.001",
"#time_absolute": 0.001395976
},
{
"#operator": "Unnest",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 14,
"#phaseSwitches": 33,
"execTime": "43.932025ms",
"kernTime": "1.419991ms"
},
"as": "acc",
"expr": "(`org`.`accounts`)",
"#time_normal": "00:00.043",
"#time_absolute": 0.043932025
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.705µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 3,
"#phaseSwitches": 25,
"execTime": "546.267µs",
"kernTime": "51.152057ms"
},
"alias": "c",
"on_clause": "(((((`c`.`type`) = \"collection\") or (((`c`.`type`) = \"project-assignment\") and any `module` in (`org`.`modules`) satisfies (`module` = \"Projects\") end)) and ((`c`.`accountId`) = (`acc`.`id`))) and ((((`c`.`type`) = \"collection\") and any `owner` in (`c`.`owners`) satisfies ((`owner`.`userId`) = 2052) end) or any `u` in (`c`.`users`) satisfies ((`u`.`userId`) = 2052) end))",
"outer": true,
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 4,
"execTime": "987.254µs",
"kernTime": "1.266µs",
"state": "running"
},
"~children": [
{
"#operator": "UnionScan",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 30,
"execTime": "79.23µs",
"kernTime": "5.399087ms"
},
"scans": [
{
"#operator": "DistinctScan",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 18,
"execTime": "110.789µs",
"kernTime": "2.103454ms"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 14,
"execTime": "14.78µs",
"kernTime": "2.515µs",
"servTime": "2.0604ms"
},
"as": "c",
"index": "collection-owners",
"index_id": "9cdf67d8f32498da",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`acc`.`id`)",
"inclusion": 3,
"low": "(`acc`.`id`)"
},
{
"high": "2052",
"inclusion": 3,
"low": "2052"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"#stats": {
"#phaseSwitches": 14,
"execTime": "65.678µs",
"kernTime": "928.699µs"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#phaseSwitches": 10,
"execTime": "25.944µs",
"kernTime": "2.148µs",
"servTime": "889.864µs"
},
"as": "c",
"index": "collection-users",
"index_id": "e1409ef0a796b7b5",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`acc`.`id`)",
"inclusion": 3,
"low": "(`acc`.`id`)"
},
{
"high": "2052",
"inclusion": 3,
"low": "2052"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "IntersectScan",
"#stats": {
"#phaseSwitches": 18,
"execTime": "110.523µs",
"kernTime": "836.956µs"
},
"scans": [
{
"#operator": "DistinctScan",
"#stats": {
"#phaseSwitches": 10,
"execTime": "20.695µs",
"kernTime": "370.902µs"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#phaseSwitches": 6,
"execTime": "6.525µs",
"kernTime": "1.279µs",
"servTime": "365.343µs"
},
"as": "c",
"index": "collection-owners",
"index_id": "9cdf67d8f32498da",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`acc`.`id`)",
"inclusion": 3,
"low": "(`acc`.`id`)"
},
{
"high": "2052",
"inclusion": 3,
"low": "2052"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "IndexScan3",
"#stats": {
"#phaseSwitches": 10,
"execTime": "9.383µs",
"kernTime": "1.929µs",
"servTime": "789.746µs"
},
"as": "c",
"index": "project-assignment-all",
"index_id": "533e5742e43bdc36",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "null",
"inclusion": 0,
"low": "null"
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "DistinctScan",
"#stats": {
"#itemsIn": 1,
"#itemsOut": 1,
"#phaseSwitches": 18,
"execTime": "55.256µs",
"kernTime": "2.020807ms"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1,
"#phaseSwitches": 14,
"execTime": "50.843µs",
"kernTime": "3.429µs",
"servTime": "1.946087ms"
},
"as": "c",
"index": "project-assignment-users",
"index_id": "bdd7afcf9e1e8f1d",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`acc`.`id`)",
"inclusion": 3,
"low": "(`acc`.`id`)"
},
{
"high": "2052",
"inclusion": 3,
"low": "2052"
}
]
}
],
"using": "gsi"
}
}
]
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 16,
"execTime": "35.153µs",
"kernTime": "5.485039ms",
"servTime": "228.779µs"
},
"as": "c",
"keyspace": "collections",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000546267
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 3,
"#phaseSwitches": 15,
"execTime": "831.908µs",
"kernTime": "51.605565ms"
},
"condition": "(any `a` in (`acc`.`assignments`) satisfies ((`a`.`userId`) = 2052) end or any `w` in (`acc`.`workspaces`) satisfies (any `a` in (`w`.`assignments`) satisfies ((`a`.`userId`) = 2052) end or ((`c`.`accountId`) = (`acc`.`id`))) end)",
"#time_normal": "00:00.000",
"#time_absolute": 0.000831908
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 3,
"#phaseSwitches": 15,
"execTime": "37.108µs",
"kernTime": "52.424136ms"
},
"distinct": true,
"result_terms": [
{
"expr": "(`acc`.`id`)"
},
{
"expr": "(`acc`.`name`)"
},
{
"expr": "(`acc`.`locked`)"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000037108
},
{
"#operator": "Distinct",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 2,
"#phaseSwitches": 13,
"execTime": "82.464µs",
"kernTime": "52.417872ms"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000082464
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 7,
"execTime": "3.338µs",
"kernTime": "2.123µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000003338
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000003705
},
{
"#operator": "Distinct",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 11,
"execTime": "38.183µs",
"kernTime": "52.499926ms"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000038183
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000013709
},
"#time_normal": "00:00.003",
"#time_absolute": 0.0035321199999999997
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 7,
"execTime": "39.737µs",
"kernTime": "56.071865ms"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000039737
}
],
"~versions": [
"6.5.0-N1QL",
"6.6.0-7909-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000002511
}