Slow LEFT NEST performance

SQL++
1

Hello there!

I’m currently working on optimizing a query that involves the usage of LEFT NEST. This query is used to search through the contacts collection, which can have a variety of attribute conditions. Additionally, it may also have conditions related to events that have occurred with these contacts.

The query:

SELECT res.attributes
FROM (
    SELECT c.customerId,
           c.attributes,
           ARRAY v FOR v IN e WHEN v.name='PaymentAccepted'
        AND (v.data.`price`>100
            AND v.data.`currency`='GBP') END AS events_1
    FROM `contacts-pool-5` c LEFT NEST `contact-events-pool-5` e ON c.attributes.nxId = e.contactId
    WHERE c.customerId=$customerId) AS res
WHERE res.customerId=$customerId
    AND (LOWER(res.attributes.`email`) LIKE '%zog.na%'
        AND ARRAY_LENGTH(events_1) >= 1)
LIMIT 10

The execution plan:

type or paste code here
3

You have predicate on right side of LEFT NEST on parent query. So you do not need LEFT NEST.

CREATE INDEX ix1 ON `contacts-pool-5`(customerId, attributes);
CREATE INDEX ix2 ON `contacts-pool-5`(data.currency, contactId, data.price,) WHERE name = "PaymentAccepted";

SELECT c.attributes
FROM `contacts-pool-5` AS c
WHERE c.customerId = $customerId
      AND LOWER(c.attributes.email) LIKE "%zog.na%"
      AND EXISTS (SELECT RAW 1
                  FROM `contact-events-pool-5` AS e
                  WHERE c.attributes.nxId = e.contactId
                        AND e.name = "PaymentAccepted"
                        AND e.data.currency = 'GBP'
                        AND e.data.price > 100)
LIMIT 10;
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.