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

Improve index advisor to handle `self` queries

    XMLWordPrintable

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 7.0.0-Beta1
    • feature-backlog
    • query
    • None
    • 1

    Description

      SELECT ENCODED_SIZE(d)
      FROM `EverC` AS d
      WHERE ENCODED_SIZE(d) > 230 
      and ENCODED_SIZE(d) < 247;
      

      Above query recommends covered secondary index.

      SELECT ENCODED_SIZE(self)
      FROM `EverC` 
      WHERE ENCODED_SIZE(self) > 230 
      and ENCODED_SIZE(self) < 247;
      

      But the same query with "self" only recommends the primary index.  I understand that the indexer may not be able to use the planner for `self` but in the advisor perhaps we could have a special message that says "if using `self`, please replace with a proper alias to advise on the query.  A query that uses `self` will function in the same way"

      Attachments

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

        Activity

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          In my opinion this is corner case. It is much more complex.

          JOSN has no structure and it inherits implicit or explicit alias of collection as name. Once name is derived it must be used that in reset of the query.

          INSERT INTO default VALUES("k01",{"a":1});
          

          Implicit alias

          SELECT default FROM default;
          {
                  "default": {
                      "a": 1
                  }
              }
          

          Explicit Alias

          SELECT d FROM default AS d;
            {
                  "d": {
                      "a": 1
                  }
              }
          
          

          self in query (self is after alias another layer of object. encoded_size() of self include extra object length) with EXPLICIT alias, Implicit alias

          SELECT self FROM default AS d;
            {
                  "$1": {
                      "d": {
                          "a": 1
                      }
                  }
              }
           
          SELECT self FROM default;
          {
                  "$1": {
                      "default": {
                          "a": 1
                      }
                  }
              }
          

          Due to no-schema we need to stay with to same naming convention. We can't map one to many

          select default from default AS d;
           {}
          

          In above case user explicitly aliased to d. We don't use default as document it will considered as d.default i.e default as field in the document.

          Treating self same as index is not possible due to JOINs, LET,WITH,LETTING variables because all those combined treated as self. Depends on where self used things can change

          SELECT self FROM default AS d let b = 20;
              {
                  "$1": {
                      "b": 20,
                      "d": {
                          "a": 1
                      }
                  }
              }
          

          Location of self used can change content.

           SELECT ENCODED_SIZE(self) AS projsize, self FROM `default` LET sizeself= ENCODED_SIZE(self), size= ENCODED_SIZE(default);
          {
                  "$1": {
                      "default": {
                          "a": 1
                      },
                      "size": 7,
                      "sizeself": 19
                  },
                  "projsize": 42
              }
          

          On top of that we need to sargable planner may need to do repeat same thing 2-3times. That will add up time no reason for this corner case. Best way avoid this document not to use self in the query.

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited In my opinion this is corner case. It is much more complex. JOSN has no structure and it inherits implicit or explicit alias of collection as name. Once name is derived it must be used that in reset of the query. INSERT INTO default VALUES( "k01" ,{ "a" : 1 }); Implicit alias SELECT default FROM default ; { "default" : { "a" : 1 } } Explicit Alias SELECT d FROM default AS d; { "d" : { "a" : 1 } } self in query (self is after alias another layer of object. encoded_size() of self include extra object length) with EXPLICIT alias, Implicit alias SELECT self FROM default AS d; { "$1" : { "d" : { "a" : 1 } } }   SELECT self FROM default ; { "$1" : { "default" : { "a" : 1 } } } Due to no-schema we need to stay with to same naming convention. We can't map one to many select default from default AS d; {} In above case user explicitly aliased to d. We don't use default as document it will considered as d.default i.e default as field in the document. Treating self same as index is not possible due to JOINs, LET,WITH,LETTING variables because all those combined treated as self. Depends on where self used things can change SELECT self FROM default AS d let b = 20 ; { "$1" : { "b" : 20 , "d" : { "a" : 1 } } } Location of self used can change content. SELECT ENCODED_SIZE(self) AS projsize, self FROM ` default ` LET sizeself= ENCODED_SIZE(self), size= ENCODED_SIZE( default ); { "$1" : { "default" : { "a" : 1 }, "size" : 7 , "sizeself" : 19 }, "projsize" : 42 } On top of that we need to sargable planner may need to do repeat same thing 2-3times. That will add up time no reason for this corner case. Best way avoid this document not to use self in the query.
          perry Perry Krug added a comment -

          Understood.  Maybe it would be enough if the index advisor simply called out to the user that the use of 'self' was not supported by the advisor and for the user to replace it with an actual alias.

          perry Perry Krug added a comment - Understood.  Maybe it would be enough if the index advisor simply called out to the user that the use of 'self' was not supported by the advisor and for the user to replace it with an actual alias.

          People

            kamini.jagtiani Kamini Jagtiani
            perry Perry Krug
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty