Hi!
I have a sample quyry like
SELECT id,
data.en.name
FROM `cms-data` col_doc
WHERE col_doc._scope="data"
AND col_doc._type="game"
AND col_doc.status="active"
AND col_doc._env=""
CREATE INDEX adv_type_status_scope_env ON cms-data(_type,_scope,status,_env)
elapsed: 882.8ms execution: 882.7ms docs: 5818 size: 555481 bytes
Why is the elapsed time so long (882.8ms) ? How can I reduce it?
Your query requires a fetch; if you can afford the space a covering index:
CREATE INDEX adv_type_status_env_scope_id_data_en_name ON `cms-data`(`_type`,`status`,`_env`,`_scope`,`id`,`data`.`en`.`name`)
should improve the performance as the second step of full document retrieval will not be necessary.
You could also set profile to timings and examine the output to confirm where in your query time is being spent. (Ref: Monitor Queries | Couchbase Docs )
HTH.
profile is
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.009873726s”,
“executionTime”: “1.009738953s”,
“resultCount”: 5818,
“resultSize”: 555481
},
“profile”: {
“phaseTimes”: {
“authorize”: “1.270704ms”,
“fetch”: “993.244003ms”,
“filter”: “824.3364ms”,
“indexScan”: “19.027485ms”,
“instantiate”: “45.614µs”,
“parse”: “975.914µs”,
“plan”: “1.341836ms”,
“run”: “1.007319234s”
},
“phaseCounts”: {
“fetch”: 5828,
“filter”: 5818,
“indexScan”: 5818
},
“phaseOperators”: {
“authorize”: 1,
“fetch”: 1,
“filter”: 1,
“indexScan”: 1
}
}
Time:
{
"metrics": {
"elapsedTime": "822.054832ms",
"executionTime": "821.916183ms",
"resultCount": 5818,
"resultSize": 555481
},
"profile": {
"phaseTimes": {
"authorize": "4.374256ms",
"fetch": "793.706006ms",
"filter": "768.777062ms",
"indexScan": "25.116412ms",
"instantiate": "35.918µs",
"parse": "1.645071ms",
"plan": "800.654µs",
"run": "819.395938ms"
},
"phaseCounts": {
"fetch": 5831,
"filter": 5818,
"indexScan": 5818
},
"phaseOperators": {
"authorize": 1,
"fetch": 1,
"filter": 1,
"indexScan": 1
},
"executionTimings": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.825µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "40.086µs",
"servTime": "4.33417ms"
},
"privileges": {
"List": [
{
"Target": "default:cms-data",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "10.881µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 5818,
"#phaseSwitches": 23275,
"execTime": "15.684213ms",
"kernTime": "716.775815ms",
"servTime": "9.432199ms"
},
"as": "col_doc",
"index": "adv_type_status_scope_env",
"index_id": "fcbb464442e57bc3",
"index_projection": {
"primary_key": true
},
"keyspace": "cms-data",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"game\"",
"inclusion": 3,
"low": "\"game\""
},
{
"high": "\"data\"",
"inclusion": 3,
"low": "\"data\""
},
{
"high": "\"active\"",
"inclusion": 3,
"low": "\"active\""
},
{
"high": "\"\"",
"inclusion": 3,
"low": "\"\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 5818,
"#itemsOut": 5818,
"#phaseSwitches": 24003,
"execTime": "20.013161ms",
"kernTime": "10.729867ms",
"servTime": "773.692845ms"
},
"as": "col_doc",
"keyspace": "cms-data",
"namespace": "default"
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "4.167µs"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 5818,
"#itemsOut": 5818,
"#phaseSwitches": 23275,
"execTime": "768.777062ms",
"kernTime": "46.006348ms"
},
"condition": "(((((`col_doc`.`_scope`) = \"data\") and ((`col_doc`.`_type`) = \"game\")) and ((`col_doc`.`status`) = \"active\")) and ((`col_doc`.`_env`) = \"\"))"
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 5818,
"#itemsOut": 5818,
"#phaseSwitches": 17459,
"execTime": "638.70416ms",
"kernTime": "129.446133ms"
},
"result_terms": [
{
"expr": "(`col_doc`.`id`)"
},
{
"expr": "(((`col_doc`.`data`).`en`).`name`)"
}
]
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 5818,
"#itemsOut": 5818,
"#phaseSwitches": 17455,
"execTime": "5.548703ms",
"kernTime": "39.319114ms"
}
}
]
}
]
}
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 5818,
"#itemsOut": 5818,
"#phaseSwitches": 11639,
"execTime": "66.261128ms",
"kernTime": "753.091932ms"
}
}
]
}
}
}
Thanks. It does look like the only practical way to improve this will be with the covering index so as to avoid the FETCH and FILTER (filter must be re-asserted after document fetch owing to eventual consistency model).