The index doesn’t seems to improve the query time. I can share the explain statement:
(updated with the new query)
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"index": "idx_from_cb_forum",
"index_id": "8a604dbc321a329f",
"index_projection": {
"primary_key": true
},
"keyspace": "DATA_BUCKET",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 1,
"low": "null"
},
{
"high": "false",
"inclusion": 3,
"low": "false"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "event",
"keyspace": "DATA_BUCKET",
"namespace": "default"
},
{
"#operator": "Join",
"as": "patient",
"keyspace": "DATA_BUCKET",
"namespace": "default",
"on_keys": "(`event`.`patientId`)"
},
{
"#operator": "Join",
"as": "functionalUnit",
"keyspace": "METADATA_BUCKET",
"namespace": "default",
"on_keys": "(`event`.`functionalUnitId`)"
},
{
"#operator": "Join",
"as": "lastModifier",
"keyspace": "USERS",
"namespace": "default",
"on_keys": "(`event`.`lastModifier`)"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Let",
"bindings": [
{
"expr": "ifmissingornull((`event`.`eventTypeId`), (`event`.`configurableMetadataId`))",
"var": "eventType"
}
]
},
{
"#operator": "Filter",
"condition": "(((((((`event`.`type`) = \"EVENT\") and (`eventType` is not missing)) and (0 < array_length((`event`.`elementValues`)))) and (ifmissing((`event`.`isVoided`), false) = false)) and ((`patient`.`type`) = \"PATIENT\")) and (ifmissing((`patient`.`isVoided`), false) = false))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"array_agg({\"completed\": (`event`.`isComplete`), \"fields\": array {\"id\": (`field`.`elementValueTypeId`), \"value\": (`field`.`value`), \"vdo\": (`field`.`valueDataOptionId`)} for `field` in (`event`.`elementValues`) end, \"functionalUnitId\": (`event`.`functionalUnitId`), \"functionalUnitName\": (`functionalUnit`.`name`), \"id\": (meta(`event`).`id`), \"lastUpdate\": (`event`.`lastModificationDate`), \"lastUpdateById\": (`event`.`lastModifier`), \"lastUpdateByName\": ((upper((`lastModifier`.`familyName`)) || \" \") || title((`lastModifier`.`givenName`))), \"patientBirthDate\": millis_to_str((`patient`.`birthDate`)), \"patientFamilyName\": upper((`patient`.`familyName`)), \"patientFirstName\": title((`patient`.`givenName`)), \"patientId\": (`event`.`patientId`), \"patientSex\": (`patient`.`sex`), \"visitId\": (`event`.`visitId`)})"
],
"group_keys": [
"`eventType`"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"array_agg({\"completed\": (`event`.`isComplete`), \"fields\": array {\"id\": (`field`.`elementValueTypeId`), \"value\": (`field`.`value`), \"vdo\": (`field`.`valueDataOptionId`)} for `field` in (`event`.`elementValues`) end, \"functionalUnitId\": (`event`.`functionalUnitId`), \"functionalUnitName\": (`functionalUnit`.`name`), \"id\": (meta(`event`).`id`), \"lastUpdate\": (`event`.`lastModificationDate`), \"lastUpdateById\": (`event`.`lastModifier`), \"lastUpdateByName\": ((upper((`lastModifier`.`familyName`)) || \" \") || title((`lastModifier`.`givenName`))), \"patientBirthDate\": millis_to_str((`patient`.`birthDate`)), \"patientFamilyName\": upper((`patient`.`familyName`)), \"patientFirstName\": title((`patient`.`givenName`)), \"patientId\": (`event`.`patientId`), \"patientSex\": (`patient`.`sex`), \"visitId\": (`event`.`visitId`)})"
],
"group_keys": [
"`eventType`"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"array_agg({\"completed\": (`event`.`isComplete`), \"fields\": array {\"id\": (`field`.`elementValueTypeId`), \"value\": (`field`.`value`), \"vdo\": (`field`.`valueDataOptionId`)} for `field` in (`event`.`elementValues`) end, \"functionalUnitId\": (`event`.`functionalUnitId`), \"functionalUnitName\": (`functionalUnit`.`name`), \"id\": (meta(`event`).`id`), \"lastUpdate\": (`event`.`lastModificationDate`), \"lastUpdateById\": (`event`.`lastModifier`), \"lastUpdateByName\": ((upper((`lastModifier`.`familyName`)) || \" \") || title((`lastModifier`.`givenName`))), \"patientBirthDate\": millis_to_str((`patient`.`birthDate`)), \"patientFamilyName\": upper((`patient`.`familyName`)), \"patientFirstName\": title((`patient`.`givenName`)), \"patientId\": (`event`.`patientId`), \"patientSex\": (`patient`.`sex`), \"visitId\": (`event`.`visitId`)})"
],
"group_keys": [
"`eventType`"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`eventType`"
},
{
"as": "events",
"expr": "array_agg({\"completed\": (`event`.`isComplete`), \"fields\": array {\"id\": (`field`.`elementValueTypeId`), \"value\": (`field`.`value`), \"vdo\": (`field`.`valueDataOptionId`)} for `field` in (`event`.`elementValues`) end, \"functionalUnitId\": (`event`.`functionalUnitId`), \"functionalUnitName\": (`functionalUnit`.`name`), \"id\": (meta(`event`).`id`), \"lastUpdate\": (`event`.`lastModificationDate`), \"lastUpdateById\": (`event`.`lastModifier`), \"lastUpdateByName\": ((upper((`lastModifier`.`familyName`)) || \" \") || title((`lastModifier`.`givenName`))), \"patientBirthDate\": millis_to_str((`patient`.`birthDate`)), \"patientFamilyName\": upper((`patient`.`familyName`)), \"patientFirstName\": title((`patient`.`givenName`)), \"patientId\": (`event`.`patientId`), \"patientSex\": (`patient`.`sex`), \"visitId\": (`event`.`visitId`)})"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "`eventType`"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT eventType,\n ARRAY_AGG ({'id': META(event).id,\n 'patientId': event.patientId,\n 'visitId': event.visitId,\n 'fields': ARRAY {'id': field.elementValueTypeId,\n 'value': field.`value`,\n 'vdo': field.valueDataOptionId }\n FOR field IN event.elementValues END,\n 'patientFirstName': INITCAP(patient.givenName),\n 'patientFamilyName': UPPER(patient.familyName),\n 'patientBirthDate': MILLIS_TO_STR(patient.birthDate),\n 'patientSex': patient.sex,\n 'functionalUnitId': event.functionalUnitId,\n 'functionalUnitName': functionalUnit.name,\n 'completed': event.isComplete,\n 'lastUpdate': event.lastModificationDate,\n 'lastUpdateById': event.lastModifier,\n 'lastUpdateByName': UPPER(lastModifier.familyName) || ' ' ||\n INITCAP(lastModifier.givenName)}) as events\nFROM DATA_BUCKET event\nJOIN DATA_BUCKET patient ON KEYS event.patientId\nJOIN METADATA_BUCKET functionalUnit ON KEYS event.functionalUnitId\nJOIN USERS lastModifier ON KEYS event.lastModifier\nLET eventType = IFMISSINGORNULL(event.eventTypeId, event.configurableMetadataId)\nWHERE event.type = 'EVENT'\n AND eventType IS NOT MISSING\n AND ARRAY_LENGTH(event.elementValues) > 0\n AND IFMISSING(event.isVoided, false) = false\n AND patient.type = 'PATIENT'\n AND IFMISSING(patient.isVoided, false) = false\nGROUP BY eventType\nORDER BY eventType;"
}