@geraldss,
The result:
{
"query": {
"bool": {
"must": [
{
"range": {
"couchbaseDocument.doc.actual": {
"from": "20",
"to": "40"
}
}
}
],
"must_not": [ ],
"should": [ ]
}
},
"from": 0,
"size": 25000,
"sort": [ ],
"facets": { }
}
87934 hits. 0.477 seconds
The N1QL:
"status": "success",
"metrics": {
"elapsedTime": "21.424015321s",
"executionTime": "21.423889537s",
"resultCount": 87934,
"resultSize": 36879829
}
cbq> explain select * from default where actual between 20 and 40;
{
"requestID": "3f8d8d33-6a9b-4021-8942-293f3ec02737",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "indextest",
"keyspace": "default",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"40"
],
"Inclusion": 3,
"Low": [
"20"
]
},
"Seek": null
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((`default`.`actual`) between 20 a nd 40)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "4.368175ms",
"executionTime": "4.184228ms",
"resultCount": 1,
"resultSize": 2022
}
}
Well, I will try to do this at elasticsearch:
select
t.category,
t.period,
sum(t.sumActual)
from
default as q
inner join default as p on keys q.parent
inner join default as t on keys p.iwpCumulatives
where
q.type = 'IwpCumulative'
and q.period = 50
and q.sumActual > 0
and q.category = 'Porcentaje'
group by t.category,t.period
order by t.period,t.category;
"status": "success",
"metrics": {
"elapsedTime": "12.758127084s",
"executionTime": "12.757850405s",
"resultCount": 250,
"resultSize": 25339,
"sortCount": 250
}
{
"requestID": "7c817808-e1f3-45b6-8be7-d8400d30d467",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "SecondIndex",
"keyspace": "default",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"50",
"\"IwpCumulative\"",
"successor(\"Porcentaje\")"
],
"Inclusion": 0,
"Low": [
"50",
"\"IwpCumulative\"",
"\"Porcentaje\"",
"0"
]
},
"Seek": null
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "q",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Join",
"as": "p",
"keyspace": "default",
"namespace": "default",
"on_keys": "(`q`.`parent`)"
},
{
"#operator": "Join",
"as": "t",
"keyspace": "default",
"namespace": "default",
"on_keys": "(`p`.`iwpCumulatives`)"
},
{
"#operator": "Filter",
"condition": "(((((`q`.`type`) = \"IwpCumulative\") and ((`q`.`period`) = 50)) and (0 \u003c (`q`.`sumActual`))) and ((`q`.`category`) = \"Porcentaje\"))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum((`t`.`sumActual`))"
],
"group_keys": [
"(`t`.`category`)",
"(`t`.`period`)"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum((`t`.`sumActual`))"
],
"group_keys": [
"(`t`.`category`)",
"(`t`.`period`)"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum((`t`.`sumActual`))"
],
"group_keys": [
"(`t`.`category`)",
"(`t`.`period`)"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`t`.`category`)"
},
{
"expr": "(`t`.`period`)"
},
{
"expr": "sum((`t`.`sumActual`))"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "(`t`.`period`)"
},
{
"expr": "(`t`.`category`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "4.846268ms",
"executionTime": "4.704478ms",
"resultCount": 1,
"resultSize": 6133
}
}
Regards.
