This is really cool. I applied your index suggestions and they do help tremendously.
Can you help me understand why in the explain the LET is taking so much time? This is my explain text.
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.174µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "11.743µs",
"servTime": "1.016365ms"
},
"privileges": {
"List": [
{
"Target": "default:mdata",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "8.696µs"
},
"~children": [
{
"#operator": "IntersectScan",
"#stats": {
"#itemsIn": 864,
"#itemsOut": 583,
"#phaseSwitches": 2903,
"execTime": "1.313074ms",
"kernTime": "21.211604ms"
},
"scans": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 184,
"#phaseSwitches": 745,
"execTime": "492.314µs",
"kernTime": "242.398µs",
"servTime": "2.73331ms"
},
"as": "o",
"index": "ix_subset_version_fcstValidEpoch_DD_obs",
"index_id": "9a0d59e5e39e178d",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"METAR\"",
"inclusion": 3,
"low": "\"METAR\""
},
{
"high": "\"V01\"",
"inclusion": 3,
"low": "\"V01\""
},
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.003",
"#time_absolute": 0.003225624
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 97,
"#phaseSwitches": 393,
"execTime": "292.162µs",
"kernTime": "59.505µs",
"servTime": "3.105274ms"
},
"as": "o",
"index": "idx_type_docType_version_fcstValidEpoch_METAR",
"index_id": "acf9f11cc0879013",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"DD\"",
"inclusion": 3,
"low": "\"DD\""
},
{
"high": "\"obs\"",
"inclusion": 3,
"low": "\"obs\""
},
{
"high": "\"V01\"",
"inclusion": 3,
"low": "\"V01\""
},
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.003",
"#time_absolute": 0.0033974359999999998
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 583,
"#phaseSwitches": 2337,
"execTime": "1.178854ms",
"kernTime": "320.14µs",
"servTime": "1.8744ms"
},
"as": "o",
"index": "adv_fcstValidEpoch_docType_subset_version_type",
"index_id": "aab729c871fbf576",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.003",
"#time_absolute": 0.003053254
}
],
"#time_normal": "00:00.001",
"#time_absolute": 0.001313074
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 583,
"#itemsOut": 583,
"#phaseSwitches": 2409,
"execTime": "4.256622ms",
"kernTime": "2.441514033s",
"servTime": "490.409745ms"
},
"as": "o",
"keyspace": "mdata",
"namespace": "default",
"#time_normal": "00:00.494",
"#time_absolute": 0.494666367
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "8.395µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 583,
"#itemsOut": 7682,
"#phaseSwitches": 34229,
"execTime": "289.664183ms",
"kernTime": "47.837926883s"
},
"alias": "m",
"on_clause": "(ceil((3600 * floor((((`o`.`fcstValidEpoch`) + (3600 / 2)) / 3600)))) = ceil((3600 * floor((((`m`.`fcstValidEpoch`) + (3600 / 2)) / 3600)))))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1166,
"execTime": "39.422477307s",
"kernTime": "1.750126ms",
"state": "running"
},
"~children": [
{
"#operator": "IntersectScan",
"#stats": {
"#itemsIn": 3781568,
"#itemsOut": 7682,
"#phaseSwitches": 7583747,
"execTime": "5.114748981s",
"kernTime": "5.684166012s"
},
"scans": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1906903,
"#phaseSwitches": 7631940,
"execTime": "5.033923583s",
"kernTime": "2.487471313s",
"servTime": "2.783055257s"
},
"as": "m",
"index": "idx_type_docType_version_fcstValidEpoch_METAR",
"index_id": "acf9f11cc0879013",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"range": [
{
"high": "\"DD\"",
"inclusion": 3,
"low": "\"DD\""
},
{
"high": "\"model\"",
"inclusion": 3,
"low": "\"model\""
},
{
"high": "\"V01\"",
"inclusion": 3,
"low": "\"V01\""
},
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 7682,
"#phaseSwitches": 33643,
"execTime": "41.189062ms",
"kernTime": "54.520114ms",
"servTime": "10.109341365s"
},
"as": "m",
"index": "ix_subset_version_model_fcstLen_fcstValidEpoch_DD_model",
"index_id": "968e2954d5368e50",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"range": [
{
"high": "\"METAR\"",
"inclusion": 3,
"low": "\"METAR\""
},
{
"high": "\"V01\"",
"inclusion": 3,
"low": "\"V01\""
},
{
"high": "\"HRRR\"",
"inclusion": 3,
"low": "\"HRRR\""
},
{
"high": "6",
"inclusion": 3,
"low": "6"
},
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
},
{
"high": "ceil((3600 * floor((((`o`.`fcstValidEpoch`) + (3600 / 2)) / 3600))))",
"inclusion": 3,
"low": "ceil((3600 * floor((((`o`.`fcstValidEpoch`) + (3600 / 2)) / 3600))))"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 1866983,
"#phaseSwitches": 7472279,
"execTime": "5.179590472s",
"kernTime": "2.449477912s",
"servTime": "2.909344991s"
},
"as": "m",
"index": "adv_fcstLen_model_fcstValidEpoch_type_docType_subset_version",
"index_id": "44cc5b35c7c4e605",
"index_projection": {
"primary_key": true
},
"keyspace": "mdata",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"range": [
{
"high": "6",
"inclusion": 3,
"low": "6"
},
{
"high": "\"HRRR\"",
"inclusion": 3,
"low": "\"HRRR\""
},
{
"high": "(1615401000 + 900)",
"inclusion": 3,
"low": "(1612805400 - 900)"
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 7682,
"#itemsOut": 7682,
"#phaseSwitches": 33949,
"execTime": "39.682079ms",
"kernTime": "10.801279003s",
"servTime": "1.126032525s"
},
"as": "m",
"keyspace": "mdata",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.289",
"#time_absolute": 0.28966418299999996
},
{
"#operator": "Let",
"#stats": {
"#itemsIn": 7682,
"#itemsOut": 7682,
"#phaseSwitches": 30731,
"execTime": "51.289474254s",
"kernTime": "108.835001ms"
},
"bindings": [
{
"expr": "array {\"name\": (`station`.`name`), \"station\": (`station`.`Ceiling`)} for `station` in (`o`.`data`) when ((`station`.`name`) in [\"KBYG\", \"KGEY\", \"KSHR\"]) end",
"var": "observation"
},
{
"expr": "array {\"name\": (`station`.`name`), \"station\": (`station`.`Ceiling`)} for `station` in object_values((`m`.`data`)) when ((`station`.`name`) in [\"KBYG\", \"KGEY\", \"KSHR\"]) end",
"var": "prediction"
}
],
"#time_normal": "00:51.289",
"#time_absolute": 51.289474254
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 7682,
"#itemsOut": 343,
"#phaseSwitches": 16053,
"execTime": "508.293338ms",
"kernTime": "50.890165348s"
},
"condition": "(((((((((((((((((`o`.`type`) = \"DD\") and ((`o`.`docType`) = \"obs\")) and ((`o`.`subset`) = \"METAR\")) and ((`o`.`version`) = \"V01\")) and ((1612805400 - 900) <= (`o`.`fcstValidEpoch`))) and ((`o`.`fcstValidEpoch`) <= (1615401000 + 900))) and (array_length(`observation`) = 3)) and ((`m`.`type`) = \"DD\")) and ((`m`.`docType`) = \"model\")) and ((`m`.`subset`) = \"METAR\")) and ((`m`.`version`) = \"V01\")) and ((`m`.`model`) = \"HRRR\")) and ((`m`.`fcstLen`) = 6)) and ((1612805400 - 900) <= (`m`.`fcstValidEpoch`))) and ((`m`.`fcstValidEpoch`) <= (1615401000 + 900))) and (array_length(`prediction`) = 3))",
"#time_normal": "00:00.508",
"#time_absolute": 0.508293338
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 343,
"#itemsOut": 343,
"#phaseSwitches": 1034,
"execTime": "6.317129ms",
"kernTime": "51.390252994s"
},
"result_terms": [
{
"expr": "`observation`"
},
{
"expr": "`prediction`"
},
{
"as": "avtime",
"expr": "ceil((3600 * floor((((`o`.`fcstValidEpoch`) + (3600 / 2)) / 3600))))"
}
],
"#time_normal": "00:00.006",
"#time_absolute": 0.006317129
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 343,
"#itemsOut": 343,
"#phaseSwitches": 1030,
"execTime": "649.917µs",
"kernTime": "988.742µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.0006499170000000001
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000008395
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000008696
},
"#time_normal": "00:00.001",
"#time_absolute": 0.001028108
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 343,
"#itemsOut": 343,
"#phaseSwitches": 689,
"execTime": "35.423083ms",
"kernTime": "51.364131276s"
},
"#time_normal": "00:00.035",
"#time_absolute": 0.035423083
}
],
"~versions": [
"6.5.0-N1QL",
"6.6.2-9588-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000002174
}
Going back to the original question, is there a way (without specifying a single account number) to join between the account- and financial- docs to gain access to the nested elements within the financials doc (such as “amount”)?