Details
-
Bug
-
Resolution: Fixed
-
Critical
-
7.6.0
-
7.6.0-2149
-
Untriaged
-
0
-
Yes
Description
buckets
travel-sample
default
standard_bucket0
several queries that were using hash join in 7.2.4 are no longer using hash join in 7.6
only primary index exists for default
travel-sample indexes that are created with the load are present
Query with subqueries
EXPLAIN select * from (select * from default as d_main) d INNER JOIN (select * from default d1 where d1.name == 'employee-9') d2 ON (d.d_main.name = d2.d1.name)
|
[
|
{
|
"cardinality": 32833.44,
|
"cost": 650922.2744924887,
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "PrimaryScan3",
|
"as": "d1",
|
"index": "#primary",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 575.7822852450187,
|
"fr_cost": 12.31113812651491,
|
"size": 37
|
},
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "d1",
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 3474.2135420048257,
|
"fr_cost": 25.90409135872231,
|
"size": 1015
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((`d1`.`name`) = \"employee-9\")",
|
"optimizer_estimates": {
|
"cardinality": 181.20000000000002,
|
"cost": 3531.942167140022,
|
"fr_cost": 26.22268200516379,
|
"size": 1015
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 181.20000000000002,
|
"cost": 3537.7150296535415,
|
"fr_cost": 26.25454106980794,
|
"size": 1015
|
},
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Alias",
|
"as": "d2",
|
"optimizer_estimates": {
|
"cardinality": 181.20000000000002,
|
"cost": 3537.7150296535415,
|
"fr_cost": 26.25454106980794,
|
"size": 1015
|
},
|
"secondary_term": true
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "NestedLoopJoin",
|
"alias": "d",
|
"on_clause": "((((`d`.`d_main`).`name`) = ((`d2`.`d1`).`name`)))",
|
"optimizer_estimates": {
|
"cardinality": 32833.44,
|
"cost": 649442.946737076,
|
"fr_cost": 52.3707135783915,
|
"size": 2030
|
},
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "d",
|
"expr": "(select self.* from `default`:`default` as `d_main`)",
|
"nested_loop": true,
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 3531.942167140022,
|
"fr_cost": 25.93595042336646,
|
"size": 1015
|
},
|
"uncorrelated": true
|
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 32833.44,
|
"cost": 650922.2744924887,
|
"fr_cost": 52.41576909969578,
|
"size": 2030
|
},
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "select * from (select * from default as d_main) d INNER JOIN (select * from default d1 where d1.name == 'employee-9') d2 ON (d.d_main.name = d2.d1.name)",
|
"~subqueries": [
|
{
|
"correlated": false,
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "PrimaryScan3",
|
"as": "d_main",
|
"index": "#primary",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 575.7822852450187,
|
"fr_cost": 12.31113812651491,
|
"size": 37
|
},
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "d_main",
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 3474.2135420048257,
|
"fr_cost": 25.90409135872231,
|
"size": 1015
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 1812,
|
"cost": 3531.942167140022,
|
"fr_cost": 25.93595042336646,
|
"size": 1015
|
},
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"subquery": "select self.* from `default`:`default` as `d_main`"
|
}
|
]
|
}
|
]
|
This query does not even use any buckets
EXPLAIN select * from ([{'name' : 'employee-9'}, {'name': 'employee-10'}]) d INNER JOIN ([{'name' : 'employee-9'},{'name' : 'employee-10'}]) d2 ON (d.name = d2.name)
|
[
|
{
|
"cardinality": 0.4,
|
"cost": 0.07062741699796954,
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "d2",
|
"expr": "[{\"name\": \"employee-9\"}, {\"name\": \"employee-10\"}]",
|
"optimizer_estimates": {
|
"cardinality": 2,
|
"cost": 0.016,
|
"fr_cost": 0.008,
|
"size": 64
|
},
|
"uncorrelated": true
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "NestedLoopJoin",
|
"alias": "d",
|
"on_clause": "(((`d`.`name`) = (`d2`.`name`)))",
|
"optimizer_estimates": {
|
"cardinality": 0.4,
|
"cost": 0.06610193359837563,
|
"fr_cost": 0.06610193359837563,
|
"size": 128
|
},
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "d",
|
"expr": "[{\"name\": \"employee-9\"}, {\"name\": \"employee-10\"}]",
|
"nested_loop": true,
|
"optimizer_estimates": {
|
"cardinality": 2,
|
"cost": 0.016,
|
"fr_cost": 0.008,
|
"size": 64
|
},
|
"uncorrelated": true
|
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 0.4,
|
"cost": 0.07062741699796954,
|
"fr_cost": 0.07062741699796954,
|
"size": 128
|
},
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "select * from ([{'name' : 'employee-9'}, {'name': 'employee-10'}]) d INNER JOIN ([{'name' : 'employee-9'},{'name' : 'employee-10'}]) d2 ON (d.name = d2.name)"
|
}
|
]
|
There are other tests failing for this same reason, but I believe that they are all probably related to the same issue
http://qa.sc.couchbase.com/job/test_suite_executor/682458/consoleFull