Poor query performance

jfaria · July 4, 2020, 1:43pm

Hi,
I have the following query which is performing very poorly. 1m on a 300k documents bucket. Any suggestion on how I can improve this query performance? Thanks in advance

SELECT DISTINCT entity.id
FROM PCS AS entity
INNER JOIN PCS AS node ON entity.id=node.entityId
WHERE entity.type = “aggregation”
AND entity.deleted = FALSE
AND entity.parentIdentifier IN(
SELECT RAW CASE WHEN entity.createdBy = “ef847d76-1ce3-4fc8-908d-37f403dd5107” THEN(
SELECT DISTINCT RAW “ef847d76-1ce3-4fc8-908d-37f403dd5107”
LIMIT 1) ELSE(
SELECT DISTINCT RAW pcs2.id
FROM PCS AS pcs2 USE INDEX(adv_rootNodesInnerQuery USING GSI)
WHERE pcs2.type = “aggregation”
AND pcs2.deleted = FALSE
AND pcs2.isVisibleForCreator = FALSE
AND pcs2.isRoot = TRUE) END)[0]
AND node.entityType = “aggregation”
AND node.deleted = FALSE
AND node.type = “node”
AND(node.userOrGroupIdentifier = “ef847d76-1ce3-4fc8-908d-37f403dd5107”
OR ANY item IN node.accessControlList SATISFIES item.userOrGroupIdentifier IN [“3b4aa12a-271e-4d73-ac5b-d93cf5d6bf9e”,“ef847d76-1ce3-4fc8-908d-37f403dd5107”] END)
ORDER BY entity.createdTimestamp DESC
LIMIT 10
OFFSET 0

The following indexes are created:

CREATE INDEX adv_aggregationsRootNodes ON PCS(parentIdentifier,length,description,classIdentifier,id,title,extension,createdBy,createdTimestamp) WHERE ((type = “aggregation”) and (deleted = false)) WITH { “num_replica”:2 }

CREATE INDEX adv_rootNodesInnerQuery ON PCS(isVisibleForCreator,isRoot,id) WHERE ((type = “aggregation”) and (deleted = false)) WITH { “num_replica”:2 }

