I have the below Query - that i want to run for my application.
Can You please suggest covered index for this to give optimal performance.
Data is low and hence we can afford covered indexes
SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
(SELECT p1.*, p.data.displayName AS productName, p.uuid
FROM api_external AS p USE KEYS t3.products[*].product
LET p1 = FIRST {v.env, v.status} FOR v IN t3.products WHEN META(p).id == v.product END) AS products
FROM (SELECT t2.id,
ARRAY_AGG({"product":"product::"||t2.product, t2.env, t2.status}) AS products,
MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
FROM (SELECT t1.id,product,
MIN(t1.env) AS env,
MAX(status) AS status,
MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*
FROM (SELECT m.appName AS name, m.uuid AS id, m.description, m.env, m.fourthParty AS dcrFlag,
m.createdTs AS lastModified, m.productStatus , m.productId , m.status
FROM api_external AS pa
JOIN api_external AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
WHERE pa.type = "partnerApp" AND pa.data.companyId = "<companyID>"
GROUP BY pa.uuid, i1.data.productStatus
LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
i1.data.env, i1.data.productStatus , i1.createdTs,
i1.data.productId , i1.data.status }])[1]
) AS t1
LEFT UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
LET product = IFMISSINGORNULL(t1.productId, ps.name),
status = IFMISSINGORNULL(t1.status, ps.val)
GROUP BY t1.id, product) AS t2
GROUP BY t2.id) AS t3;