Aggregate function and group by

SQL++
,
1

Hi, i dont know whats worng with my query its fetching the data correctly but taking time. created the indexes from advisory tool and some from my own but still didnt work Please help thanks

CREATE INDEX adv_all_invoice_services_type_cus_id ON
stitchit_data_bucket(ALL invoice_services,type,cus_id)

CREATE INDEX def_cus_id_invoice ON stitchit_data_bucket(cus_id) WHERE (type = ‘invoice’)

MY QUERY
SELECT
COUNT(ins.qty) as qty ,
MIN(ins.service_id) as id,
MIN(ins.service_name) as name,
SUM(ins.total_bill) as total_amount
FROM stitchit_data_bucket AS inv
UNNEST inv.invoice_services AS ins
WHERE inv.cus_id = ‘024420000000’ AND inv.type = ‘invoice’
GROUP BY ins.service_id

2

drop index adv_all_invoice_services_type_cus_id .
If needed USE INDEX (def_cus_id_invoice)

3

i removed adv_all_invoice_services_type_cus_id but still its taking around 700ms to fetch is this normal ?

4

It depends on your resources, data. Query need to do Fetch, Unnest, aggregation. You can use monitoring/profiling troubleshoot.