The query execution plan is the following:
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “5.309µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “6.357µs”,
“servTime”: “6.626463ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:PCS”,
“Priv”: 7
},
{
“Target”: “:PCS”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “5.631µs”
},
“~children”: [
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “3.067µs”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 10312,
“#phaseSwitches”: 41251,
“execTime”: “413.823151ms”,
“kernTime”: “1m28.927496769s”,
“servTime”: “67.583372ms”
},
“as”: “entity”,
“covers”: [
“cover ((entity.parentIdentifier))”,
“cover ((entity.length))”,
“cover ((entity.description))”,
“cover ((entity.classIdentifier))”,
“cover ((entity.id))”,
“cover ((entity.title))”,
“cover ((entity.extension))”,
“cover ((entity.createdBy))”,
“cover ((entity.createdTimestamp))”,
“cover ((meta(entity).id))”
],
“filter_covers”: {
“cover ((entity.deleted))”: false,
“cover ((entity.type))”: “aggregation”
},
“index”: “adv_aggregationsRootNodes”,
“index_id”: “e8dea70c5104927d”,
“index_projection”: {
“entry_keys”: [
0,
4,
7,
8
],
“primary_key”: true
},
“keyspace”: “PCS”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:00.4814”,
“#time_absolute”: 0.481406523
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.335µs”
},
“~children”: [
{
“#operator”: “NestedLoopJoin”,
“#stats”: {
“#itemsIn”: 10312,
“#itemsOut”: 61453,
“#phaseSwitches”: 307687,
“execTime”: “7.220284398s”,
“kernTime”: “1m27.631390837s”
},
“alias”: “node”,
“on_clause”: “(cover ((entity.id)) = (node.entityId))”,
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 20624,
“execTime”: “7.763623813s”,
“kernTime”: “6.711876ms”,
“state”: “running”
},
“~children”: [
{
“#operator”: “UnionScan”,
“#stats”: {
“#itemsIn”: 61453,
“#itemsOut”: 61453,
“#phaseSwitches”: 317996,
“execTime”: “713.273962ms”,
“kernTime”: “59.784798167s”
},
“scans”: [
{
“#operator”: “DistinctScan”,
“#stats”: {
“#phaseSwitches”: 72184,
“execTime”: “86.840332ms”,
“kernTime”: “40.644700899s”
},
“scan”: {
“#operator”: “IndexScan3”,
“#stats”: {
“#phaseSwitches”: 51560,
“execTime”: “550.124813ms”,
“kernTime”: “29.481555ms”,
“servTime”: “39.97419755s”
},
“as”: “node”,
“index”: “adv_RootNodes”,
“index_id”: “b850e72d9ececfe9”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “PCS”,
“namespace”: “default”,
“nested_loop”: true,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““aggregation””,
“inclusion”: 3,
“low”: ““aggregation””
},
{
“high”: “cover ((entity.id))”,
“inclusion”: 3,
“low”: “cover ((entity.id))”
},
{
“high”: "“ef847d76-1ce3-4fc8-908d-37f403dd5107"”,
“inclusion”: 3,
“low”: "“ef847d76-1ce3-4fc8-908d-37f403dd5107"”
}
]
}
],
“using”: “gsi”
}
},
{
“#operator”: “DistinctScan”,
“#stats”: {
“#itemsIn”: 1274965,
“#itemsOut”: 61453,
“#phaseSwitches”: 2745020,
“execTime”: “1.609574574s”,
“kernTime”: “51.099061064s”
},
“scan”: {
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 1274965,
“#phaseSwitches”: 5151420,
“execTime”: “5.291813984s”,
“kernTime”: “823.987998ms”,
“servTime”: “45.724058183s”
},
“as”: “node”,
“index”: “adv_RootNodes”,
“index_id”: “b850e72d9ececfe9”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “PCS”,
“namespace”: “default”,
“nested_loop”: true,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““aggregation””,
“inclusion”: 3,
“low”: ““aggregation””
},
{
“high”: “cover ((entity.id))”,
“inclusion”: 3,
“low”: “cover ((entity.id))”
}
]
}
],
“using”: “gsi”
}
}
]
},
{
“#operator”: “Fetch”,
“#stats”: {
“#itemsIn”: 61453,
“#itemsOut”: 61453,
“#phaseSwitches”: 300546,
“execTime”: “866.08225ms”,
“kernTime”: “59.421634011s”,
“servTime”: “26.819928683s”
},
“as”: “node”,
“keyspace”: “PCS”,
“namespace”: “default”,
“nested_loop”: true
}
]
},
“#time_normal”: “00:07.2202”,
“#time_absolute”: 7.220284398
},
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 61453,
“#phaseSwitches”: 122909,
“execTime”: “1m12.985524545s”,
“kernTime”: “21.898704656s”
},
“condition”: “(((((((cover ((entity.type)) = “aggregation”) and (cover ((entity.deleted)) = false)) and (cover ((entity.parentIdentifier)) in (correlated (select raw case when (cover ((entity.createdBy)) = “ef847d76-1ce3-4fc8-908d-37f403dd5107”) then (select distinct raw “ef847d76-1ce3-4fc8-908d-37f403dd5107” limit 1) else (select distinct raw cover ((pcs2.id)) from default:PCS as pcs2 where ((((cover ((pcs2.type)) = “aggregation”) and (cover ((pcs2.deleted)) = false)) and (cover ((pcs2.isVisibleForCreator)) = false)) and (cover ((pcs2.isRoot)) = true))) end)[0]))) and ((node.entityType) = “aggregation”)) and ((node.deleted) = false)) and ((node.type) = “node”)) and (((node.userOrGroupIdentifier) = “ef847d76-1ce3-4fc8-908d-37f403dd5107”) or any item in (node.accessControlList) satisfies ((item.userOrGroupIdentifier) in [“3b4aa12a-271e-4d73-ac5b-d93cf5d6bf9e”, “ef847d76-1ce3-4fc8-908d-37f403dd5107”]) end))”,
“#time_normal”: “01:12.9855”,
“#time_absolute”: 72.985524545
},
{
“#operator”: “InitialProject”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “8.047µs”,
“kernTime”: “1m34.884236745s”
},
“distinct”: true,
“result_terms”: [
{
“expr”: “cover ((entity.id))”
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000008047000000000001
},
{
“#operator”: “Distinct”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “5.256µs”,
“kernTime”: “1m34.884253502s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000005256
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000004335
},
{
“#operator”: “Distinct”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “6.013µs”,
“kernTime”: “1m34.884268346s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000006013
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000003067
},
{
“#operator”: “Order”,
“#stats”: {
“#phaseSwitches”: 5,
“execTime”: “73.551µs”,
“kernTime”: “1m34.884284405s”
},
“limit”: “10”,
“sort_terms”: [
{
“desc”: true,
“expr”: “cover ((entity.createdTimestamp))”
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000073551
},
{
“#operator”: “Limit”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “1.988µs”
},
“expr”: “10”,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000001988
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “931ns”
},
“#time_normal”: “00:00”,
“#time_absolute”: 0
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000005631
},
“#time_normal”: “00:00.0066”,
“#time_absolute”: 0.00663282
},
{
“#operator”: “Stream”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “10.288µs”,
“kernTime”: “1m34.891013582s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000010288
}
],
“~versions”: [
“2.0.0-N1QL”,
“6.0.1-2037-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000005309
}

