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

Advise with subquery returns No index recommendation at this time: no keyspace found

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Yes

    Description

      to repro:

      • install travel-sample
      • run following query: ADVISE SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );

      cbq> advise SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );
      {
          "requestID": "6813647f-d445-4155-b08c-9dde8be41dd0",
          "signature": "json",
          "results": [
          {
              "#operator": "Advise",
              "advice": {
                  "#operator": "IndexAdvice",
                  "adviseinfo": {
                      "recommended_indexes": "No index recommendation at this time: no keyspace found."
                  }
              },
              "query": "SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );",
              "~subqueries": [
                  {
                      "#operator": "IndexAdvice",
                      "adviseinfo": {
                          "current_indexes": [
                              {
                                  "index_statement": "CREATE PRIMARY INDEX def_inventory_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
                                  "keyspace_alias": "airport"
                              }
                          ],
       

       

      Pre subquery advise, we get:

      cbq> advise SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );
      {
          "requestID": "95b446ed-74eb-4f4d-b54f-a65f5b2b9f31",
          "signature": "json",
          "results": [
          {
              "#operator": "Advise",
              "advice": {
                  "#operator": "IndexAdvice",
                  "adviseinfo": {
                      "current_indexes": [
                          {
                              "index_statement": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)",
                              "index_status": "THIS IS AN OPTIMAL COVERING INDEX.",
                              "keyspace_alias": "landmark_t1"
                          }
                      ],
                      "recommended_indexes": "No index recommendation at this time."
                  }
              },
              "query": "SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );"
          }
          ],
       

      Attachments

        Issue Links

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

          Activity

            Build couchbase-server-7.2.0-1472 contains query commit 07773c6 with commit message:
            MB-52689 Save/restore of advisor structure for subquery

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.2.0-1472 contains query commit 07773c6 with commit message: MB-52689 Save/restore of advisor structure for subquery

            Build couchbase-server-8.0.0-1037 contains query commit 07773c6 with commit message:
            MB-52689 Save/restore of advisor structure for subquery

            build-team Couchbase Build Team added a comment - Build couchbase-server-8.0.0-1037 contains query commit 07773c6 with commit message: MB-52689 Save/restore of advisor structure for subquery

            Build couchbase-server-7.1.2-3336 contains query commit a047938 with commit message:
            MB-52689 Save/restore of advisor structure for subquery

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.2-3336 contains query commit a047938 with commit message: MB-52689 Save/restore of advisor structure for subquery

            Verified on 7.1.2-3336, 7.2.0-1472 and 8.0.0-1037

            cbq> advise SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );
            {
                "requestID": "1fcea9ab-cf8a-4425-9edd-db1b28a20941",
                "signature": "json",
                "results": [
                {
                    "#operator": "Advise",
                    "advice": {
                        "#operator": "IndexAdvice",
                        "adviseinfo": {
                            "current_indexes": [
                                {
                                    "index_statement": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)",
                                    "index_status": "THIS IS AN OPTIMAL COVERING INDEX.",
                                    "keyspace_alias": "landmark_t1"
                                }
                            ],
                            "recommended_indexes": "No index recommendation at this time."
                        }
                    },
                    "query": "SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );",
                    "~subqueries": [
                        {
                            "#operator": "IndexAdvice",
                            "adviseinfo": {
                                "current_indexes": [
                                    {
                                        "index_statement": "CREATE PRIMARY INDEX def_inventory_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
                                        "keyspace_alias": "airport"
                                    }
                                ],
                                "recommended_indexes": {
                                    "covering_indexes": [
                                        {
                                            "index_statement": "CREATE INDEX adv_city ON `default`:`travel-sample`.`inventory`.`airport`(`city` INCLUDE MISSING)",
                                            "keyspace_alias": "airport"
                                        }
                                    ],
                                    "indexes": [
                                        {
                                            "index_statement": "CREATE INDEX adv_city ON `default`:`travel-sample`.`inventory`.`airport`(`city` INCLUDE MISSING)",
                                            "index_status": "SAME ONLINE INDEX EXISTS: CREATE INDEX def_inventory_airport_city ON `default`:`travel-sample`.`inventory`.`airport`(`city`)",
                                            "keyspace_alias": "airport",
                                            "recommending_rule": "Leading Index Key Missing."
                                        }
                                    ]
                                }
                            },
                            "subquery": "select raw cover ((`airport`.`city`)) from `default`:`travel-sample`.`inventory`.`airport`"
                        }
                    ]
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "1.596191051s",
                    "executionTime": "1.595985356s",
                    "resultCount": 1,
                    "resultSize": 2372,
                    "serviceLoad": 6
                }
            }
             

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 7.1.2-3336, 7.2.0-1472 and 8.0.0-1037 cbq> advise SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport ); {     "requestID" : "1fcea9ab-cf8a-4425-9edd-db1b28a20941" ,     "signature" : "json" ,     "results" : [     {         "#operator" : "Advise" ,         "advice" : {             "#operator" : "IndexAdvice" ,             "adviseinfo" : {                 "current_indexes" : [                     {                         "index_statement" : "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)" ,                         "index_status" : "THIS IS AN OPTIMAL COVERING INDEX." ,                         "keyspace_alias" : "landmark_t1"                     }                 ],                 "recommended_indexes" : "No index recommendation at this time."             }         },         "query" : "SELECT t1.city FROM `travel-sample`.inventory.landmark t1 WHERE t1.city IN (SELECT RAW city FROM `travel-sample`.inventory.airport );" ,         "~subqueries" : [             {                 "#operator" : "IndexAdvice" ,                 "adviseinfo" : {                     "current_indexes" : [                         {                             "index_statement" : "CREATE PRIMARY INDEX def_inventory_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`" ,                             "keyspace_alias" : "airport"                         }                     ],                     "recommended_indexes" : {                         "covering_indexes" : [                             {                                 "index_statement" : "CREATE INDEX adv_city ON `default`:`travel-sample`.`inventory`.`airport`(`city` INCLUDE MISSING)" ,                                 "keyspace_alias" : "airport"                             }                         ],                         "indexes" : [                             {                                 "index_statement" : "CREATE INDEX adv_city ON `default`:`travel-sample`.`inventory`.`airport`(`city` INCLUDE MISSING)" ,                                 "index_status" : "SAME ONLINE INDEX EXISTS: CREATE INDEX def_inventory_airport_city ON `default`:`travel-sample`.`inventory`.`airport`(`city`)" ,                                 "keyspace_alias" : "airport" ,                                 "recommending_rule" : "Leading Index Key Missing."                             }                         ]                     }                 },                 "subquery" : "select raw cover ((`airport`.`city`)) from `default`:`travel-sample`.`inventory`.`airport`"             }         ]     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "1.596191051s" ,         "executionTime" : "1.595985356s" ,         "resultCount" : 1 ,         "resultSize" : 2372 ,         "serviceLoad" : 6     } }

            Build couchbase-server-7.2.0-5000 contains query commit a047938 with commit message:
            MB-52689 Save/restore of advisor structure for subquery

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.2.0-5000 contains query commit a047938 with commit message: MB-52689 Save/restore of advisor structure for subquery

            People

              pierre.regazzoni Pierre Regazzoni
              pierre.regazzoni Pierre Regazzoni
              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