Thanks for the reply @vsr1,
I have tried with the given query and suggested index , but still the performance is very slow here, it is taking 33 sec to return the result. I am attaching the plan text tab output here for your reference.
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.427µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "14.935µs",
"servTime": "17.377463ms"
},
"privileges": {
"List": [
{
"Target": "default:bucket2",
"Priv": 7
},
{
"Target": "default:bucket1",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "11.359µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "11.9µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 31135,
"#phaseSwitches": 124543,
"execTime": "76.378988ms",
"kernTime": "32.269867546s",
"servTime": "15.676527ms"
},
"as": "cms",
"index": "adv_deleted_page_by_METAid",
"index_id": "a3563eac9e8d676",
"index_projection": {
"primary_key": true
},
"keyspace": "bucket2",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"low": "null"
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.092",
"#time_absolute": 0.092055515
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 31135,
"#itemsOut": 31135,
"#phaseSwitches": 128435,
"execTime": "218.756632ms",
"kernTime": "27.00366432s",
"servTime": "5.582049524s"
},
"as": "cms",
"keyspace": "bucket2",
"namespace": "default",
"#time_normal": "00:05.800",
"#time_absolute": 5.800806156
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.053µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 31135,
"#itemsOut": 6344,
"#phaseSwitches": 212189,
"execTime": "238.58238ms",
"kernTime": "33.032827543s"
},
"alias": "history",
"on_clause": "((meta(`cms`).`id`) = to_string((`history`.`instanceRef`)))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 62270,
"execTime": "451.180744ms",
"kernTime": "38.649582ms",
"state": "running"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 6344,
"#phaseSwitches": 118781,
"execTime": "211.606272ms",
"kernTime": "6.198151ms",
"servTime": "29.105165891s"
},
"as": "history",
"index": "adv_instanceRef_type_test",
"index_id": "6d55256bc3efb827",
"index_projection": {
"primary_key": true
},
"keyspace": "bucket1",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(meta(`cms`).`id`)",
"inclusion": 3,
"low": "(meta(`cms`).`id`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 6344,
"#itemsOut": 6344,
"#phaseSwitches": 123439,
"execTime": "206.094303ms",
"kernTime": "29.465355864s",
"servTime": "3.135639315s"
},
"as": "history",
"keyspace": "bucket1",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.238",
"#time_absolute": 0.23858238
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 6344,
"#itemsOut": 27,
"#phaseSwitches": 12745,
"execTime": "2.697790724s",
"kernTime": "30.573700111s"
},
"condition": "(((`history`.`type`) = \"history\") and search(`cms`, {\"should\": {\"disjuncts\": [{\"disjuncts\": [{\"boost\": 1.4, \"field\": \"name\", \"match\": \"pool\"}, {\"boost\": 1.4, \"field\": \"name\", \"fuzziness\": 1, \"match\": \"pool\"}]}, {\"disjuncts\": [{\"field\": \"title\", \"match\": \"pool\"}, {\"field\": \"title\", \"fuzziness\": 1, \"match\": \"pool\"}]}, {\"disjuncts\": [{\"field\": \"pageName\", \"match\": \"pool\"}, {\"field\": \"pageName\", \"fuzziness\": 1, \"match\": \"pool\"}]}, {\"disjuncts\": [{\"field\": \"ticketNumber\", \"match\": \"pool\"}, {\"field\": \"ticketNumber\", \"fuzziness\": 1, \"match\": \"pool\"}]}]}}))",
"#time_normal": "00:02.697",
"#time_absolute": 2.697790724
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 27,
"#itemsOut": 27,
"#phaseSwitches": 111,
"execTime": "916.61µs",
"kernTime": "33.270590214s"
},
"result_terms": [
{
"expr": "`history`",
"star": true
},
{
"expr": "(`cms`.`pageId`)"
},
{
"expr": "(`cms`.`ticketNumber`)"
},
{
"expr": "(`cms`.`effectiveTime`)"
},
{
"expr": "(`cms`.`expiryTime`)"
},
{
"expr": "(`cms`.`title`)"
},
{
"expr": "(`cms`.`pageType`)"
},
{
"expr": "(`cms`.`type`)"
},
{
"expr": "(`cms`.`name`)"
},
{
"expr": "(`cms`.`pageGroup`)"
},
{
"expr": "(`cms`.`supersededTime`)"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.00091661
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000003053
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000119
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 27,
"#itemsOut": 20,
"#phaseSwitches": 79,
"execTime": "242.346µs",
"kernTime": "33.271393468s"
},
"limit": "20",
"sort_terms": [
{
"desc": true,
"expr": "(`history`.`timestamp`)"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000242346
},
{
"#operator": "Limit",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 41,
"execTime": "19.017µs"
},
"expr": "20",
"#time_normal": "00:00.000",
"#time_absolute": 0.000019017
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 61,
"execTime": "37.086µs",
"kernTime": "896.644µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000037086
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000011359
},
"#time_normal": "00:00.017",
"#time_absolute": 0.017392398
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 43,
"execTime": "875.848µs",
"kernTime": "33.289221194s"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.0008758479999999999
}
],
"~versions": [
"6.5.0-N1QL",
"6.5.1-6299-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000024270000000000002
}
When I have added a condition to force to use the additional index which I have created earlier
CREATE INDEX adv_type ON `ecom-tesla-cms`(`type`)
I can see the response time slightly decreases to 25 sec, but still it is slow performance.
Can you suggest better approach here..or any alternative way I can achieve this so that result will return in less time.
Also, I have tried to create FTS index and executing below query,
SELECT history.*, cms.pageId, cms.ticketNumber, cms.effectiveTime,
cms.expiryTime, cms.title, cms.pageType, cms.type, cms.name, cms.pageGroup,
cms.supersededTime
FROM `bucket2` AS cms
JOIN `bucket1` AS history
ON META(cms).id = TOSTRING(history.instanceRef)
WHERE SEARCH(cms,
{
"query": {
"disjuncts": [
{ "disjuncts": [
{"field":"name", "match": "pool", "boost": 1.4},
{"field":"name", "match": "pool", "fuzziness":1, "boost": 1.4}
]
},
{ "disjuncts": [
{"field":"title", "match": "pool"},
{ "field":"title", "match": "pool", "fuzziness":1}
]
},
{ "disjuncts": [
{"field":"pageName", "match": "pool"},
{ "field":"pageName", "match": "pool", "fuzziness":1}
]
},
{ "disjuncts": [
{"field":"ticketNumber", "match": "pool"},
{ "field":"ticketNumber", "match": "pool", "fuzziness":1}
]
}
]
},
"score": "none"
}, { "index": "fts_test" })
AND history.type='history'
The index fts_test definition is as below:
{
"type": "fulltext-index",
"name": "fts_test",
"uuid": "3828d3e70b2091cb",
"sourceType": "couchbase",
"sourceName": "bucket2",
"sourceUUID": "341281c3e28240f8e4ea76e8aeaa952a",
"planParams": {
"maxPartitionsPerPIndex": 171,
"indexPartitions": 6
},
"params": {
"doc_config": {
"docid_prefix_delim": "",
"docid_regexp": "",
"mode": "type_field",
"type_field": "type"
},
"mapping": {
"analysis": {
"analyzers": {
"fts_test_analyzer": {
"char_filters": [
"html",
"zero_width_spaces",
"asciifolding"
],
"token_filters": [
"apostrophe",
"camelCase",
"to_lower",
"stop_en"
],
"tokenizer": "unicode",
"type": "custom"
}
}
},
"default_analyzer": "standard",
"default_datetime_parser": "dateTimeOptional",
"default_field": "_all",
"default_mapping": {
"dynamic": true,
"enabled": false
},
"default_type": "_default",
"docvalues_dynamic": true,
"index_dynamic": true,
"store_dynamic": false,
"type_field": "_type",
"types": {
"ContentFragment": {
"dynamic": true,
"enabled": true
},
"deleted_ContentFragment": {
"dynamic": true,
"enabled": true
},
"deleted_pagedef": {
"dynamic": true,
"enabled": true
},
"fragment": {
"dynamic": true,
"enabled": true
},
"page": {
"dynamic": true,
"enabled": true
},
"pagedef": {
"dynamic": true,
"enabled": true
}
}
},
"store": {
"indexType": "scorch"
}
},
"sourceParams": {}
}
but I am getting error as
{
"code": 5010,
"msg": "Error evaluating filter. - cause: n1fty: index mapping not found"
}
Not sure what I am missing here, I am new to the couchbase here, any help is greatly appreciated. Thanks in advance.