The following very simple query makes the cbq-engine consume 10GB of memory. The data set inside the GROUP statement only returns approx. 4,000 records. The “drg-IDC-9” document is about 212KB in size.
Is it possible that it’s trying to tack on the entire 212KB json doc onto the end of each row returned inside the grouping? If so, is there a better way to write this? Inside the drg-IDC-9 document, there’s an array of codes. What we’re trying to do is join to the document to get the corresponding weight where the visits.drgCode matches the msDrg code in the drg-IDC-9 document.
SELECT SUBSTR(visits.dischargeDate,0,10) As dischargeDate, AVG(FIRST c.weight for c IN codes.drgs WHEN c.msDrg = visits.drgCode END) As avgWeight FROM bucket visits JOIN bucket codes ON KEYS "drg-IDC-9" WHERE meta(visits).id LIKE "visit-%" AND visits.patientClass LIKE "I%" AND visits.tenantName = "xxxxxxxx" AND visits.drgCode != "" AND visits.drgType LIKE "MS%" AND SUBSTR(visits.dischargeDate,0,10) >= "2016-07-01" AND SUBSTR(visits.dischargeDate,0,10) < "2016-08-01" GROUP BY SUBSTR(visits.dischargeDate,0,10)