Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-60572

Index advisor incorrectly recommend an index with multiple array keys for UNNEST

    XMLWordPrintable

Details

    • Untriaged
    • 0
    • Unknown

    Description

      Repro:

      ADVISE SELECT META(b).id
      FROM default b UNNEST b.arr1 AS a
      WHERE
          b.c1 = "green"
          AND NOT (a.c2 = "abc" AND a.c3 = "abc")
      ;
      

      This generates:

          {
              "#operator": "Advise",
              "advice": {
                  "#operator": "IndexAdvice",
                  "adviseinfo": {
                      "recommended_indexes": {
                          "indexes": [
                              {
                                  "index_statement": "CREATE INDEX adv_c1_ALL_arr1_c3_ALL_arr1_c2 ON `default`(`c1`,ALL ARRAY `a`.`c3` FOR a IN `arr1` END,ALL ARRAY `a`.`c2` FOR a IN `arr1` END)",
                                  "keyspace_alias": "default_b",
                                  "recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing), 1. leading array index for unnest."
                              },
                              {
                                  "index_statement": "CREATE INDEX adv_ALL_arr1_c3_c1 ON `default`(ALL ARRAY `a`.`c3` FOR a IN `arr1` END,`c1`)",
                                  "keyspace_alias": "default_b",
                                  "recommending_rule": "Index keys follow order of predicate types: 1. leading array index for unnest, 2. equality/null/missing."
                              }
                          ]
                      }
                  }
              },
              "query": "SELECT META(b).id FROM default b UNNEST b.arr1 AS a WHERE b.c1 = \"green\" AND NOT (a.c2 = \"abc\" AND a.c3 = \"abc\");"
          }
      

      Note that the first index recommendation is invalid since it contains multiple array index keys.

      Attachments

        Issue Links

          For Gerrit Dashboard: MB-60572
          # Subject Branch Project Status CR V

          Activity

            People

              pierre.regazzoni Pierre Regazzoni
              bingjie.miao Bingjie Miao
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty