Details
-
Bug
-
Resolution: Fixed
-
Major
-
4.6.0, 5.0.0
-
Untriaged
-
Centos 64-bit
-
Unknown
Description
Summary of results:
| Goal | 650 queries/sec |
| Actual | 40-70 queries/sec |
See also: http://showfast.sc.couchbase.com/
Setup:
- 4 data nodes, 1 index node, 1 query node.
- 3 Buckets, 10M documents in bucket-1 and bucket-2, 1M documents in bucket-3.
- MOI, indexer.settings.gc_percent=200
- 15K mutations/sec/bucket (bucket-1 and bucket-2 only), 720 N1QL workers.
Configuration files:
https://raw.githubusercontent.com/couchbase/perfrunner/master/tests/n1ql/n1ql_thr_JOIN_Q11_10M_gsi_moi_ok.test
https://raw.githubusercontent.com/couchbase/perfrunner/master/tests/n1ql/n1ql_thr_JOIN_Q11_10M_gsi_moi_false.test
Sample documents:
{
|
"category": 1,
|
"city": "210bf5",
|
"realm": "782e95",
|
"name": "d76d72 8332cc",
|
"capped_small": "n1ql_100_0",
|
"country": "769823",
|
"topics": [
|
"n1ql-000000000013",
|
"n1ql-000000000021",
|
"n1ql-000000000025",
|
"n1ql-000000000031"
|
],
|
"coins": 11.13,
|
"body": "d76d728332ccda0b751f4880210bf5782e9504595a7698234d260b6d147c5cd2d76d728332ccda0b751f4880210bf5782e9504595a7698234d260b6d147c5cd2d76d728332ccda0b751f4880210bf5782e9504595a7698234d260b6d147c5cd2d76d728332ccda0b751f4880210bf5782e9504595a7698234d260b6d147c5cd2d76d728332ccda0b751f4880210bf5782e9504595a7698234d260b6d147c5cd2d76d72",
|
"gmtime": [
|
1972,
|
3,
|
3,
|
0,
|
0,
|
0,
|
4,
|
63,
|
0
|
],
|
"county": "4d260b",
|
"state": "AL",
|
"street": "6d147c5c",
|
"alt_email": "8332cc@1f4880.com",
|
"year": 1998,
|
"full_state": "Colorado",
|
"achievements": [
|
0,
|
0,
|
121,
|
193
|
],
|
"email": "da0b75@1f4880.com"
|
}
|
{
|
"owner": "n1ql-000000000001",
|
"capped_large": "n1ql_1000_0",
|
"replies": [
|
{
|
"user": "n1ql-000000000538"
|
},
|
{
|
"user": "n1ql-000000000539"
|
},
|
{
|
"user": "n1ql-000000000540"
|
},
|
{
|
"user": "n1ql-000000000541"
|
},
|
{
|
"user": "n1ql-000000000542"
|
},
|
{
|
"user": "n1ql-000000000543"
|
},
|
{
|
"user": "n1ql-000000000544"
|
},
|
{
|
"user": "n1ql-000000000545"
|
},
|
{
|
"user": "n1ql-000000000546"
|
},
|
{
|
"user": "n1ql-000000000547"
|
},
|
{
|
"user": "n1ql-000000000548"
|
},
|
{
|
"user": "n1ql-000000000549"
|
},
|
{
|
"user": "n1ql-000000000550"
|
},
|
{
|
"user": "n1ql-000000000551"
|
},
|
{
|
"user": "n1ql-000000000552"
|
},
|
{
|
"user": "n1ql-000000000553"
|
},
|
{
|
"user": "n1ql-000000000554"
|
},
|
{
|
"user": "n1ql-000000000555"
|
},
|
{
|
"user": "n1ql-000000000556"
|
},
|
{
|
"user": "n1ql-000000000557"
|
},
|
{
|
"user": "n1ql-000000000558"
|
},
|
{
|
"user": "n1ql-000000000559"
|
},
|
{
|
"user": "n1ql-000000000560"
|
},
|
{
|
"user": "n1ql-000000000561"
|
},
|
{
|
"user": "n1ql-000000000562"
|
},
|
{
|
"user": "n1ql-000000000563"
|
},
|
{
|
"user": "n1ql-000000000564"
|
},
|
{
|
"user": "n1ql-000000000565"
|
},
|
{
|
"user": "n1ql-000000000566"
|
},
|
{
|
"user": "n1ql-000000000567"
|
},
|
{
|
"user": "n1ql-000000000568"
|
},
|
{
|
"user": "n1ql-000000000569"
|
},
|
{
|
"user": "n1ql-000000000570"
|
},
|
{
|
"user": "n1ql-000000000571"
|
},
|
{
|
"user": "n1ql-000000000572"
|
},
|
{
|
"user": "n1ql-000000000573"
|
},
|
{
|
"user": "n1ql-000000000574"
|
},
|
{
|
"user": "n1ql-000000000575"
|
},
|
{
|
"user": "n1ql-000000000576"
|
},
|
{
|
"user": "n1ql-000000000577"
|
},
|
{
|
"user": "n1ql-000000000578"
|
},
|
{
|
"user": "n1ql-000000000579"
|
},
|
{
|
"user": "n1ql-000000000580"
|
},
|
{
|
"user": "n1ql-000000000581"
|
},
|
{
|
"user": "n1ql-000000000582"
|
},
|
{
|
"user": "n1ql-000000000583"
|
},
|
{
|
"user": "n1ql-000000000584"
|
},
|
{
|
"user": "n1ql-000000000585"
|
},
|
{
|
"user": "n1ql-000000000586"
|
},
|
{
|
"user": "n1ql-000000000587"
|
},
|
{
|
"user": "n1ql-000000000588"
|
},
|
{
|
"user": "n1ql-000000000589"
|
},
|
{
|
"user": "n1ql-000000000590"
|
},
|
{
|
"user": "n1ql-000000000591"
|
},
|
{
|
"user": "n1ql-000000000592"
|
},
|
{
|
"user": "n1ql-000000000593"
|
},
|
{
|
"user": "n1ql-000000000594"
|
},
|
{
|
"user": "n1ql-000000000595"
|
},
|
{
|
"user": "n1ql-000000000596"
|
},
|
{
|
"user": "n1ql-000000000597"
|
},
|
{
|
"user": "n1ql-000000000598"
|
},
|
{
|
"user": "n1ql-000000000599"
|
},
|
{
|
"user": "n1ql-000000000600"
|
},
|
{
|
"user": "n1ql-000000000601"
|
},
|
{
|
"user": "n1ql-000000000602"
|
},
|
{
|
"user": "n1ql-000000000603"
|
},
|
{
|
"user": "n1ql-000000000604"
|
},
|
{
|
"user": "n1ql-000000000605"
|
},
|
{
|
"user": "n1ql-000000000606"
|
},
|
{
|
"user": "n1ql-000000000607"
|
},
|
{
|
"user": "n1ql-000000000608"
|
},
|
{
|
"user": "n1ql-000000000609"
|
},
|
{
|
"user": "n1ql-000000000610"
|
},
|
{
|
"user": "n1ql-000000000611"
|
},
|
{
|
"user": "n1ql-000000000612"
|
},
|
{
|
"user": "n1ql-000000000613"
|
},
|
{
|
"user": "n1ql-000000000614"
|
},
|
{
|
"user": "n1ql-000000000615"
|
},
|
{
|
"user": "n1ql-000000000616"
|
},
|
{
|
"user": "n1ql-000000000617"
|
},
|
{
|
"user": "n1ql-000000000618"
|
},
|
{
|
"user": "n1ql-000000000619"
|
},
|
{
|
"user": "n1ql-000000000620"
|
},
|
{
|
"user": "n1ql-000000000621"
|
},
|
{
|
"user": "n1ql-000000000622"
|
},
|
{
|
"user": "n1ql-000000000623"
|
},
|
{
|
"user": "n1ql-000000000624"
|
},
|
{
|
"user": "n1ql-000000000625"
|
},
|
{
|
"user": "n1ql-000000000626"
|
},
|
{
|
"user": "n1ql-000000000627"
|
},
|
{
|
"user": "n1ql-000000000628"
|
},
|
{
|
"user": "n1ql-000000000629"
|
},
|
{
|
"user": "n1ql-000000000630"
|
},
|
{
|
"user": "n1ql-000000000631"
|
},
|
{
|
"user": "n1ql-000000000632"
|
},
|
{
|
"user": "n1ql-000000000633"
|
},
|
{
|
"user": "n1ql-000000000634"
|
},
|
{
|
"user": "n1ql-000000000635"
|
},
|
{
|
"user": "n1ql-000000000636"
|
},
|
{
|
"user": "n1ql-000000000637"
|
}
|
],
|
"categories": [
|
"n1ql-000000000012",
|
"n1ql-000000000020",
|
"n1ql-000000000024",
|
"n1ql-000000000030"
|
],
|
"title": "d76d728332ccda0b751f4880210bf578"
|
}
|
{
|
"name": "n1ql-000000000001"
|
}
|
Index:
CREATE INDEX `by_capped_large` ON `bucket-2`(`capped_large`)
|
Query:
PREPARE complex_join AS SELECT u.email AS email, COUNT(t.title) FROM `bucket-2` t INNER JOIN `bucket-3` c ON KEYS t.categories LEFT OUTER JOIN `bucket-1` u ON KEYS t.owner WHERE t.capped_large = $1 AND c.name = $2 GROUP BY u.email;
|
Explain:
{
|
"requestID": "17ff93f2-ddb6-43a8-980f-0e5cb456c087",
|
"clientContextID": "9f2965ec-be54-4f71-b9c5-cf80ea478012",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan",
|
"index": "by_capped_large",
|
"index_id": "7c8c46128dfc2efd",
|
"keyspace": "bucket-2",
|
"namespace": "default",
|
"spans": [
|
{
|
"Range": {
|
"High": [
|
"\"n1ql_1000_0\""
|
],
|
"Inclusion": 3,
|
"Low": [
|
"\"n1ql_1000_0\""
|
]
|
}
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "t",
|
"keyspace": "bucket-2",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Join",
|
"as": "c",
|
"keyspace": "bucket-3",
|
"namespace": "default",
|
"on_keys": "(`t`.`categories`)"
|
},
|
{
|
"#operator": "Join",
|
"as": "u",
|
"keyspace": "bucket-1",
|
"namespace": "default",
|
"on_keys": "(`t`.`owner`)",
|
"outer": true
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(((`t`.`capped_large`) = \"n1ql_1000_0\") and ((`c`.`name`) = \"n1ql-000000000024\"))"
|
},
|
{
|
"#operator": "InitialGroup",
|
"aggregates": [
|
"count((`t`.`title`))"
|
],
|
"group_keys": [
|
"(`u`.`email`)"
|
]
|
}
|
]
|
}
|
},
|
{
|
"#operator": "IntermediateGroup",
|
"aggregates": [
|
"count((`t`.`title`))"
|
],
|
"group_keys": [
|
"(`u`.`email`)"
|
]
|
},
|
{
|
"#operator": "FinalGroup",
|
"aggregates": [
|
"count((`t`.`title`))"
|
],
|
"group_keys": [
|
"(`u`.`email`)"
|
]
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "(`u`.`email`)"
|
},
|
{
|
"expr": "count((`t`.`title`))"
|
}
|
]
|
},
|
{
|
"#operator": "FinalProject"
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT u.email, COUNT(t.title) FROM `bucket-2` t INNER JOIN `bucket-3` c ON KEYS t.categories LEFT OUTER JOIN `bucket-1` u ON KEYS t.owner WHERE t.capped_large = 'n1ql_1000_0' AND c.name = 'n1ql-000000000024' GROUP BY u.email;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "8.015034ms",
|
"executionTime": "7.904652ms",
|
"resultCount": 1,
|
"resultSize": 4657
|
}
|
}
|
Attachments
Issue Links
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
(13 mentioned in)