Slow N1QL search on using OR condition

[Kannan_Muruganmony]

The below N1QL query result is slow as it not using the right indexes in OR conditions,(Body, Title, Standfirst in below query), but works in AND Condition.

SELECT count(1) as Total
from `Documents` as t1  
where   
  t1.Status in [0,1,2,3,4,5]
  and (any DeliveryTargetCode in t1.DeliveryTargetCodes satisfies DeliveryTargetCode in 	['web3_1'] end)
  and
  (
	  (any wrd in SPLIT(LOWER(t1.Body)) satisfies wrd = 'brexit' end) or
	  (any wrd in SPLIT(LOWER(t1.Title)) satisfies wrd = 'brexit' end) or
	  (any wrd in SPLIT(LOWER(t1.StandFirst)) satisfies wrd = 'brexit' end)
  )
  and t1.ContentType in [0,1,2]
  and t1.IsLatestVersion = true    
  and t1.Deleted = false

We have three indexes for Body, Title, StandFirst

CREATE INDEX `QA_idx_Dashboard_Body_Split` ON `Documents`((distinct (array `wrd` for `wrd` in split(lower(`Body`)) end))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

CREATE INDEX `QA_idx_Dashboard_Title_Split` ON `Documents`((distinct (array `wrd` for `wrd` in split(lower(`Title`)) end))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

CREATE INDEX `QA_idx_Dashboard_StandFirst_Split` ON `Documents`((distinct (array `wrd` for `wrd` in split(lower(`StandFirst`)) end))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

Any help is highly appreciated. Thank you.

[vsr1]

CREATE INDEX `ix1` ON `Documents`(status, DISTINCT SPLIT(LOWER( CONCAT(IFMISSINGORNULL(Body,""), " ", IFMISSINGORNULL(Title, "")," ", IFMISSINGORNULL(StandFirst," ")))) )
WHERE `ContentType` IN [0, 1, 2] AND `IsLatestVersion` = true AND `Deleted` = false;

SELECT COUNT(1) AS Total
FROM `Documents` AS t1
WHERE   t1.Status IN [0,1,2,3,4,5]
  AND (ANY DeliveryTargetCode IN t1.DeliveryTargetCodes SATISFIES DeliveryTargetCode IN ['web3_1'] END)
  AND (ANY wrd IN SPLIT(LOWER( CONCAT(IFMISSINGORNULL(t1.Body,""), " ", IFMISSINGORNULL(t1.Title, "")," ", IFMISSINGORNULL(t1.StandFirst," "))))
       SATISFIES wrd = 'brexit' END)
  AND t1.ContentType IN [0,1,2]
  AND t1.IsLatestVersion = true
  AND t1.Deleted = false;

OR

Only Equality predicates

CREATE INDEX `ix2` ON `Documents`(status, DISTINCT ARRAY (DISTINCT ARRAY [dtc, wrd] FOR wrd IN SPLIT(LOWER( CONCAT(IFMISSINGORNULL(Body,""), " ", IFMISSINGORNULL(Title, "")," ", IFMISSINGORNULL(StandFirst," ")))) END) FOR dtc IN DeliveryTargetCodes END)
WHERE `ContentType` IN [0, 1, 2] AND `IsLatestVersion` = true AND `Deleted` = false;

SELECT COUNT(1) AS Total
FROM `Documents` AS t1
WHERE t1.Status IN [0,1,2,3,4,5]
  AND (ANY dtc IN t1.DeliveryTargetCodes SATISFIES (ANY wrd IN SPLIT(LOWER( CONCAT(IFMISSINGORNULL(t1.Body,""), " ", IFMISSINGORNULL(t1.Title, "")," ", IFMISSINGORNULL(t1.StandFirst," ")))) SATISFIES [dtc,wrd] = ["web3_1", "brexit"] END) END)
  AND t1.ContentType IN [0,1,2]
  AND t1.IsLatestVersion = true
  AND t1.Deleted = false;

[Kannan_Muruganmony]

Thanks for ur reply @vsr1

Followed the first one and created a index (index definition below):

CREATE INDEX `ix1` ON `Documents`(`Status`,(distinct (split(lower((ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " "))))))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false)) 

Query used to test:

SELECT COUNT(1) AS Total
FROM `Documents` AS t1
WHERE t1.Status IN [0,1,2,3,4,5]
  AND (ANY wrd IN SPLIT(LOWER( CONCAT(IFMISSINGORNULL(t1.Body,''), ' ', IFMISSINGORNULL(t1.Title, ''),' ', IFMISSINGORNULL(t1.StandFirst,' '))))
       SATISFIES wrd = 'brexit' END)
  AND t1.ContentType IN [0,1,2]
  AND t1.IsLatestVersion = true
  AND t1.Deleted = false;

But the query still doesn’t use the created index (checked in EXPLAIN too). Not sure if I’m missing something. Any help will be great. Thank you.

[vsr1]

Try this (it works in 6.6). If doesn’t work post CB version

   SELECT COUNT(1) AS Total
    FROM `Documents` AS t1
    WHERE t1.Status IN [0,1,2,3,4,5]
      AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " ") ))
           SATISFIES wrd = 'brexit' END)
      AND t1.ContentType IN [0,1,2]
      AND t1.IsLatestVersion = true
      AND t1.Deleted = false;

