Issue with N1QL query with GROUP BY

SQL++
,
1

I am having an issue with N1QL query with GROUP BY clause. This query is taking around 30+ seconds and I need some help to optimize it. Currently there are no indexes being used by this query. I am using Couchbase 6.0.1 version.

Please see below sample data and query.

QUERY:

SELECT
sr.attrName,sr.groupColumn, sr.groupValue,
SUM(CASE WHEN sr.statName = "STAT_1" THEN TONUMBER(sr.statResult) ELSE 0 END) AS STAT_1,
SUM(CASE WHEN sr.statName = "STAT_2" THEN TONUMBER(sr.statResult) ELSE 0 END) AS STAT_2,
SUM(CASE WHEN sr.statName = "STAT_3" THEN TONUMBER(sr.statResult) ELSE 0 END) AS STAT_3
FROM
my_bucket attrs
USE KEYS "1234::StatAttrs"
LEFT JOIN my_bucket s
ON KEYS attrs.statAttrs
UNNEST s.statResults sr
GROUP BY sr.attrName, sr.groupColumn, sr.groupValue;

OUTPUT:

[
  {
    "STAT_1": 200,
    "STAT_2": 3,
    "STAT_3": 1,
    "attrName": "col1",
    "groupColumn": "attrib1|attrib2",
    "groupValue": "001|002"
  },
  {
    "STAT_1": 400,
    "STAT_2": 6,
    "STAT_3": 2,
    "attrName": "col1",
    "groupColumn": "attrib1|attrib2",
    "groupValue": "003|004"
  },
  {
    "STAT_1": 20000,
    "STAT_2": 500,
    "STAT_3": 600,
    "attrName": "col2",
    "groupColumn": "attrib1|attrib2",
    "groupValue": "001|002"
  },
  {
    "STAT_1": 10000,
    "STAT_2": 100,
    "STAT_3": 200,
    "attrName": "col2",
    "groupColumn": "attrib1|attrib2",
    "groupValue": "003|004"
  }
]

StatAttrs Sample Doc:

Key:
1234::StatAttrs

Value:
{
“type”: “StatAttrs”,
“id”: 1234,
“statAttrs”: [
“1234::col1::attrib1|attrib2::001|002”,
“1234::col1::attrib1|attrib2::003|004”,
“1234::col2::attrib1|attrib2::001|002”,
“1234::col2::attrib1|attrib2::003|004”
]
}

StatVals Sample Doc 1:

Key:
1234::col1::attrib1|attrib2::001|002

Value:
{
“type”: “StatVals”,
“id”: 1234,
“statResults”: [
{
“attrName”: “col1”,
“statName”: “STAT_1”,
“statResult”: “200.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
},
{
“attrName”: “col1”,
“statName”: “STAT_2”,
“statResult”: “3.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
},
{
“attrName”: “col1”,
“statName”: “STAT_3”,
“statResult”: “1.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
}
]
}

StatVals Sample Doc 2:

Key:
1234::col1::attrib1|attrib2::003|004

Value:
{
“type”: “StatVals”,
“id”: 1234,
“statResults”: [
{
“attrName”: “col1”,
“statName”: “STAT_1”,
“statResult”: “400.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
},
{
“attrName”: “col1”,
“statName”: “STAT_2”,
“statResult”: “6.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
},
{
“attrName”: “col1”,
“statName”: “STAT_3”,
“statResult”: “2.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
}
]
}

StatVals Sample Doc 3:

Key:
1234::col2::attrib1|attrib2::001|002

Value:
{
“type”: “StatVals”,
“id”: 1234,
“statResults”: [
{
“attrName”: “col2”,
“statName”: “STAT_1”,
“statResult”: “20000.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
},
{
“attrName”: “col2”,
“statName”: “STAT_2”,
“statResult”: “500.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
},
{
“attrName”: “col2”,
“statName”: “STAT_3”,
“statResult”: “600.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “001|002”
}
]
}

StatVals Sample Doc 4:

Key:
1234::col2::attrib1|attrib2::003|004

Value:
{
“type”: “StatVals”,
“id”: 1234,
“statResults”: [
{
“attrName”: “col2”,
“statName”: “STAT_1”,
“statResult”: “10000.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
},
{
“attrName”: “col2”,
“statName”: “STAT_2”,
“statResult”: “100.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
},
{
“attrName”: “col2”,
“statName”: “STAT_3”,
“statResult”: “200.00”,
“segIn”: true,
“groupColumn”: “attrib1|attrib2”,
“groupValue”: “003|004”
}
]
}

2

Any pointers on this query? Appreciate if someone could chime in…thank you

3

No further optimization is possible. You can try max_parallelism request level setting.

https://docs.couchbase.com/server/5.5/settings/query-settings.html#section_nnj_sjk_k1b