Slow FTS query on a 60m bucket

Full Text Search
1

Hi,
A match query on a 60 million docs bucket is very slow - around 7s-9s for the given query -
Couchbase community 6.5
3 nodes
64GB RAM nodes resrouces - Server RAM settings: data 15GB, index 6GB, search 7GB.
Search FTS index is 100% resident.
Bucket has 15% resident, total 7500GB quota and RAM used/quota is 13GB/22GB.
Query is:

SELECT META().id, search_score() AS score
FROM `somesource` AS t
WHERE SEARCH(t, {"query": {"match": "John Smith", "field": "externalDisplayName"}, "sort": ["-_score"]},
{"index": "my-index-name"}) order by score desc limit 100

FTS search definition:

{
  "type": "fulltext-index",
  "name": "display-name-asis-fts-index",
  "uuid": "2f98e7b38dffa478",
  "sourceType": "couchbase",
  "sourceName": "source",
  "sourceUUID": "eb070d143b86e18a54befb625dbbfbdc",
  "planParams": {
    "maxPartitionsPerPIndex": 171,
    "indexPartitions": 6
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "type"
    },
    "mapping": {
      "analysis": {},
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": false,
        "enabled": true,
        "properties": {
          "externalDisplayName": {
            "dynamic": false,
            "enabled": true,
            "fields": [
              {
                "analyzer": "standard",
                "docvalues": true,
                "index": true,
                "name": "externalDisplayName",
                "type": "text"
              }
            ]
          }
        }
      },
      "default_type": "_default",
      "docvalues_dynamic": true,
      "index_dynamic": true,
      "store_dynamic": false,
      "type_field": "_type"
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}
2

I believe the poor latency is because you are requesting N1QL to perform the sort and limit on all the results returned by the search index as opposed to deferring the sort and limit capability to the search index in which case only the qualifying results will be returned by the search index. Try this query …

SELECT META().id, search_score()
FROM `somesource` AS t
WHERE SEARCH(t,
{"query": {"match": "John Smith", "field": "externalDisplayName"}, "sort": ["-_score"], "size": 100},
{"index": "my-index-name"})
1 Like