The performance is different when filtering on string and int, any help?

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?

Both query return 1 record(exactly same record) and bucket has 4.5m different records, classType = “AccountOption” has 50k records.

1st query result:

2nd query result:

For the first query, you need a different index whose leading key is “superior”.

CREATE INDEX AccountOption_idx ON default(login,server,level,superior,lowerLimitRebate,upperLimitRebate,rebateType,mobile,bankAccountName,bankAccountNo,bankName,bankBranchName,englishName) WHERE (classType = “AccountOption”)

This is my 1st query’s index, what should I change?
Should I add another new index which have superior and classType only?

Hi,

Leading key means that “superior” should be the first key in your second index.

I see, got it.
thx for the help

1 Like