[Kannan_Muruganmony]

Hi @vsr1 ,

Thanks for your help with the query to handle OR condition that greatly helped.

I’m a bit struggling with finding the optimized N1QL query to my below scenario.
I have a dashboard whose data is displayed based on a few search filters.

I have 2 queries to achieve this.

1. To get the data (has limit and offset)
2. To get the total count (to handle pagination)

In search filters, there are few mandatory, optional parameters that will be passed to the query.
My idea behind creating indexes: I have created a single index for mandatory params and separate indexes for each optional params.

Search Filters:

Mandatory (which will be always be passed in the query):

• Status (number)
• DeliveryTargetCodes (string array - match with item in an DeliveryTargetCodes string array))
• ContentType (number) - always [1,2,3]
• IsLatestVersion (boolean) - always true
• Deleted (boolean) - always false

Optional (the condition string will be only passed based on user filters):

• SearchText (string - OR condition between three text fields (Body, Title, StandFirst))
• AuthorId (string - match with an item in an Authors array)
• TopicId (number - match with an item in a Topics array)
• LastUpdatedOn (datetime)
• userFilter (string - OR condition between CreatedBy and StartedEditingByFilter)

1) Query to fetch Data

SELECT 
,Id
,Title
,Status
from `Documents`
where  Status in [1,2,3]
and (any wrd in DeliveryTargetCodes satisfies wrd in ['web3_1', 'web3_2'] end)

AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(`EditingStartedByUser`, "") || " " || ifmissingornull(`CreatedByUser`, "")))
SATISFIES wrd = 'user1' END)

AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " ") ))
   SATISFIES wrd = 'brexit' END)

and LastUpdatedOn >= '2000-02-28T08:52:41'

and (any item in Topics satisfies item.Id in [7] end)

and (any item in Authors satisfies item.Id in ['Author1'] end)

and ContentType in [0,1,2]
and IsLatestVersion = true    
and Deleted = false
order by LastUpdatedOn desc
limit 10
offset 1

2) Query to Data Count

SELECT count(1) as Total
from `Documents`  
where   
  Status in [1,2,3]
  and (any wrd in DeliveryTargetCodes satisfies wrd in ['web3_1', 'web3_2'] end)
  
  and (ANY wrd IN SPLIT(LOWER( ifmissingornull(`EditingStartedByUser`, "") || " " || ifmissingornull(`CreatedByUser`, "")))
	    SATISFIES wrd = 'user1' END)

  and (ANY wrd IN SPLIT(LOWER( ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " ") ))
	           SATISFIES wrd = 'brexit' END) 

  and LastUpdatedOn >= '2000-02-28T08:52:41'

  and (any item in Topics satisfies item.Id in [7] end)

  and (any item in Authors satisfies item.Id in ['Author1'] end)

  and ContentType in [0,1,2]
  and IsLatestVersion = true    
  and Deleted = false

Main Index (for mandatory):