vsr1 · July 4, 2020, 3:09pm

CREATE INDEX ix1 ON PCS(createdTimestamp DESC, parentIdentifier, id, createdBy) WHERE ((type = "aggregation") and (deleted = false)) WITH { "num_replica":2 }
CREATE INDEX ix3 ON PCS(userOrGroupIdentifier,  entityId) WHERE (entityType = "aggregation" AND (type = "node") and (deleted = false)) WITH { "num_replica":2 }
CREATE INDEX ix4 ON PCS(DISTINCT ARRAY item.userOrGroupIdentifier FOR item IN userOrGroupIdentifier END,  entityId) WHERE (entityType = "aggregation" AND (type = "node") and (deleted = false)) WITH { "num_replica":2 }


SELECT DISTINCT entity.id
FROM PCS AS entity
INNER JOIN PCS AS node ON entity.id = node.entityId
WHERE entity.type = "aggregation"
      AND entity.deleted = FALSE
      AND entity.createdTimestamp IS NOT MISSING
      AND entity.parentIdentifier IN ( CASE WHEN entity.createdBy = "ef847d76-1ce3-4fc8-908d-37f403dd5107"
                                            THEN [entity.createdBy]
                                            ELSE ( SELECT DISTINCT RAW pcs2.id
                                                   FROM PCS AS pcs2 USE INDEX(adv_rootNodesInnerQuery USING GSI)
                                                   WHERE pcs2.type = "aggregation"
                                                         AND pcs2.deleted = FALSE
                                                         AND pcs2.isVisibleForCreator = FALSE
                                                         AND pcs2.isRoot = TRUE)
                                       END)
      AND node.entityType = "aggregation"
      AND node.deleted = FALSE
      AND node.type = "node"
      AND (node.userOrGroupIdentifier = "ef847d76-1ce3-4fc8-908d-37f403dd5107"
      OR ANY item IN node.accessControlList
             SATISFIES item.userOrGroupIdentifier IN ["3b4aa12a-271e-4d73-ac5b-d93cf5d6bf9e","ef847d76-1ce3-4fc8-908d-37f403dd5107"] END)
ORDER BY entity.createdTimestamp DESC
LIMIT 10
OFFSET 0;

Use above index and query
Use HASH JOIN (INNER JOIN PCS HASH(BUILD) AS node)
Change JOIN order

Topic		Replies	Views
N1QL: performance and advanced features SQL++	3	1767	September 6, 2016
N1ql very low performance?! SQL++ query , n1ql , dot-net	2	1782	March 13, 2016
N1Ql query performance with IN clause SQL++	7	2193	January 6, 2020
N1QL Query Performance. What is wrong? Couchbase Server	1	648	February 7, 2019
Poor query performance on Couchbase 5.0.1 CE SQL++ query , n1ql	6	1147	July 23, 2018

Poor query performance

Related topics