The environment is 6 VMs, 8G RAM 8CPU each.
Using N1QL
1st query takes 2.5s
prepare select login, server from default where classType = "AccountOption" and superior = "130" and server = "1" and login is not missing and rebateType is not missing and level is not missing and upperLimitRebate is not missing and lowerLimitRebate is not missing
2nd query takes below 100ms
> prepare select login, server from default where
> classType = “AccountOption”
> and superior is not missing
> and server = “1”
> and login = 130
> and rebateType is not missing
> and level is not missing
> and upperLimitRebate is not missing
> and lowerLimitRebate is not missing
1st query explain
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`default`.`login`))",
"cover ((`default`.`server`))",
"cover ((`default`.`level`))",
"cover ((`default`.`superior`))",
"cover ((`default`.`lowerLimitRebate`))",
"cover ((`default`.`upperLimitRebate`))",
"cover ((`default`.`rebateType`))",
"cover ((`default`.`mobile`))",
"cover ((`default`.`bankAccountName`))",
"cover ((`default`.`bankAccountNo`))",
"cover ((`default`.`bankName`))",
"cover ((`default`.`bankBranchName`))",
"cover ((`default`.`englishName`))",
"cover ((meta(`default`).`id`))"
],
"filter_covers": {
"cover ((`default`.`classType`))": "AccountOption"
},
"index": "AccountOption_idx",
"index_id": "9151f9a6a92c2970",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 1,
"Low": [
"null",
"\"1\"",
"null",
"\"130\"",
"null",
"null",
"null"
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((((((cover ((`default`.`classType`)) = \"AccountOption\") and (cover ((`default`.`superior`)) = \"130\")) and (cover ((`default`.`server`)) = \"1\")) and (cover ((`default`.`login`)) is not missing)) and (cover ((`default`.`rebateType`)) is not missing)) and (cover ((`default`.`level`)) is not missing)) and (cover ((`default`.`upperLimitRebate`)) is not missing)) and (cover ((`default`.`lowerLimitRebate`)) is not missing))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`default`.`login`))"
},
{
"expr": "cover ((`default`.`server`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "select login, server from default where\nclassType = \"AccountOption\"\nand superior = \"130\"\nand server = \"1\"\nand login is not missing\nand rebateType is not missing\nand level is not missing\nand upperLimitRebate is not missing\nand lowerLimitRebate is not missing"
}
]
2nd query explain
[ { "plan": { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "covers": [ "cover ((`default`.`login`))", "cover ((`default`.`server`))", "cover ((`default`.`level`))", "cover ((`default`.`superior`))", "cover ((`default`.`lowerLimitRebate`))", "cover ((`default`.`upperLimitRebate`))", "cover ((`default`.`rebateType`))", "cover ((`default`.`mobile`))", "cover ((`default`.`bankAccountName`))", "cover ((`default`.`bankAccountNo`))", "cover ((`default`.`bankName`))", "cover ((`default`.`bankBranchName`))", "cover ((`default`.`englishName`))", "cover ((meta(`default`).`id`))" ], "filter_covers": { "cover ((`default`.`classType`))": "AccountOption" }, "index": "AccountOption_idx", "index_id": "9151f9a6a92c2970", "keyspace": "default", "namespace": "default", "spans": [ { "Range": { "High": [ "130", "successor(\"1\")" ], "Inclusion": 1, "Low": [ "130", "\"1\"", "null", "null", "null", "null", "null" ] } } ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Filter", "condition": "((((((((cover ((`default`.`classType`)) = \"AccountOption\") and (cover ((`default`.`superior`)) is not missing)) and (cover ((`default`.`server`)) = \"1\")) and (cover ((`default`.`login`)) = 130)) and (cover ((`default`.`rebateType`)) is not missing)) and (cover ((`default`.`level`)) is not missing)) and (cover ((`default`.`upperLimitRebate`)) is not missing)) and (cover ((`default`.`lowerLimitRebate`)) is not missing))" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "cover ((`default`.`login`))" }, { "expr": "cover ((`default`.`server`))" } ] }, { "#operator": "FinalProject" } ] } } ] }, "text": "select login, server from default where\nclassType = \"AccountOption\"\nand superior is not missing\nand server = \"1\"\nand login = 130\nand rebateType is not missing\nand level is not missing\nand upperLimitRebate is not missing\nand lowerLimitRebate is not missing" } ]
any way to improve the performance of 1st query near the 2nd query? Am I doing something wrong?