Poor query performance

Couchbase Server
, ,
2 
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;
  1. Use above index and query
  2. Use HASH JOIN (INNER JOIN PCS HASH(BUILD) AS node)
  3. Change JOIN order
1 Like