N1ql index performance is different on same query

SQL++
, ,
1

Hi i want to count the working status from my array. i wrote 2 queries both calling same index but one query is taking 360 ms and second one is taking 40 ms . The second query seems perfect for me but there is another issue. in my second query it is not counting the all sub array have working status = rack. in each array there would be multiple working status = ‘rack’ . it gives me only distinct working status even i tried both in my index with distinct and All ARRAY but result is same . so is there any way to count all attritbutes with having working status = ‘rack’

here is my index

CREATE INDEX def_index_ins_id_working_status
ON stitchit_data_bucket (date,store_id,ALL ARRAY ins.working_status FOR ins IN invoice_services END)
WHERE type = ‘invoice’

MY QUERY 1 taking 360 ms returns 5 result is fine i have 5 working status but too much time taking

SELECT RAW COUNT(*) FROM stitchit_data_bucket invoice USE INDEX (def_index_ins_id_working_status)
UNNEST invoice.invoice_services as ins
WHERE invoice.date BETWEEN ‘2020-03-01’ AND ‘2020-03-30’
AND invoice.store_id = ‘3221’
AND ins.working_status = ‘rack’
AND invoice.type = ‘invoice’

MY QUERY 2 taking 48 ms returns 2 but return 2 instead it should return 5

SELECT RAW count(*) FROM stitchit_data_bucket
WHERE type = “invoice” AND date BETWEEN ‘2020-03-01’ AND ‘2020-03-30’ AND store_id = ‘3221’ AND
ANY ins IN invoice_services SATISFIES
ins.working_status = ‘rack’ END;

MY doc

{
“id”: “00602000000015”,
“date”: “2020-07-21”,
“store_id”: “60”,
“invoice_services”: [
{
“id”: “0060200000001500”,
“inv_id”: “00602000000015”,
“date”: “2020-07-21”,
“service_id”: “3221190002”,
“service_name”: “Skirt”,
“working_status”: “rack”
}
]
}
],
“type”: “invoice”
}

2

Please checkout https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
when will array index will be used for UNNEST.

  CREATE INDEX ix1 ON  stitchit_data_bucket (store_id ,date , DISTINCT  ARRAY ins.working_status FOR ins IN invoice_services END) 
    WHERE  type = "invoice";

SELECT 
        RAW SUM(ARRAY_COUNT(ARRAY 1 FOR ins IN invoice_services WHEN  ins.working_status = "rack" END) ) 
FROM stitchit_data_bucket
WHERE type = "invoice" AND date BETWEEN "2020-03-01" AND "2020-03-30" AND store_id = "3221"
                     AND ANY ins IN invoice_services SATISFIES ins.working_status = "rack" END;
3

Thanks its working but taking same time as my previous query was taking both takes around 300 ms. i have around 100,000 docs. isnt there any way to bring this time to less then 100 ms. Thanks

SELECT RAW SUM(ARRAY_COUNT(ARRAY 1 FOR ins IN invoice_services WHEN ins.working_status = “rack” END) )
FROM stitchit_data_bucket
WHERE type = “invoice” AND date BETWEEN “2020-03-01” AND “2020-03-30” AND store_id = “3221”
AND ANY ins IN invoice_services SATISFIES ins.working_status = “rack” END;

SELECT RAW COUNT(*) FROM stitchit_data_bucket invoice USE INDEX (def_index_ins_id_working_status)
UNNEST invoice.invoice_services as ins
WHERE invoice.date BETWEEN ‘2020-03-01’ AND ‘2020-03-30’
AND invoice.store_id = ‘3221’
AND ins.working_status = ‘rack’
AND invoice.type = ‘invoice’

4

Use this index. Explore examples https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

CREATE INDEX ix1 ON stitchit_data_bucket ( ALL ARRAY ins.working_status FOR ins IN invoice_services END, store_id ,date) 
WHERE type = "invoice";