Hi,
We are seeing irregular n1ql performance with the same query. Some of them are super fast, but some of them takes 600ms+. As some of them are super fast, I don’t think this is index problem. I would appreciate any suggestions.
Query:
select *from catalog bucket1 where bucket1.clientId = 'value' and bucket1.form = 'value' and bucket1.values.slug= 'value' ;
Explain:
{
"requestID": "0fc748f0-8fc9-41c8-b250-8a8f8b37d3c8",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "idx_creative_form",
"keyspace": "catalog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"creative\""
],
"Inclusion": 3,
"Low": [
"\"creative\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "idx_creative_slug",
"keyspace": "catalog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"\""
],
"Inclusion": 3,
"Low": [
"\"\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "idx_creative_clientId",
"keyspace": "catalog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"\""
],
"Inclusion": 3,
"Low": [
"\"\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "bucket1",
"keyspace": "catalog",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((((`bucket1`.`clientId`) = \"\") and ((`bucket1`.`form`) = \"creative\")) and (((`bucket1`.`values`).`slug`) = \"\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "9.455053ms",
"executionTime": "9.414738ms",
"resultCount": 1,
"resultSize": 4320
}
}
These are my test result in cbq.
cbq>
{
"requestID": "844276df-5d2f-44e4-96a7-97531f73236f",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "11.555695ms",
"executionTime": "11.505606ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq>
{
"requestID": "7effd6a7-3800-4d43-b76b-60300130d7c6",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "194.256269ms",
"executionTime": "194.173828ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq>
{
"requestID": "c8dc8596-ff0e-4321-807d-a75753948022",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "31.065162ms",
"executionTime": "31.010268ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq>
{
"requestID": "824c8da6-d175-4c84-aa0b-d2475af5966e",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "8.138917ms",
"executionTime": "8.100675ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq> hidden
{
"requestID": "02cf4cf5-fdb4-4fbc-a4e7-c193453f936e",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "8.808598ms",
"executionTime": "8.749748ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq> hidden
{
"requestID": "edfd6e3b-f84d-4a11-8278-942987f1898c",
"signature": {
"id": "json"
},
"status": "success",
"metrics": {
"elapsedTime": "401.523652ms",
"executionTime": "401.428373ms",
"resultCount": 1,
"resultSize": 57
}
}
cbq> hidden
{
"requestID": "f5b7373f-f13f-48c8-be62-44ea36b4028b",
"signature": {
"id": "json"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "617.373537ms",
"executionTime": "617.336218ms",
"resultCount": 1,
"resultSize": 57
}
}