How to properly index array field?

Couchbase Server
,
1

Hi, I tried multiple method from documentation, yet my query on array field still not using index. How can I properly index array?

couchbase version: 5.0-beta

my query:

SELECT default.* FROM default WHERE ARRAY_CONTAINS(AArray, 10020) LIMIT 500

I tried following indexes:

  • idx_dir_arr on ["`AArray`"]
  • idx_arr on ["(all (`AArray`))"]
  • aarray_index on ["(distinct (array (`v`.`flight`) for `v` in `AArray` end))"] (this one is wrong, I have no flight field in AArray)

query explain:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 2,
    "execTime": "1.235µs",
    "kernTime": "3.45354312s"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "execTime": "1.934µs",
        "kernTime": "3.452684469s",
        "servTime": "656.542µs"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:default",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 3,
          "execTime": "1.626µs",
          "kernTime": "3.452680038s"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 2,
              "execTime": "967ns",
              "kernTime": "3.452848984s"
            },
            "~children": [
              {
                "#operator": "IndexScan2",
                "#stats": {
                  "#itemsOut": 4113,
                  "#phaseSwitches": 16455,
                  "execTime": "2.530715178s",
                  "kernTime": "150.986954ms",
                  "servTime": "771.882473ms",
                  "state": "kernel"
                },
                "index": "idx_dir_arr",
                "index_id": "b1e2ac4b7beec754",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "default",
                "namespace": "default",
                "spans": [
                  {
                    "range": [
                      {
                        "inclusion": 0,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:03.3025",
                "#time_absolute": 3.302597651
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 3601,
                  "#itemsOut": 3088,
                  "#phaseSwitches": 13393,
                  "execTime": "10.809026ms",
                  "kernTime": "3.283767638s",
                  "servTime": "159.072673ms",
                  "state": "services"
                },
                "keyspace": "default",
                "namespace": "default",
                "#time_normal": "00:00.1698",
                "#time_absolute": 0.169881699
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 5,
                  "execTime": "5.15µs",
                  "kernTime": "3.452839159s"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 3088,
                      "#itemsOut": 509,
                      "#phaseSwitches": 7195,
                      "execTime": "48.527975ms",
                      "kernTime": "3.40515603s",
                      "state": "kernel"
                    },
                    "condition": "array_contains((`default`.`AArray`), 10020)",
                    "#time_normal": "00:00.0485",
                    "#time_absolute": 0.048527975
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 509,
                      "#itemsOut": 509,
                      "#phaseSwitches": 2037,
                      "execTime": "29.23106ms",
                      "kernTime": "3.424481127s",
                      "state": "kernel"
                    },
                    "result_terms": [
                      {
                        "expr": "`default`",
                        "star": true
                      }
                    ],
                    "#time_normal": "00:00.0292",
                    "#time_absolute": 0.02923106
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 509,
                      "#itemsOut": 509,
                      "#phaseSwitches": 2043,
                      "execTime": "361.118µs",
                      "kernTime": "3.452476696s"
                    },
                    "#time_normal": "00:00.0003",
                    "#time_absolute": 0.000361118
                  }
                ],
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.000005150000000000001
              }
            ],
            "#time_normal": "00:00",
            "#time_absolute": 0
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 501,
              "#itemsOut": 500,
              "#phaseSwitches": 2007,
              "execTime": "79.543µs",
              "kernTime": "3.452588296s"
            },
            "expr": "500",
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.00007954300000000001
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000001626
      },
      "#time_normal": "00:00.0006",
      "#time_absolute": 0.000658476
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 500,
        "#itemsOut": 500,
        "#phaseSwitches": 1007,
        "execTime": "40.908µs",
        "kernTime": "3.453486735s"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.000040908
    }
  ],
  "~versions": [
    "1.7.0-N1QL",
    "5.0.0-2873-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000001235
}

Thanks

2 
CREATE INDEX array_index ON default( DISTINCT AArray);
SELECT default.* FROM default WHERE ANY v IN AArray SATISFIES v = 10020 END LIMIT 500;

If that is not worked post the sample document or AArray portion.

FYI: The explain can be generated by adding EXPLAIN in front of the query statement. The one you posted is profile information of the query which can be used to understand improve query performance by looking the time each operator is taken and comparing In/Out Items etc.

1 Like
3

Hi it works.

However if I complex this query by a little bit, the query become slow again.

my query:

SELECT default.AString FROM default WHERE ANY v IN AArray SATISFIES v = 10020 END AND -AInt IS NOT MISSING ORDER BY -AInt LIMIT 500

I indexed AInt according to post @13173

CREATE INDEX neg_int ON default(-AInt);
SELECT default.* FROM default WHERE -AInt IS NOT MISSING ORDER BY -AInt LIMIT 50;

If I execute only with ORDER BY or only with ANY v IN AArray SATISFIES v = 10020 END, both queries are very fast. Yet if I compound them into one query like above, it will take about 4 second to execute.

query explain:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "OrderedIntersectScan",
            "scans": [
              {
                "#operator": "IndexScan2",
                "index": "neg_only_int",
                "index_id": "a3d97094df534a3e",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "default",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "inclusion": 1,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan2",
                  "index": "array_index",
                  "index_id": "7a3b90c6716808",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "default",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10020",
                          "inclusion": 3,
                          "low": "10020"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              }
            ]
          },
          {
            "#operator": "Fetch",
            "keyspace": "default",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "maxParallelism": 1,
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(any `v` in (`default`.`AArray`) satisfies (`v` = 10020) end and ((-(`default`.`AInt`)) is not missing))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`default`.`AString`)"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "500"
      }
    ]
  },
  "text": "SELECT default.AString FROM default WHERE ANY v IN AArray SATISFIES v = 10020 END AND -AInt IS NOT MISSING ORDER BY -AInt LIMIT 500"
}

and query profile:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "OrderedIntersectScan",
            "scans": [
              {
                "#operator": "IndexScan2",
                "index": "neg_only_int",
                "index_id": "a3d97094df534a3e",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "default",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "inclusion": 1,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan2",
                  "index": "array_index",
                  "index_id": "7a3b90c6716808",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "default",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10020",
                          "inclusion": 3,
                          "low": "10020"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              }
            ]
          },
          {
            "#operator": "Fetch",
            "keyspace": "default",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "maxParallelism": 1,
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(any `v` in (`default`.`AArray`) satisfies (`v` = 10020) end and ((-(`default`.`AInt`)) is not missing))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`default`.`AString`)"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "500"
      }
    ]
  },
  "text": "SELECT default.AString FROM default WHERE ANY v IN AArray SATISFIES v = 10020 END AND -AInt IS NOT MISSING ORDER BY -AInt LIMIT 500"
}
4

Use composite index.

CREATE INDEX ix1 ON default(-AInt, DISTINCT AArray, AString);