FlexIndex: IntersectScan with GSI index is not required when sargable FTS index is available

Build: 7.0.0-1834

• Load dataset attached
• Create fts index with below:

  {

  "type": "fulltext-index",

  "name": "custom_index",

  "uuid": "55e3f1f1166f1a14",

  "sourceType": "gocbcore",

  "sourceName": "default",

  "sourceUUID": "172bb32f736de2f3db0347b634fe68c5",

  "planParams": {

  "maxPartitionsPerPIndex": 171

  },

  "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": true,

  "enabled": false

  },

  "default_type": "_default",

  "docvalues_dynamic": true,

  "index_dynamic": true,

  "store_dynamic": false,

  "type_field": "type",

  "types": {

  "emp": {

  "dynamic": false,

  "enabled": true,

  "properties": {

  "is_manager": {

  "dynamic": false,

  "enabled": true,

  "fields": [

  {

  "include_in_all": true,

  "include_term_vectors": true,

  "index": true,

  "name": "is_manager",

  "type": "boolean"

  }

  ]

  },

  "join_date": {

  "dynamic": false,

  "enabled": true,

  "fields": [

  {

  "include_in_all": true,

  "include_term_vectors": true,

  "index": true,

  "name": "join_date",

  "type": "datetime"

  }

  ]

  },

  "manages": {

  "dynamic": false,

  "enabled": true,

  "properties": {

  "team_size": {

  "dynamic": false,

  "enabled": true,

  "fields": [

  {

  "include_in_all": true,

  "include_term_vectors": true,

  "index": true,

  "name": "team_size",

  "type": "number"

  }

  ]

  }

  }

  },

  "mutated": {

  "dynamic": false,

  "enabled": true,

  "fields": [

  {

  "include_in_all": true,

  "include_term_vectors": true,

  "index": true,

  "name": "mutated",

  "type": "number"

  }

  ]

  },

  "name": {

  "dynamic": false,

  "enabled": true,

  "fields": [

  {

  "analyzer": "keyword",

  "include_in_all": true,

  "include_term_vectors": true,

  "index": true,

  "name": "name",

  "type": "text"

  }

  ]

  }

  }

  }

  }

  },

  "store": {

  "indexType": "scorch",

  "mossStoreOptions": {}

  }

  },

  "sourceParams": {}

  }

• Create GSI index with type : CREATE INDEX `gsi_index_4` ON `default`(`type`)
• For the below Flex query, we see that its doing intersect scan b/w gsi_index_4 and fts index, where it can just use fts index as the field name is indexed with type mapping emp

  select meta().id from default USE INDEX ( USING FTS, USING GSI) where type = "emp" and name between "Fuscienne" and "Lilith"

• Explain plan

  {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "IntersectScan",

  "scans": [

  {

  "#operator": "IndexScan3",

  "index": "gsi_index_4",

  "index_id": "7fcf588711fd93dd",

  "index_projection": {

  "primary_key": true

  },

  "keyspace": "default",

  "namespace": "default",

  "spans": [

  {

  "exact": true,

  "range": [

  {

  "high": "\"emp\"",

  "inclusion": 3,

  "low": "\"emp\""

  }

  ]

  }

  ],

  "using": "gsi"

  },

  {

  "#operator": "IndexFtsSearch",

  "index": "custom_index",

  "index_id": "55e3f1f1166f1a14",

  "keyspace": "default",

  "namespace": "default",

  "search_info": {

  "field": "\"\"",

  "options": "{\"index\": \"custom_index\"}",

  "outname": "out",

  "query": "{\"query\": null, \"score\": \"none\"}"

  },

  "using": "fts"

  }

  ]

  },

  {

  "#operator": "Fetch",

  "keyspace": "default",

  "namespace": "default"

  },

  {

  "#operator": "Parallel",

  "~child": {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "Filter",

  "condition": "(((`default`.`type`) = \"emp\") and ((`default`.`name`) between \"Fuscienne\" and \"Lilith\"))"

  },

  {

  "#operator": "InitialProject",

  "result_terms": [

  {

  "expr": "(meta(`default`).`id`)"

  }

  ]

  }

  ]

  }

  }

  ]

  }