I try left join query The bucket cms-deploy-dev is empty and query must issue result but it is empty!
What is a reason?
SELECT col_data.id AS _k,
dex as c
FROM `cms-data` col_data
LEFT JOIN `cms-deploy-dev` as dex ON dex.id = col_data.id
AND dex.status = 'progress'
AND dex.`hash` != col_data.`hash`
AND dex._scope=col_data._scope
AND dex._type= col_data._type
WHERE col_data._scope='test' AND col_data._type IN ['achievement', 'achievement_order']
Indexes are:
CREATE INDEX Index_Type_scope__ ON `cms-data`(`_type`,`_scope`) PARTITION BY HASH(`_type`,`_scope`)
CREATE INDEX adv_status_type_hash_scope_id ON `cms-deploy-dev`(`status`,`_type`,`hash`,`_scope`,`id`) PARTITION BY HASH(`_scope`)
Plan is
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 4,
"execTime": "4.796µs",
"servTime": "2.643568ms"
},
"privileges": {
"List": [
{
"Target": "default:cms-data",
"Priv": 7,
"Props": 0
},
{
"Target": "default:cms-deploy-dev",
"Priv": 7,
"Props": 0
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "4.623µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 3,
"#phaseSwitches": 15,
"execTime": "54.196µs",
"kernTime": "4.004µs",
"servTime": "3.646981ms"
},
"as": "col_data",
"index": "Index_Type_scope__",
"index_id": "5c2e7c852de8e954",
"index_partition_by": "[`_type`, `_scope`]",
"index_projection": {
"primary_key": true
},
"keyspace": "cms-data",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"achievement\"",
"inclusion": 3,
"low": "\"achievement\""
},
{
"high": "\"rollingslots\"",
"inclusion": 3,
"low": "\"rollingslots\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"achievement_order\"",
"inclusion": 3,
"low": "\"achievement_order\""
},
{
"high": "\"rollingslots\"",
"inclusion": 3,
"low": "\"rollingslots\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.003",
"#time_absolute": 0.0037011769999999995
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 3,
"#phaseSwitches": 18,
"execTime": "71.584µs",
"kernTime": "3.729177ms",
"servTime": "1.234519ms"
},
"as": "col_data",
"keyspace": "cms-data",
"namespace": "default",
"#time_normal": "00:00.001",
"#time_absolute": 0.0013061029999999999
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "735.4µs",
"kernTime": "240ns",
"state": "running"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 3,
"#itemsOut": 3,
"#phaseSwitches": 16,
"execTime": "97.012µs",
"kernTime": "5.064408ms"
},
"condition": "(((`col_data`.`_scope`) = \"rollingslots\") and ((`col_data`.`_type`) in [\"achievement\", \"achievement_order\"]))",
"#time_normal": "00:00.000",
"#time_absolute": 0.000097012
},
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 3,
"#phaseSwitches": 25,
"execTime": "132.152µs",
"kernTime": "17.417013ms"
},
"alias": "dex",
"filter": "(not ((`dex`.`hash`) = (`col_data`.`hash`)))",
"on_clause": "((((`dex`.`id`) = (`col_data`.`id`)) and ((`dex`.`status`) = \"progress\")) and (not ((`dex`.`hash`) = (`col_data`.`hash`))))",
"outer": true,
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 6,
"execTime": "955.418µs",
"kernTime": "12.573µs",
"state": "running"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#phaseSwitches": 9,
"execTime": "30.779µs",
"servTime": "12.106126ms"
},
"as": "dex",
"index": "adv_status_type_hash_scope_id",
"index_id": "839b2d8cf8988ca7",
"index_partition_by": "[`_scope`]",
"index_projection": {
"primary_key": true
},
"keyspace": "cms-deploy-dev",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "\"progress\"",
"inclusion": 3,
"low": "\"progress\""
},
{
"inclusion": 0
},
{
"high": "(`col_data`.`hash`)",
"inclusion": 0,
"low": "null"
},
{
"inclusion": 0
},
{
"high": "(`col_data`.`id`)",
"inclusion": 3,
"low": "(`col_data`.`id`)"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"progress\"",
"inclusion": 3,
"low": "\"progress\""
},
{
"inclusion": 0
},
{
"inclusion": 0,
"low": "(`col_data`.`hash`)"
},
{
"inclusion": 0
},
{
"high": "(`col_data`.`id`)",
"inclusion": 3,
"low": "(`col_data`.`id`)"
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.012",
"#time_absolute": 0.012136905
},
{
"#operator": "Fetch",
"#stats": {
"#phaseSwitches": 12,
"execTime": "27.975µs",
"kernTime": "12.164551ms"
},
"as": "dex",
"keyspace": "cms-deploy-dev",
"namespace": "default",
"nested_loop": true,
"#time_normal": "00:00.000",
"#time_absolute": 0.000027975000000000002
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000955418
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000132152
},
{
"#operator": "InitialProject",
"#stats": {
"#phaseSwitches": 6,
"execTime": "11.329µs",
"kernTime": "17.610448ms"
},
"result_terms": [
{
"as": "_k",
"expr": "(`col_data`.`id`)"
},
{
"as": "c",
"expr": "`dex`"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000011329000000000001
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0007354
},
{
"#operator": "Stream",
"#stats": {
"#phaseSwitches": 2,
"execTime": "1.167µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000001167
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000046230000000000005
},
"~versions": [
"7.0.0-N1QL",
"7.0.1-6102-enterprise"
],
"#time_normal": "00:00.002",
"#time_absolute": 0.0026483640000000003
}