I have the query below that takes ~30sec to execute. Based on the query plan (attached as well), it looks like it’s spending a lot of time joining against the collections bucket. I’ve tried a few different things eliminate the join, but nothing has panned out so far. Any help would be appreciated.
Query:
currentMap.currentVersionId as assetId,
c.id as collectionId,
ARRAY_CONCAT(
ARRAY { 'id': o.userId } FOR o IN c.owners END,
ARRAY { 'id': u.userId, u.expiresOn } FOR u IN c.users END) as users
FROM (
SELECT
baseAsset.id AS versionId,
baseAsset.version.currentVersionId AS currentVersionId
FROM assets AS baseAsset USE KEYS [
"asset:000b4e3a-be6e-4a14-a544-84c8200c8319",
"asset:0026d148-f313-41dd-92cb-fe7051e47063"...(many more elements)
]
WHERE baseAsset.trashedOn IS NULL OR MISSING
) AS currentMap
JOIN collections AS c ON c.accountId = 1091
WHERE c.type = 'collection'
AND ANY a IN c.assets SATISFIES a.assetId = currentMap.versionId END
Query Plan:
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.292µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "3.297µs",
"servTime": "13.258483ms"
},
"privileges": {
"List": [
{
"Target": "default:assets",
"Priv": 7
},
{
"Target": "default:collections",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.384µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "1.772µs"
},
"~children": [
{
"#operator": "KeyScan",
"#stats": {
"#itemsOut": 1744,
"#phaseSwitches": 3489,
"execTime": "2.283049ms",
"kernTime": "164.828126ms"
},
"keys": "[\"asset:000b4e3a-be6e-4a14-a544-84c8200c8319\", \"asset:0026d148-f313-41dd-92cb-fe7051e47063\"...(many more)]",
"#time_normal": "00:00.0022",
"#time_absolute": 0.002283049
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 7197,
"execTime": "5.242993ms",
"kernTime": "1.430366ms",
"servTime": "220.553994ms"
},
"as": "baseAsset",
"keyspace": "assets",
"namespace": "default",
"#time_normal": "00:00.2257",
"#time_absolute": 0.22579698699999998
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "970ns"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 6979,
"execTime": "26.269193ms",
"kernTime": "876.856987ms"
},
"condition": "(((`baseAsset`.`trashedOn`) is null) or missing)",
"#time_normal": "00:00.0262",
"#time_absolute": 0.026269193
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 5237,
"execTime": "49.47823ms",
"kernTime": "121.823108ms"
},
"result_terms": [
{
"as": "versionId",
"expr": "(`baseAsset`.`id`)"
},
{
"as": "currentVersionId",
"expr": "((`baseAsset`.`version`).`currentVersionId`)"
}
],
"#time_normal": "00:00.0494",
"#time_absolute": 0.049478230000000005
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 5233,
"execTime": "1.155019ms",
"kernTime": "3.694810722s"
},
"#time_normal": "00:00.0011",
"#time_absolute": 0.001155019
}
],
"#time_normal": "00:00",
"#time_absolute": 0
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001772
},
{
"#operator": "Alias",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 6979,
"execTime": "4.326947ms",
"kernTime": "11.607292005s"
},
"as": "currentMap",
"#time_normal": "00:00.0043",
"#time_absolute": 0.004326946999999999
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.023µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 17443,
"execTime": "44.804343ms",
"kernTime": "19.649841608s"
},
"alias": "c",
"on_clause": "((`c`.`accountId`) = 1091)",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 3488,
"execTime": "20.319632007s",
"kernTime": "835.547µs",
"state": "running"
},
"~children": [
{
"#operator": "DistinctScan",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 15696,
"execTime": "66.511807ms",
"kernTime": "1.267558308s"
},
"scan": {
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1744,
"#phaseSwitches": 15696,
"execTime": "23.157266ms",
"kernTime": "3.740378ms",
"servTime": "1.232569979s"
},
"as": "c",
"index": "collection-accountId-assetId",
"index_id": "6df320d187dc1ca0",
"index_projection": {
"primary_key": true
},
"keyspace": "collections",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "1091",
"inclusion": 3,
"low": "1091"
},
{
"high": "(`currentMap`.`versionId`)",
"inclusion": 3,
"low": "(`currentMap`.`versionId`)"
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 15698,
"execTime": "60.085207ms",
"kernTime": "1.356379567s",
"servTime": "5.570769208s"
},
"as": "c",
"keyspace": "collections",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.0448",
"#time_absolute": 0.044804343
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 6979,
"execTime": "27.272037257s",
"kernTime": "34.104655ms"
},
"condition": "(((`c`.`type`) = \"collection\") and any `a` in (`c`.`assets`) satisfies ((`a`.`assetId`) = (`currentMap`.`versionId`)) end)",
"#time_normal": "00:27.2720",
"#time_absolute": 27.272037257
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 5237,
"execTime": "135.061491ms",
"kernTime": "27.163704278s"
},
"result_terms": [
{
"as": "assetId",
"expr": "(`currentMap`.`currentVersionId`)"
},
{
"as": "collectionId",
"expr": "(`c`.`id`)"
},
{
"as": "users",
"expr": "array_concat(array {\"id\": (`o`.`userId`)} for `o` in (`c`.`owners`) end, array {\"expiresOn\": (`u`.`expiresOn`), \"id\": (`u`.`userId`)} for `u` in (`c`.`users`) end)"
}
],
"#time_normal": "00:00.1350",
"#time_absolute": 0.13506149099999998
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 5233,
"execTime": "1.632786ms",
"kernTime": "5.002649ms"
},
"#time_normal": "00:00.0016",
"#time_absolute": 0.001632786
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003023
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002384
},
"#time_normal": "00:00.0132",
"#time_absolute": 0.013261779999999999
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 1744,
"#itemsOut": 1744,
"#phaseSwitches": 6979,
"execTime": "667.124µs",
"kernTime": "27.318815765s"
},
"#time_normal": "00:00.0006",
"#time_absolute": 0.0006671240000000001
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.1-2037-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002292
}