CREATE INDEX `QA_idx_Dashboard_T_Count_1` ON `Documents`(`Status`,(distinct (array `DeliveryTargetCode` for `DeliveryTargetCode` in `DeliveryTargetCodes` end))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

Other Indexes (for optional params):

CREATE INDEX `QA_idx_Status_Title_Body_StandFirst` ON `Documents`(`Status`,(distinct (split(lower((ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " "))))))) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false)) 

CREATE INDEX `QA_idx_Author` ON `Documents`((distinct (array (`Author`.`Id`) for `Author` in `Authors` end)))

CREATE INDEX `QA_idx_Topic` ON `Documents`((distinct (array (`Topic`.`Id`) for `Topic` in `Topics` end)))

CREATE INDEX `QA_idx_LastUpdatedOnDesc` ON `Documents`(`LastUpdatedOn` DESC) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

CREATE INDEX `QA_idx_EditingStartedBy_CreatedByUser` ON `Documents`((distinct (split(lower(((ifmissingornull(`EditingStartedByUser`, "") || " ") || ifmissingornull(`CreatedByUser`, ""))))))) 

The query works fast with mandatory params.
But with few combinations of optional params (like +SearchText alone), the query is slow.
Could you please help me in writing the right index for this scenario. Kindly help.

Sample Doc:

{
  "Authors": [
    {
      "Id": "Author_1",
      "Name": "Author Name"
    }
  ],
  "Topics": [
    {
      "Id": 1,
      "Name": "Topic 1"
    }
  ],
  "DeliveryTargetCodes": [
    "web3_1",
    "web3_2"
  ],
  "Body": "<p>Test Body</p>",
  "StandFirst": "Stand first text",
  "Title": "Test article",
  "ContentType": 0,
  "CreatedByUser": "user1",
  "EditingStartedByUser": null,
  "Id": "Id_1",
  "IsLatestVersion": true,
  "Deleted": false,  
  "LastUpdatedOn": "2017-01-27T04:09:29.18Z",
  "Status": 1
}

[vsr1]

I would recommend use mandatory to index for all the queries and see . it should work.

Also check this out

Announcing Flex Index with Couchbase | The Couchbase Blog

FTS Flex Index vs GSI Indexes in Couchbase Server 6.6 | The Couchbase Blog

[Kannan_Muruganmony]

Hi @vsr1, I tried FTS index earlier, everything worked but unfortunately, our system configuration needed some change, update to use FTS efficiently, so we had to go with N1QL for now.

“I would recommend use mandatory to index for all the queries and see .” , Could you please show me an example. I have been trying different combinations of the index to make it work. but no luck. using Couchbase 6.0.3 .Any help will be great, Thank you.

[vsr1]

CREATE INDEX ix30 ON Documents(Status, DISTINCT DeliveryTargetCodes, LastUpdatedOn) WHERE (((ContentType in [0, 1, 2]) and (IsLatestVersion = true)) and (Deleted = false));

I would recommend above index as mandatory index. Then what ever query you have try with USE INDEX
Those may take time approximately same time as mandatory fields as filters.

CREATE INDEX `ix30` ON `Documents`(`Status`, DISTINCT `DeliveryTargetCodes`, LastUpdatedOn) WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false));

SELECT 
,Id
,Title
,Status
from `Documents` USE INDEX (ix30)
where  Status in [1,2,3]
and (any wrd in DeliveryTargetCodes satisfies wrd in ['web3_1', 'web3_2'] end)

AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(`EditingStartedByUser`, "") || " " || ifmissingornull(`CreatedByUser`, "")))
SATISFIES wrd = 'user1' END)

AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(`Body`, "") || " " || ifmissingornull(`Title`, "") || " " || ifmissingornull(`StandFirst`, " ") ))
   SATISFIES wrd = 'brexit' END)

and LastUpdatedOn >= '2000-02-28T08:52:41'

and (any item in Topics satisfies item.Id in [7] end)

and (any item in Authors satisfies item.Id in ['Author1'] end)

and ContentType in [0,1,2]
and IsLatestVersion = true    
and Deleted = false
order by LastUpdatedOn desc
limit 10
offset 1

[Kannan_Muruganmony]

Thanks a lot, @vsr1, could see the performance improved.
All the other condition except the search on text are very fast.

When the search condition on Body,Title, StandFirst is added, the response is slow, it takes 10-12 secs. Is there any way to optimize? and also with some words the response is better and with some words is worse?

Better:
AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(Body, “”) || " " || ifmissingornull(Title, “”) || " " || ifmissingornull(StandFirst, " ") ))
SATISFIES wrd in [ ‘articles’] END)

Worse:
AND (ANY wrd IN SPLIT(LOWER( ifmissingornull(Body, “”) || " " || ifmissingornull(Title, “”) || " " || ifmissingornull(StandFirst, " ") ))
SATISFIES wrd in [ ‘global’] END)

[vsr1]

That may not related to indexscan. Most probably due to Filter. Check executionTime of each operator (UI Plan Text Tab) and see which one taking time.
ANY clause terminates when it encounter first true condition or till the end.
i.e. SPLIT(LOWER(txt)) generates 1000 words, if first word match loop executed once. if non match it executes 1000 times.

[Kannan_Muruganmony]

Yes, You are right mate, the filter is taking a long time. Any suggestions to fix it? Thanks for helping out.

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "servTime": "143.0067ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:Documents",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1
        },
        "~children": [
          {
            "#operator": "IntersectScan",
            "#stats": {
              "#itemsIn": 6968,
              "#itemsOut": 6358,
              "#phaseSwitches": 26659,
              "execTime": "21ms",
              "kernTime": "16.1411366s"
            },
            "scans": [
              {
                "#operator": "DistinctScan",
                "#stats": {
                  "#itemsIn": 610,
                  "#itemsOut": 610,
                  "#phaseSwitches": 2447,
                  "kernTime": "151.0089ms"
                },
                "scan": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 610,
                    "#phaseSwitches": 2446,
                    "execTime": "1.9981ms",
                    "kernTime": "1.0011ms",
                    "servTime": "148.0097ms"
                  },
                  "index": "QA_idx_Mandatory_L",
                  "index_id": "3e874286627ccab1",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "Documents",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "0",
                          "inclusion": 3,
                          "low": "0"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "2",
                          "inclusion": 3,
                          "low": "2"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "3",
                          "inclusion": 3,
                          "low": "3"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "4",
                          "inclusion": 3,
                          "low": "4"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "5",
                          "inclusion": 3,
                          "low": "5"
                        },
                        {
                          "high": "\"web3__1\"",
                          "inclusion": 3,
                          "low": "\"web3__1\""
                        },
                        {
                          "inclusion": 1,
                          "low": "\"2000-01-01T00:00:00\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi",
                  "#time_normal": "00:00.1500",
                  "#time_absolute": 0.15000780000000002
                }
              },
              {
                "#operator": "DistinctScan",
                "#stats": {
                  "#itemsIn": 6358,
                  "#itemsOut": 6358,
                  "#phaseSwitches": 25439,
                  "execTime": "2.9975ms",
                  "kernTime": "148.0114ms"
                },
                "scan": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 6358,
                    "#phaseSwitches": 25437,
                    "execTime": "5.995ms",
                    "kernTime": "11.0032ms",
                    "servTime": "134.0107ms"
                  },
                  "index": "QA_idx_Status_Title_Body_StandFirst_Count",
                  "index_id": "1d0fa20b03de3dc6",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "Documents",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"article\"",
                          "inclusion": 3,
                          "low": "\"article\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi",
                  "#time_normal": "00:00.1400",
                  "#time_absolute": 0.1400057
                },
                "#time_normal": "00:00.0029",
                "#time_absolute": 0.0029975
              }
            ],
            "#time_normal": "21:00",
            "#time_absolute": 1260
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 6358,
              "#itemsOut": 6358,
              "#phaseSwitches": 26231,
              "execTime": "83.0049ms",
              "kernTime": "779.1156ms",
              "servTime": "16.2950853s"
            },
            "keyspace": "Documents",
            "namespace": "default",
            "#time_normal": "00:16.3780",
            "#time_absolute": 16.3780902
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1
            },
            "~children": [
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 6358,
                  "#itemsOut": 65,
                  "#phaseSwitches": 12849,
                  "execTime": "15.4049451s",
                  "kernTime": "1.7742639s"
                },
                "condition": "((((((((`Documents`.`Status`) in [0, 1, 2, 3, 4, 5]) and any `wrd` in (`Documents`.`DeliveryTargetCodes`) satisfies (`wrd` in [\"web3__1\"]) end) and (\"2000-01-01T00:00:00\" <= (`Documents`.`LastUpdatedOn`))) and any `wrd` in split(lower(((((ifmissingornull((`Documents`.`Body`), \"\") || \" \") || ifmissingornull((`Documents`.`Title`), \"\")) || \" \") || ifmissingornull((`Documents`.`StandFirst`), \" \")))) satisfies (`wrd` in [\"article\"]) end) and ((`Documents`.`ContentType`) in [0, 1, 2])) and ((`Documents`.`IsLatestVersion`) = true)) and ((`Documents`.`Deleted`) = false))",
                "#time_normal": "00:15.4049",
                #time_absolute": 15.4049451
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 65,
                  "#itemsOut": 1,
                  "#phaseSwitches": 135,
                  "kernTime": "17.179209s"
                },
                "aggregates": [
                  "count(1)"
                ],
                "group_keys": []
              }
            ]
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "kernTime": "17.179209s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": []
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "kernTime": "17.179209s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": []
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 8,
                  "kernTime": "17.179209s"
                },
                "result_terms": [
                  {
                    "as": "Total",
                    "expr": "count(1)"
                  }
                ]
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 4
                }
              }
            ]
          }
        ]
      },
      "#time_normal": "00:00.1430",
      "#time_absolute": 0.1430067
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1,
        "#itemsOut": 1,
        "#phaseSwitches": 7,
        "kernTime": "17.3222157s"
      }
    }
  ],
}

[vsr1]

There is no other way.

Above query uses IntersectScan avoid it by dropping QA_idx_Status_Title_Body_StandFirst_Count which produces 6358 documents or
USE INDEX (QA_idx_Mandatory_L) which produces 610 documents.

Most cases avoid intersectscans by dropping unnecessary index (this may not possible all the time), In that case provide USE INDEX hint with right index.

As I look your EXPLAIN you are not using the SPLIT on index key try this. Now you need to worry how to do multiple words search, word boundary. May be CONTAINS_TOKEN() might be right one. (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/tokenfun.html)

(CONTAINS(LOWER(Body), “articles”) OR CONTAINS(LOWER(Title), “articles”) OR CONTAINS(LOWER(StandFirst), “articles”))

[Kannan_Muruganmony]

Split On Index Key: TIs this the partition? So Does my mandatory index have to change to this?

CREATE INDEX `QA_idx_Mandatory_L` ON `Documents`(`Status`,(distinct (`DeliveryTargetCodes`)),`LastUpdatedOn`)   PARTITION BY HASH(META().id)
WHERE (((`ContentType` in [0, 1, 2]) and (`IsLatestVersion` = true)) and (`Deleted` = false))

[vsr1]

What I am talking is SPLIT(LOWER( ifmissingornull( Body , “”) || " " || ifmissingornull( Title , “”) || " " || ifmissingornull( StandFirst , " ") ))

First avoid intersectScan the query will perform better.

FROM Documents USE INDEX ( QA_idx_Mandatory_L)

[Kannan_Muruganmony]

Updated the query to test search functionality:

SELECT Count(1) AS Total
FROM Documents USE INDEX(QA_idx_Mandatory_L)
WHERE
Status IN [0,1,2,3,4,5]
and (any wrd in DeliveryTargetCodes satisfies wrd in [‘web3__1’] end)
and LastUpdatedOn >= ‘2000-01-01T00:00:00’
and (CONTAINS(LOWER(Body), ‘articles’) OR CONTAINS(LOWER(Title), ‘articles’) OR CONTAINS(LOWER(StandFirst), ‘articles’))
AND ContentType IN [0,1,2]
AND IsLatestVersion = true
AND Deleted = false;

This takes around 30secs. Fetch takes 23secs.

