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

index advisor suggests invalid index when query has multiple array expressions

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 7.1.0
    • 6.6.3, 7.1.0
    • query
    • None
    • Untriaged
    • 1
    • Unknown

    Description

      For this query:

      SELECT META().id
      FROM `bucket`
      WHERE (ANY e IN arr1 SATISFIES e.c0 = "1111" END
              OR ANY e IN arr2 SATISFIES e.c0 = "1111" END)
          AND c1 = "green"
          AND (c2 = "abc"
              OR c3 = "abc")

      index advisor suggests:

       

      CREATE INDEX adv_c1_c2_c3_DISTINCT_arr1_c0_DISTINCT_arr2_c0 ON `bucket`(`c1`,`c2`,`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END);
      CREATE INDEX adv_c1_DISTINCT_arr2_c0_c2 ON `bucket`(`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c2`);
      CREATE INDEX adv_c1_c3_DISTINCT_arr2_c0 ON `bucket`(`c1`,`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END);
      

      The first index is invalid as it has multiple array expressions.  

      If the array expressions in the query are at the end i.e.

      SELECT META().id
      FROM `bucket`
      WHERE c1 = "green"
          AND (c2 = "abc"
              OR c3 = "abc")
          AND (ANY e IN arr1 SATISFIES e.c0 = "1111" END
              OR ANY e IN arr2 SATISFIES e.c0 = "1111" END)

      then I get valid indexes from the advisor i.e.

       

      CREATE INDEX adv_c2_c1_DISTINCT_arr1_c0 ON `bucket`(`c2`,`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END);
      CREATE INDEX adv_c1_DISTINCT_arr2_c0_c2 ON `bucket`(`c1`,DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c2`);
      CREATE INDEX adv_c3_DISTINCT_arr1_c0_c1 ON `bucket`(`c3`,DISTINCT ARRAY `e`.`c0` FOR e in `arr1` END,`c1`);
      CREATE INDEX adv_DISTINCT_arr2_c0_c1_c3 ON `bucket`(DISTINCT ARRAY `e`.`c0` FOR e in `arr2` END,`c1`,`c3`);
      

       

       

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              pierre.regazzoni Pierre Regazzoni
              hitesh.walia Hitesh Walia
              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