I am storing Listing Docs in one of my Couch DB’s which currently is about 100 K documents. THe docs have a key Value of
Record.Photos.PhotosCount which i would like to Sum up and see how many Images i have in my Records.
When i Run the below query it takes a long time
SELECT SUM(TONUMBER(Record.Photos.PhotosCount))
FROM rets
WHERE _type ="Residential" and Record.Photos.PhotosCount IS NOT MISSING
I have the following Index for it
CREATE INDEX adv_to_number_Record_Photos_PhotosCount_Record_Photos_PhotosCount_type ON rets(to_number(((Record.Photos).PhotosCount)),((Record.Photos).PhotosCount)) WHERE (_type = “Residential”)
And here is my Plan Text
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“covers”: [
“cover (to_number((((rets.Record).Photos).PhotosCount)))”,
“cover ((((rets.Record).Photos).PhotosCount))”,
“cover ((meta(rets).id))”
],
“filter_covers”: {
“cover ((rets._type))”: “Residential”
},
“index”: “adv_to_number_Record_Photos_PhotosCount_Record_Photos_PhotosCount_type”,
“index_id”: “513488e6226cdb”,
“index_projection”: {
“entry_keys”: [
0,
1
]
},
“keyspace”: “rets”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
},
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((cover ((rets._type)) = “Residential”) and (cover ((((rets.Record).Photos).PhotosCount)) is not missing))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: [
“sum(cover (to_number((((rets.Record).Photos).PhotosCount))))”
],
“group_keys”:
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“sum(cover (to_number((((rets.Record).Photos).PhotosCount))))”
],
“group_keys”:
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“sum(cover (to_number((((rets.Record).Photos).PhotosCount))))”
],
“group_keys”:
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “sum(cover (to_number((((rets.Record).Photos).PhotosCount))))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}