UI plan

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "servTime": "5.0092ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:Documents",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1
        },
        "~children": [
          {
            "#operator": "DistinctScan",
            "#stats": {
              "#itemsIn": 10982,
              "#itemsOut": 10982,
              "#phaseSwitches": 43933,
              "execTime": "56.2874ms",
              "kernTime": "22.7876724s"
            },
            "scan": {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 10982,
                "#phaseSwitches": 43933,
                "execTime": "70.1959ms",
                "kernTime": "21.155913s",
                "servTime": "52.7087ms"
              },
              "index": "QA_idx_Mandatory_L",
              "index_id": "3e874286627ccab1",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "Documents",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "0",
                      "inclusion": 3,
                      "low": "0"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "1",
                      "inclusion": 3,
                      "low": "1"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "2",
                      "inclusion": 3,
                      "low": "2"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "3",
                      "inclusion": 3,
                      "low": "3"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "4",
                      "inclusion": 3,
                      "low": "4"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                },
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "5",
                      "inclusion": 3,
                      "low": "5"
                    },
                    {
                      "high": "\"web3__1\"",
                      "inclusion": 3,
                      "low": "\"web3__1\""
                    },
                    {
                      "inclusion": 1,
                      "low": "\"2000-01-01T00:00:00\""
                    }
                  ]
                }
              ],
              "using": "gsi",
              "#time_normal": "00:00.1229",
              "#time_absolute": 0.12290459999999999
            },
            "#time_normal": "00:00.0562",
            "#time_absolute": 0.0562874
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 10982,
              "#itemsOut": 10982,
              "#phaseSwitches": 45305,
              "execTime": "96.1414ms",
              "kernTime": "70.0122ms",
              "servTime": "23.9189947s"
            },
            "keyspace": "Documents",
            "namespace": "default",
            "#time_normal": "00:24.0151",
            "#time_absolute": 24.0151361
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1
            },
            "~children": [
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 10982,
                  "#itemsOut": 53,
                  "#phaseSwitches": 22073,
                  "execTime": "6.5913566s",
                  "kernTime": "17.4968199s"
                },
                "condition": "((((((((`Documents`.`Status`) in [0, 1, 2, 3, 4, 5]) and any `wrd` in (`Documents`.`DeliveryTargetCodes`) satisfies (`wrd` in [\"web3__1\"]) end) and (\"2000-01-01T00:00:00\" <= (`Documents`.`LastUpdatedOn`))) and ((contains(lower((`Documents`.`Body`)), \"articles\") or contains(lower((`Documents`.`Title`)), \"articles\")) or contains(lower((`Documents`.`StandFirst`)), \"articles\"))) and ((`Documents`.`ContentType`) in [0, 1, 2])) and ((`Documents`.`IsLatestVersion`) = true)) and ((`Documents`.`Deleted`) = false))",
                "#time_normal": "00:06.5913",
                "#time_absolute": 6.5913566
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 53,
                  "#itemsOut": 1,
                  "#phaseSwitches": 111,
                  "execTime": "1.0056ms",
                  "kernTime": "24.0871709s"
                },
                "aggregates": [
                  "count(1)"
                ],
                "group_keys": [],
                "#time_normal": "00:00.0010",
                "#time_absolute": 0.0010056000000000002
              }
            ]
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "kernTime": "24.0881765s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": []
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "kernTime": "24.0890662s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": []
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 8,
                  "kernTime": "24.0890662s"
                },
                "result_terms": [
                  {
                    "as": "Total",
                    "expr": "count(1)"
                  }
                ]
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 4
                }
              }
            ]
          }
        ]
      },
      "#time_normal": "00:00.0050",
      "#time_absolute": 0.0050092
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1,
        "#itemsOut": 1,
        "#phaseSwitches": 7,
        "kernTime": "24.0940754s"
      }
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.3-2895-enterprise"
  ]
}

[vsr1]

@Kannan_Muruganmony

 "scan": {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 10982,
                "#phaseSwitches": 43933,
                "execTime": "70.1959ms",
                "kernTime": "21.155913s",
                "servTime": "52.7087ms"
              }

May be system overloaded. KernTime On IndexScan very high?
Try without partition index and see any better.

[Kannan_Muruganmony]

yes you are right @vsr1 System is overloaded and now tried in a different server which has less load, seems to respond fast. Thank you very very much mate Really appreciate it.