Please help!
I try to inner join two collections like this:
SELECT col_doc.data.en, col_sort
FROM `deploy-dev`.game.data col_doc inner join `deploy-dev`.game_sort.data col_sort on col_doc.id = col_sort.id
WHERE (col_doc.data.en.provider.id IN [ "rls_0ea0a93e-bf2d-4788-9005-83264286d9a5", "rls_b2a9c1b2-8129-48c5-b04e-ad6f809a45a2", "rls_bdd0068c-d1cb-4c65-91e1-91549e264615", "rls_c2ffcb64-4286-40df-b72f-c16e06ae0605", "rls_f757ecfc-e4da-4440-98bd-b62b5587b91a", "rls_fc2eb9d5-8029-4753-85ad-a38528464696", "rls_21d6f48d-3bb8-443a-b161-cca45ad10ca5", "rls_3b6d6517-16d4-45cc-bb3e-5c87d62c1d97", "rls_4b9aa36b-4454-46d8-8e15-96de45b9ea23", "rls_54169226-59bf-42a8-aa97-ba52d5b8a69a", "rls_6abe151b-9fb5-45ef-9ef5-73bbe9590933", "rls_acb1d7a3-dc2e-4167-958e-92ab475a4269", "rls_cd2e26dc-f97d-4e1f-9845-3cd0d8597b4a", "rls_d9753eb7-cba9-4752-8f8d-9e2006aa07f1", "rls_ea628317-5766-4f9c-b3a6-4830023240e2", "rls_fab76d4e-0e47-449d-a7ba-61fce4d721dd", "rls_1270abe7-5590-429a-b457-6e021c70ed29", "rls_4a10c40c-d225-432d-af1c-420a5073df85", "rls_4f86f599-286b-4d1d-88ed-6f9cf531db96", "rls_90af77db-75e9-45e0-a9be-db8aa09c9f33", "rls_a871127c-eb17-4a60-aacb-2a254d41fd75", "rls_b55d0025-daaf-4e20-a049-e852067f4031", "rls_c9fd7f4a-5345-4c92-a5c6-6024a8d8b4a3", "rls_dc26ae6e-59d4-43e5-a50a-5bbae61e00d4", "rls_dd88cbe6-d19d-422a-bfca-30e40b2d3292", "rls_f19673c5-5e7b-4a1f-b9e6-e1c3ca52483a", "rls_f5f7cf80-b327-473e-9fd6-fada678184d6", "rls_10d8da65-4482-4cc4-90ab-997c05f354e2", "rls_165e8d74-7130-4995-84df-785b32cb830c", "rls_1ac2d1fd-f446-43f2-add9-49da0c5c3faf", "rls_233b6a75-f4fc-4c5f-a0d2-51505f546fb5", "rls_4008e17a-0546-4cc9-a121-e20882f5f49e", "rls_7d3844f6-e09b-4ea0-abf2-774928d9ff76", "rls_88749131-b210-4bea-938c-fd8d1f158afb", "rls_e2b70881-942b-45a5-af1a-eb8c987de704", "rls_f0210675-f785-48f0-96d8-b1b38d30422f", "rls_feddeb73-e0c0-4a18-8f9e-366ffcb4f0d4", "rls_18237a0d-656f-4c8a-a00a-95910819b73b", "rls_1f7a0115-f52a-4ad2-a7ef-fea226c172e4", "rls_27869cdf-96f9-4f1f-9e9f-09964fa2159a", "rls_54ea62bd-f03e-4cc6-8fe8-0e40efd04e6a", "rls_705b2830-d076-4109-a7a4-c0a8b7462e59", "rls_8cc4435b-8aa3-4afb-88f4-092af701d1aa", "rls_a46dcd5d-60b6-4dad-b853-e35481bb8202", "rls_a4b418d7-761b-4dba-9fc9-08525f11effc", "rls_ce1b6050-8487-4d80-b83e-3b49efd4539c", "rls_d822b0c1-cf0d-4f63-a213-1551bafe31ac", "rls_e5bab6e6-1030-440e-853f-190af48bbf82", "rls_edd99f6a-e039-4a9e-b0d3-9875c97ec97f", "rls_5b435198-9dee-4ab0-b800-ee93d5cece87", "rls_6abef3a5-5aa0-4087-8c59-f905af5fc4fa", "rls_7b42406f-6164-45eb-8cb6-4fc90e1e902d", "rls_836a9045-ece7-44b5-94f2-8bf16bee8616", "rls_d955a4f5-46c4-4d82-89cc-3bbe31b2fe42", "rls_dc493038-0d04-42db-9885-c221289e6fbe", "rls_f672ef98-6684-423f-9d5c-6b0faef1efcf", "rls_fc2f4094-8978-4e51-a0a7-2c133d8f9eaf", "rls_2d1040b4-6883-425a-936f-3f97fd97254e", "rls_425e6fa9-cf95-493e-bb80-df19486b9f04", "rls_629ac79a-c0f1-4df8-9096-a0b5bc65926a", "rls_73082c51-e092-4e2e-b752-221196e719e8", "rls_89e91531-44ad-40be-a441-37a5c06d2a3e", "rls_8f08d3f9-63c6-47d3-9529-6f69a4ad47fe", "rls_9b1fe264-e497-47e5-b135-e5c2dad69697", "rls_a4cea5d9-2233-4343-b914-eb555e6e8294", "rls_f4e1a2dd-e3a9-4dad-a97a-8a4591cccfc3", "rls_f6485829-7c78-4a40-b1e6-dff3356e2603" ]
)
Index 1: deploy-dev.game.data.providerIdx (data,en,provider,id:string)
Index 2: deploy-dev.game_sort.rollingslots.ID(data,en,provider,id:string)
I don’t see in the Explain that indexes are used in this query, as a result the query is very slow
If I don’t use INNER JOIN then the index is used
index-search(“providerIdx”, 0, “deploy-dev/game”, “rollingslots”, TRUE, TRUE, 1, $$28, 1, $$28, TRUE, TRUE, TRUE)
{
"operator": "distribute-result",
"expressions": [
"$$37"
],
"operatorId": "1.1",
"physical-operator": "DISTRIBUTE_RESULT",
"execution-mode": "UNPARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.2",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "UNPARTITIONED",
"inputs": [
{
"operator": "limit",
"value": "100",
"operatorId": "1.3",
"physical-operator": "STREAM_LIMIT",
"execution-mode": "UNPARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.4",
"physical-operator": "RANDOM_MERGE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$37"
],
"operatorId": "1.5",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "assign",
"variables": [
"$$37"
],
"expressions": [
"{\"en\": $$38, \"col_sort\": $$col_sort}"
],
"operatorId": "1.6",
"physical-operator": "ASSIGN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "limit",
"value": "100",
"operatorId": "1.7",
"physical-operator": "STREAM_LIMIT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$38",
"$$col_sort"
],
"operatorId": "1.8",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.9",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "join",
"condition": "eq($$43, $$32)",
"operatorId": "1.10",
"physical-operator": "HYBRID_HASH_JOIN [$$32][$$43]",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.11",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$38",
"$$col_sort",
"$$32"
],
"operatorId": "1.12",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.13",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "join",
"condition": "eq($$44, $$45)",
"operatorId": "1.14",
"physical-operator": "HYBRID_HASH_JOIN [$$44][$$45]",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.15",
"physical-operator": "HASH_PARTITION_EXCHANGE [$$44]",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "assign",
"variables": [
"$$32"
],
"expressions": [
"$$38.getField(\"provider\").getField(\"id\")"
],
"operatorId": "1.16",
"physical-operator": "ASSIGN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$38",
"$$44"
],
"operatorId": "1.17",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "assign",
"variables": [
"$$38",
"$$44"
],
"expressions": [
"$$col_doc.getField(\"data\").getField(\"en\")",
"$$col_doc.getField(\"id\")"
],
"operatorId": "1.18",
"physical-operator": "ASSIGN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$col_doc"
],
"operatorId": "1.19",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.20",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "data-scan",
"variables": [
"$$39",
"$$col_doc",
"$$40"
],
"data-source": "`deploy-dev`.game.data",
"operatorId": "1.21",
"physical-operator": "DATASOURCE_SCAN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.22",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "empty-tuple-source",
"operatorId": "1.23",
"physical-operator": "EMPTY_TUPLE_SOURCE",
"execution-mode": "PARTITIONED"
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"operator": "exchange",
"operatorId": "1.24",
"physical-operator": "HASH_PARTITION_EXCHANGE [$$45]",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "assign",
"variables": [
"$$45"
],
"expressions": [
"$$col_sort.getField(\"id\")"
],
"operatorId": "1.25",
"physical-operator": "ASSIGN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "project",
"variables": [
"$$col_sort"
],
"operatorId": "1.26",
"physical-operator": "STREAM_PROJECT",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.27",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "data-scan",
"variables": [
"$$41",
"$$col_sort",
"$$42"
],
"data-source": "`deploy-dev`.game_sort.data",
"operatorId": "1.28",
"physical-operator": "DATASOURCE_SCAN",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "exchange",
"operatorId": "1.29",
"physical-operator": "ONE_TO_ONE_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "empty-tuple-source",
"operatorId": "1.30",
"physical-operator": "EMPTY_TUPLE_SOURCE",
"execution-mode": "PARTITIONED"
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"operator": "exchange",
"operatorId": "1.31",
"physical-operator": "BROADCAST_EXCHANGE",
"execution-mode": "PARTITIONED",
"inputs": [
{
"operator": "unnest",
"variables": [
"$$43"
],
"expressions": [
"scan-collection(array: [ \"rls_dc26ae6e-59d4-43e5-a50a-5bbae61e00d4\", \"rls_a4cea5d9-2233-4343-b914-eb555e6e8294\", \"rls_ea628317-5766-4f9c-b3a6-4830023240e2\", \"rls_e5bab6e6-1030-440e-853f-190af48bbf82\", \"rls_f4e1a2dd-e3a9-4dad-a97a-8a4591cccfc3\", \"rls_90af77db-75e9-45e0-a9be-db8aa09c9f33\", \"rls_ce1b6050-8487-4d80-b83e-3b49efd4539c\", \"rls_8cc4435b-8aa3-4afb-88f4-092af701d1aa\", \"rls_a871127c-eb17-4a60-aacb-2a254d41fd75\", \"rls_bdd0068c-d1cb-4c65-91e1-91549e264615\", \"rls_edd99f6a-e039-4a9e-b0d3-9875c97ec97f\", \"rls_feddeb73-e0c0-4a18-8f9e-366ffcb4f0d4\", \"rls_c9fd7f4a-5345-4c92-a5c6-6024a8d8b4a3\", \"rls_fc2f4094-8978-4e51-a0a7-2c133d8f9eaf\", \"rls_425e6fa9-cf95-493e-bb80-df19486b9f04\", \"rls_7d3844f6-e09b-4ea0-abf2-774928d9ff76\", \"rls_d822b0c1-cf0d-4f63-a213-1551bafe31ac\", \"rls_705b2830-d076-4109-a7a4-c0a8b7462e59\", \"rls_f0210675-f785-48f0-96d8-b1b38d30422f\", \"rls_89e91531-44ad-40be-a441-37a5c06d2a3e\", \"rls_f5f7cf80-b327-473e-9fd6-fada678184d6\", \"rls_c2ffcb64-4286-40df-b72f-c16e06ae0605\", \"rls_f757ecfc-e4da-4440-98bd-b62b5587b91a\", \"rls_88749131-b210-4bea-938c-fd8d1f158afb\", \"rls_1270abe7-5590-429a-b457-6e021c70ed29\", \"rls_0ea0a93e-bf2d-4788-9005-83264286d9a5\", \"rls_d9753eb7-cba9-4752-8f8d-9e2006aa07f1\", \"rls_73082c51-e092-4e2e-b752-221196e719e8\", \"rls_4008e17a-0546-4cc9-a121-e20882f5f49e\", \"rls_a46dcd5d-60b6-4dad-b853-e35481bb8202\", \"rls_9b1fe264-e497-47e5-b135-e5c2dad69697\", \"rls_4a10c40c-d225-432d-af1c-420a5073df85\", \"rls_629ac79a-c0f1-4df8-9096-a0b5bc65926a\", \"rls_8f08d3f9-63c6-47d3-9529-6f69a4ad47fe\", \"rls_f19673c5-5e7b-4a1f-b9e6-e1c3ca52483a\", \"rls_10d8da65-4482-4cc4-90ab-997c05f354e2\", \"rls_233b6a75-f4fc-4c5f-a0d2-51505f546fb5\", \"rls_54169226-59bf-42a8-aa97-ba52d5b8a69a\", \"rls_18237a0d-656f-4c8a-a00a-95910819b73b\", \"rls_3b6d6517-16d4-45cc-bb3e-5c87d62c1d97\", \"rls_836a9045-ece7-44b5-94f2-8bf16bee8616\", \"rls_2d1040b4-6883-425a-936f-3f97fd97254e\", \"rls_6abef3a5-5aa0-4087-8c59-f905af5fc4fa\", \"rls_1ac2d1fd-f446-43f2-add9-49da0c5c3faf\", \"rls_acb1d7a3-dc2e-4167-958e-92ab475a4269\", \"rls_5b435198-9dee-4ab0-b800-ee93d5cece87\", \"rls_6abe151b-9fb5-45ef-9ef5-73bbe9590933\", \"rls_fc2eb9d5-8029-4753-85ad-a38528464696\", \"rls_f672ef98-6684-423f-9d5c-6b0faef1efcf\", \"rls_b55d0025-daaf-4e20-a049-e852067f4031\", \"rls_e2b70881-942b-45a5-af1a-eb8c987de704\", \"rls_cd2e26dc-f97d-4e1f-9845-3cd0d8597b4a\", \"rls_4f86f599-286b-4d1d-88ed-6f9cf531db96\", \"rls_a4b418d7-761b-4dba-9fc9-08525f11effc\", \"rls_f6485829-7c78-4a40-b1e6-dff3356e2603\", \"rls_4b9aa36b-4454-46d8-8e15-96de45b9ea23\", \"rls_7b42406f-6164-45eb-8cb6-4fc90e1e902d\", \"rls_54ea62bd-f03e-4cc6-8fe8-0e40efd04e6a\", \"rls_fab76d4e-0e47-449d-a7ba-61fce4d721dd\", \"rls_165e8d74-7130-4995-84df-785b32cb830c\", \"rls_21d6f48d-3bb8-443a-b161-cca45ad10ca5\", \"rls_dc493038-0d04-42db-9885-c221289e6fbe\", \"rls_b2a9c1b2-8129-48c5-b04e-ad6f809a45a2\", \"rls_1f7a0115-f52a-4ad2-a7ef-fea226c172e4\", \"rls_d955a4f5-46c4-4d82-89cc-3bbe31b2fe42\", \"rls_27869cdf-96f9-4f1f-9e9f-09964fa2159a\", \"rls_dd88cbe6-d19d-422a-bfca-30e40b2d3292\" ])"
],
"operatorId": "1.32",
"physical-operator": "UNNEST",
"execution-mode": "UNPARTITIONED",
"inputs": [
{
"operator": "empty-tuple-source",
"operatorId": "1.33",
"physical-operator": "EMPTY_TUPLE_SOURCE",
"execution-mode": "UNPARTITIONED"
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}