N1QL + FTS slow

SQL++
1

I am trying to use N1QL and FTS in the same query and for an unknow reason the FTS index is not used

This is my query:

SELECT meta().id
FROM `images_fts`as t1
 WHERE SEARCH(t1,`{"query": {"match_phrase": "apple", "field":"pp"}, "score": "none"}, {"index": "image_fts-v2"})

if I use the same expression: {"query": {"match_phrase": "apple", "field":"pp"}, "score": "none"} in the fts I am getting instant results, but with the N1QL it just doesn’t use the index

here is the query plan:


{
    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "PrimaryScan3",
                        "as": "t1",
                        "index": "#primary",
                        "index_projection": {
                            "primary_key": true
                        },
                        "keyspace": "images_fts",
                        "namespace": "default",
                        "using": "gsi"
                    },
                    {
                        "#operator": "Fetch",
                        "as": "t1",
                        "keyspace": "images_fts",
                        "namespace": "default"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "search(`t1`, {\"query\": {\"field\": \"pp\", \"match_phrase\": \"apple\"}, \"score\": \"none\"}, {\"index\": \"image_fts-v2\"})"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "(meta(`t1`).`id`)"
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                ]
            },
            {
                "#operator": "Limit",
                "expr": "10"
            }
        ]
    }
}

@vsr1 can you please hint me what I am doing wrong?

2

mae sure fts index analyzer is keyword. cc @abhinav

3

@vsr1 The keyword analyzer is not necessary here.

@flaviu We’ll want to look at your search index definition to help with these issues. It’s possible you need to specify the type expression in N1QL - can confirm only with the definition.

4

Sorry @abhinav , I have missed your answer. Here is the index definition:

{
  "type": "fulltext-index",
  "name": "image_fts-v2",
  "uuid": "4e8a00692c191b1f",
  "sourceType": "gocbcore",
  "sourceName": "images_fts",
  "sourceUUID": "446eb8f81d4c800ccad037596d85a254",
  "planParams": {
    "maxPartitionsPerPIndex": 256,
    "indexPartitions": 4
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "m.t"
    },
    "mapping": {
      "analysis": {},
      "default_analyzer": "en",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "default_analyzer": "en",
        "dynamic": false,
        "enabled": false
      },
      "default_type": "_default",
      "docvalues_dynamic": false,
      "index_dynamic": false,
      "store_dynamic": false,
      "type_field": "_type",
      "types": {
        "fts": {
          "dynamic": false,
          "enabled": true,
          "properties": {
            "cd": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "cd",
                  "type": "number"
                }
              ]
            },
            "dl": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "dl",
                  "type": "number"
                }
              ]
            },
            "ih": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "ih",
                  "type": "number"
                }
              ]
            },
            "ii": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "keyword",
                  "index": true,
                  "name": "ii",
                  "type": "text"
                }
              ]
            },
            "im": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "keyword",
                  "index": true,
                  "name": "im",
                  "type": "text"
                }
              ]
            },
            "iw": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "iw",
                  "type": "number"
                }
              ]
            },
            "mi": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "mi",
                  "type": "number"
                }
              ]
            },
            "mt": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "mt",
                  "store": true,
                  "type": "number"
                }
              ]
            },
            "np": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "en",
                  "include_term_vectors": true,
                  "index": true,
                  "name": "np",
                  "type": "text"
                }
              ]
            },
            "nv": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "en",
                  "index": true,
                  "name": "nv",
                  "type": "number"
                }
              ]
            },
            "pi": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "pi",
                  "store": true,
                  "type": "boolean"
                }
              ]
            },
            "pp": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "en",
                  "include_term_vectors": true,
                  "index": true,
                  "name": "pp",
                  "type": "text"
                }
              ]
            },
            "pv": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "en",
                  "index": true,
                  "name": "pv",
                  "type": "number"
                }
              ]
            },
            "s": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "s",
                  "type": "number"
                }
              ]
            },
            "st": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "index": true,
                  "name": "st",
                  "type": "boolean"
                }
              ]
            }
          }
        }
      }
    },
    "store": {
      "indexType": "scorch",
      "segmentVersion": 15
    }
  },
  "sourceParams": {}
}
5

Ok, then your query needs changing to this to access the FTS index …

SELECT meta().id 
FROM `images_fts` as t1
WHERE `m.t` = "fts"
AND SEARCH(t1, {"query": {"match_phrase": "apple", "field":"pp"}, "score": "none"});
2 Likes
6

Thanks, It is working

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.