Couchbase Query Performance Issue

SQL++
1

Hi Team,

We had screen UI query which is taking 75s to load. Could you please help us to optimize further.

SELECT DISTINCT d.name,
       CASE WHEN EVERY v IN d.countryname SATISFIES v = -1 END THEN "All countries selected" ELSE d.countryname END AS countryname,
       CASE WHEN EVERY v IN f.networkname SATISFIES v = -1 END THEN "All networks selected" ELSE f.networkname END AS networkname,
       d.hpmn,
       d.status,
       d.used,
       d.enabled,
       d.grptype,
       d.numtype,
       d.negation,
       d.id,
       d.countryid,
       f.networkid,
       d.__by,
       d.__at
FROM(
    SELECT CASE WHEN EVERY v IN ARRAY_FLATTEN(c.cntrynw[*].nwid,2) SATISFIES v = -1 END THEN (
        SELECT DISTINCT f.name,
               TONUMBER(SPLIT(META(f).id, ':')[1]) AS hpmn,
               ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS networkid,
               ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS networkname
        FROM config f
        UNNEST ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS netid
        WHERE f.__t='vf-group-master'
            AND f.status <> 2
            AND EVERY v IN ARRAY_FLATTEN(f.cntrynw[*].nwid,2) SATISFIES v = -1 END) ELSE (
        SELECT DISTINCT b.name,
               TONUMBER(SPLIT(META(b).id, ':')[1]) AS hpmn,
               ARRAY_AGG(networkid) AS networkid,
               REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_FLATTEN(ARRAY_AGG(d.networkname || " (" || d.gsm[0].mcc || "," || d.gsm[0].mnc || ")"), 1)), "\",\"", ","), "[\"",""), "\"]", "") AS networkname
        FROM config b
        UNNEST ARRAY_FLATTEN(b.cntrynw[*].nwid,2) AS networkid
        INNER JOIN `config` AS d USE HASH(BUILD) ON d.networkid = networkid
        WHERE b.__t = 'vf-group-master'
            AND d.__t='irdb-net'
            AND b.status <> 2
        GROUP BY b.name,
                 TONUMBER(SPLIT(META(b).id, ':')[1])) END AS networkdata,
    CASE WHEN EVERY v IN c.cntrynw[*].ctryid SATISFIES v = -1 END THEN (
        SELECT DISTINCT f.name,
               f.status,
               f.used,
               f.__by,
               f.__at,
               TONUMBER(SPLIT(META(f).id, ':')[1]) AS hpmn,
               f.cntrynw[*].ctryid AS countryname,
               f.enabled,
               f.grptype,
               f.numtype,
               f.negation,
               f.cntrynw[*].ctryid AS countryid,
               TONUMBER(SPLIT(META(f).id, ':')[2]) AS id
        FROM config f
        WHERE f.__t='vf-group-master'
            AND status <> 2
            AND EVERY v IN f.cntrynw[*].ctryid SATISFIES v = -1 END) ELSE (
        SELECT DISTINCT b.name,
               b.status,
               b.used,
               TONUMBER(SPLIT(META(b).id, ':')[1]) AS hpmn,
               b.enabled,
               b.grptype,
               b.numtype,
               b.__by,
               b.__at,
               b.negation,
               TONUMBER(SPLIT(META(b).id, ':')[2]) AS id,
               REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_FLATTEN(ARRAY_AGG(DISTINCT d.countryname || " (" || d.gsm[0].cc || ")"), 1)), "\",\"", ","), "[\"",""), "\"]", "") AS countryname,
               ARRAY_AGG(countryid) AS countryid
        FROM config b
        UNNEST b.cntrynw[*].ctryid AS countryid
        INNER JOIN `config` AS d USE HASH(BUILD) ON d.countryid = countryid
        WHERE b.__t = 'vf-group-master'
            AND d.__t='irdb-country'
            AND b.status <> 2
        GROUP BY b.name,
                 b.status,
                 b.used,
                 TONUMBER(SPLIT(META(b).id, ':')[1]),
                 b.enabled,
                 b.grptype,
                 b.numtype,
                 b.negation,
                 b.__by,
                 b.__at,
                 TONUMBER(SPLIT(META(b).id, ':')[2])) END AS countrydata
FROM config c
WHERE c.__t='vf-group-master'
    AND c.status <> 2)m
UNNEST m.countrydata AS d
UNNEST m.networkdata AS f
WHERE f.name= d.name
    AND d.hpmn = f.hpmn

Thanks,
Debasis
plan_test_1602.zip (5.8 KB)

