Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
Elixir, 7.1.2
-
7.1.2-3308
-
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
- is caused by
-
MB-31640 subqueries should be advised, explained and monitored
-
- Closed
-
| For Gerrit Dashboard: MB-52689 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 176692,2 | MB-52689 Save/restore of advisor structure for subquery | master | query | Status: MERGED | +2 | +1 |
| 176749,2 | MB-52689 Save/restore of advisor structure for subquery | neo | query | Status: MERGED | +2 | +1 |
Activity
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
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 couchbase-server-7.2.0-1472 contains query commit 07773c6 with commit message:
MB-52689Save/restore of advisor structure for subquery