Ok, it works but it takes more than 50 seg with only 80K documents. Any suggestion? because we gonna be working with at least 10 millions documents
Here que explain:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “UnionScan”,
“scans”: [
{
“#operator”: “IndexScan2”,
“index”: “ix51”,
“index_id”: “c03a19f316e3fd4b”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “CONTENT”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““OPEN””,
“inclusion”: 3,
“low”: ““OPEN””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan2”,
“index”: “ix51”,
“index_id”: “c03a19f316e3fd4b”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “CONTENT”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“high”: ““OPEN””,
“inclusion”: 3,
“low”: ““OPEN””
},
{
“inclusion”: 0,
“low”: “null”
},
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
}
]
},
{
“#operator”: “Fetch”,
“as”: “store”,
“keyspace”: “CONTENT”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexJoin”,
“as”: “item”,
“for”: “store”,
“keyspace”: “CONTENT”,
“namespace”: “default”,
“on_key”: “((item
.main
).owner
)”,
“outer”: true,
“scan”: {
“index”: “ix52”,
“index_id”: “6f8d2d01eec17dac”,
“using”: “gsi”
}
},
{
“#operator”: “Filter”,
“condition”: “(((((store
.main
).type
) = “electronic-store”) and (((store
.main
).status
) = “OPEN”)) and ((item
is missing) or ((((store
.id
) = ((item
.main
).owner
)) and (((item
.main
).type
) = “real-estate”)) and (((item
.main
).status
) = “AVAILABLE”))))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: [
“count((item
.id
))”
],
“group_keys”: [
“(store
.id
)”,
“(store
.main
)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“count((item
.id
))”
],
“group_keys”: [
“(store
.id
)”,
“(store
.main
)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“count((item
.id
))”
],
“group_keys”: [
“(store
.id
)”,
“(store
.main
)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(store
.id
)”
},
{
“as”: “count”,
“expr”: “count((item
.id
))”
},
{
“expr”: “(store
.main
)”
}
]
}
]
}
}
]
},
{
“#operator”: “Order”,
“sort_terms”: [
{
“desc”: true,
“expr”: “count
”
}
]
},
{
“#operator”: “FinalProject”
}
]
},
“text”: “SELECT store.id, count(item.id) as count, store.main\r\nFROM CONTENT store\r\nLEFT JOIN CONTENT item ON KEY item.main.owner FOR store\r\nWHERE store.main.type = “electronic-store”\r\nAND store.main.status = “OPEN”\r\nAND (item IS MISSING OR (store.id = item.main.owner AND item.main.type = “real-estate” AND item.main.status = “AVAILABLE”))\r\nGROUP BY store.id, store.main\r\nORDER BY count DESC”
}