2 
            SELECT DISTINCT f.name,
               TONUMBER(SPLIT(META(f).id, ':')[1]) AS hpmn,
               ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS networkid,
               ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS networkname
        FROM config f
        UNNEST ARRAY_FLATTEN(f.cntrynw[*].nwid,2) AS netid
        WHERE f.__t='vf-group-master'
            AND f.status <> 2
            AND EVERY v IN ARRAY_FLATTEN(f.cntrynw[*].nwid,2) SATISFIES v = -1 END
            AND f.name IS NOT MISSING 
            AND TONUMBER(SPLIT(META(f).id, ':')[1])
                  )

    WITH cte2 AS (

           SELECT DISTINCT b.name,
               TONUMBER(SPLIT(META(b).id, ':')[1]) AS hpmn,
               ARRAY_AGG(networkid) AS networkid,
               REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_FLATTEN(ARRAY_AGG(d.networkname || " (" || d.gsm[0].mcc || "," || d.gsm[0].mnc || ")"), 1)), "\",\"", ","), "[\"",""), "\"]", "") AS networkname
        FROM config b
        UNNEST ARRAY_FLATTEN(b.cntrynw[*].nwid,2) AS networkid
        INNER JOIN `config` AS d USE HASH(BUILD) ON d.networkid = networkid
        WHERE b.__t = 'vf-group-master'
            AND d.__t='irdb-net'
            AND b.status <> 2
        GROUP BY b.name,
                 TONUMBER(SPLIT(META(b).id, ':')[1])
             )


   
    WITH cte3 AS (
            SELECT DISTINCT f.name,
               f.status,
               f.used,
               f.__by,
               f.__at,
               TONUMBER(SPLIT(META(f).id, ':')[1]) AS hpmn,
               f.cntrynw[*].ctryid AS countryname,
               f.enabled,
               f.grptype,
               f.numtype,
               f.negation,
               f.cntrynw[*].ctryid AS countryid,
               TONUMBER(SPLIT(META(f).id, ':')[2]) AS id
        FROM config f
        WHERE f.__t='vf-group-master'
            AND status <> 2
            AND EVERY v IN f.cntrynw[*].ctryid SATISFIES v = -1 END
            )


CREATE INDEX cte2_v1 ON `config`( ARRAY for x in  cntrynw[*].ctryid END ,

{`name`,`status`,`used`,`__by`,`__at`,TONUMBER(SPLIT(META(f).id, ':')[1]), cntrynw[*].ctryid, 
enabled,grptype,numtype,negation,f.cntrynw[*].ctryid,TONUMBER(SPLIT(META(f).id, ':')[2])}

)WHERE `__t` = 'vf-group-master' AND `status` <> 2


    WITH  cte4 AS  (  
                   SELECT 

                   DISTINCT b.name,
                   b.status,
                   b.used,
                   TONUMBER(SPLIT(META(b).id, ':')[1]) AS hpmn,
                   b.enabled,
                   b.grptype,
                   b.numtype,
                   b.__by,
                   b.__at,
                   b.negation,
                   TONUMBER(SPLIT(META(b).id, ':')[2]) AS id,
                   REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_FLATTEN(ARRAY_AGG(DISTINCT d.countryname || " (" || d.gsm[0].cc || ")"), 1)), "\",\"", ","), "[\"",""), "\"]", "") AS countryname,
                   ARRAY_AGG(countryid) AS countryid 
            FROM config b
            UNNEST b.cntrynw[*].ctryid AS countryid
            INNER 
            JOIN `config` AS d USE HASH(BUILD) ON d.countryid = countryid
            WHERE b.__t = 'vf-group-master'
                AND d.__t='irdb-country'
                AND b.status <> 2
            GROUP BY b.name,
                     b.status,
                     b.used,
                     TONUMBER(SPLIT(META(b).id, ':')[1]),
                     b.enabled,
                     b.grptype,
                     b.numtype,
                     b.negation,
                     b.__by,
                     b.__at,
                     TONUMBER(SPLIT(META(b).id, ':')[2])
                 )

????
CREATE INDEX cte4_v1 ON CONFIG(`status`,{TONUMBER(SPLIT(META(b).id, ':')[2]), REPLACE(REPLACE(REPLACE(ENCODE_JSON(ARRAY_FLATTEN(ARRAY_AGG(DISTINCT d.countryname || " (" || d.gsm[0].cc || ")") ,`name`,`used`,`enabled`,`grptype`,`numtype`,`__by`,`__at`,negation}) WHERE `__t` = 'vf-group-master' AND `status` <> 2


SELECT DISTINCT d.name,
       CASE WHEN EVERY v IN d.countryname SATISFIES v = -1 END THEN "All countries selected" ELSE d.countryname END AS countryname,
       CASE WHEN EVERY v IN f.networkname SATISFIES v = -1 END THEN "All networks selected" ELSE f.networkname END AS networkname,
       d.hpmn,
       d.status,
       d.used,
       d.enabled,
       d.grptype,
       d.numtype,
       d.negation,
       d.id,
       d.countryid,
       f.networkid,
       d.__by,
       d.__at
FROM(
    SELECT 
           CASE WHEN EVERY v IN ARRAY_FLATTEN(c.cntrynw[*].nwid,2) SATISFIES v = -1 END THEN (
                  cte1
                ) ELSE (
                  cte2
                ) END AS networkdata,
           CASE WHEN EVERY v IN c.cntrynw[*].ctryid SATISFIES v = -1 END THEN (
                  cte3
                ) ELSE (
                  cte4
                ) END AS countrydata
FROM config c
WHERE c.__t='vf-group-master'
    AND c.status <> 2 ) AS m
UNNEST m.countrydata AS d
UNNEST m.networkdata AS f
WHERE f.name= d.name
    AND d.hpmn = f.hpmn```
Try something like this and for the CTE's go to https://index-advisor.couchbase.com/indexadvisor/#1 to get a better index for them.
3

Thank you @househippo for support. Let me check and update the same.

Thanks,
Debasis