Details
-
Improvement
-
Resolution: Unresolved
-
Major
-
feature-backlog
-
None
-
0
Description
Create these indexes:
CREATE INDEX idx_a ON travel-sample("airline" || tostring(id), iata) WHERE type = "airline";
|
CREATE INDEX idx_r ON `travel-sample`(airlineid, DISTINCT ARRAY e.flight FOR e IN schedule END) WHERE `type` = "route";
|
Run this query:
SELECT 1
|
FROM `travel-sample` airline
|
LEFT JOIN `travel-sample` route
|
ON ("airline_" || TOSTRING(airline.id)) = route.airlineid
|
AND route.type = "route"
|
AND ANY e IN route.schedule SATISFIES e.flight = airline.iata || "678" END
|
WHERE ("airline_" || TOSTRING(airline.id)) IS NOT MISSING
|
AND airline.type = "airline"
|
This query performs fetch. See explain below:
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"as": "airline",
|
"covers": [
|
"cover ((\"airline_\" || to_string((`airline`.`id`))))",
|
"cover ((`airline`.`iata`))",
|
"cover ((meta(`airline`).`id`))"
|
],
|
"filter": "((cover ((\"airline_\" || to_string((`airline`.`id`)))) is not missing) and (cover ((`airline`.`type`)) = \"airline\"))",
|
"filter_covers": {
|
"cover ((`airline`.`type`))": "airline"
|
},
|
"index": "idx_a",
|
"index_id": "3c5c08de5952cac9",
|
"index_projection": {
|
"entry_keys": [
|
0,
|
1
|
]
|
},
|
"keyspace": "travel-sample",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"inclusion": 1,
|
"index_key": "(\"airline_\" || to_string(`id`))",
|
"low": "null"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "NestedLoopJoin",
|
"alias": "route",
|
"on_clause": "(((cover ((\"airline_\" || to_string((`airline`.`id`)))) = (`route`.`airlineid`)) and ((`route`.`type`) = \"route\")) and any `e` in (`route`.`schedule`) satisfies ((`e`.`flight`) = (cover ((`airline`.`iata`)) || \"678\")) end)",
|
"outer": true,
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"as": "route",
|
"index": "idx_r",
|
"index_id": "4cc65eb27d8f3702",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "travel-sample",
|
"namespace": "default",
|
"nested_loop": true,
|
"spans": [
|
{
|
"range": [
|
{
|
"high": "cover ((\"airline_\" || to_string((`airline`.`id`))))",
|
"inclusion": 3,
|
"index_key": "`airlineid`",
|
"low": "cover ((\"airline_\" || to_string((`airline`.`id`))))"
|
},
|
{
|
"high": "(cover ((`airline`.`iata`)) || \"678\")",
|
"inclusion": 3,
|
"index_key": "(distinct (array (`e`.`flight`) for `e` in `schedule` end))",
|
"low": "(cover ((`airline`.`iata`)) || \"678\")"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "Fetch",
|
"as": "route",
|
"keyspace": "travel-sample",
|
"namespace": "default",
|
"nested_loop": true
|
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "1"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|