I have 63M docs, each doc - 300 bytes, meta is about 100 bytes
I have what I think are correct indexes however the query keeps failing even after and hour.
indexer log shows no errors
indexer RAM (4GB) is at about 80%
CPU never spikes over 40-50
Buckets Operations Per Second during query - 15k
I am banging my head with this for several days now, nothing I did help, maybe this is too much for CB to take? can some one help me out here?
thanks
Shay
- query and plan below:
SELECT
field,
dsid,
Sum(counter) AS count,
Sum(rvalues) AS rvalues
FROM metrics
WHERE field IS NOT missing
AND dsid IS NOT missing
AND systemId = ‘configtest’
and metricType = ‘field’
GROUP BY
subField,
field,
dsid
this is the execution plan -
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IntersectScan”,
“scans”: [
{
“#operator”: “IndexScan3”,
“index”: “metric_job_systemId”,
“index_id”: “3444774e726bad02”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “metrics”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““configtest””,
“inclusion”: 3,
“low”: ““configtest””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan3”,
“index”: “metric_job_metricType”,
“index_id”: “443efd32ef30652d”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “metrics”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““field””,
“inclusion”: 3,
“low”: ““field””
}
]
}
],
“using”: “gsi”
}
]
},
{
“#operator”: “Fetch”,
“keyspace”: “metrics”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((((metrics.field) is not missing) and ((metrics.dsid) is not missing)) and ((metrics.systemId) = “configtest”)) and ((metrics.metricType) = “field”))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: [
“sum((metrics.counter))”,
“sum((metrics.rvalues))”
],
“group_keys”: [
“(metrics.subField)”,
“(metrics.field)”,
“(metrics.dsid)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“sum((metrics.counter))”,
“sum((metrics.rvalues))”
],
“group_keys”: [
“(metrics.subField)”,
“(metrics.field)”,
“(metrics.dsid)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“sum((metrics.counter))”,
“sum((metrics.rvalues))”
],
“group_keys”: [
“(metrics.subField)”,
“(metrics.field)”,
“(metrics.dsid)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(metrics.field)”
},
{
“expr”: “(metrics.dsid)”
},
{
“as”: “count”,
“expr”: “sum((metrics.counter))”
},
{
“as”: “rvalues”,
“expr”: “sum((metrics.rvalues))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT field, \n dsid, \n Sum(counter) AS count, \n Sum(rvalues) AS rvalues \nFROM metrics \n\nWHERE field IS NOT missing \nAND dsid IS NOT missing \nAND systemId = ‘configtest’\nand metricType = ‘field’\nGROUP BY \nsubField,\nfield, \ndsid”
}