CB 6.6 Flex/N1QL

Full Text Search
1

I am looking at using Flex FTS and doing some testing.

Query #1.

 SELECT  meta(t).id FROM s t USE INDEX (USING FTS)
 WHERE t.type='task' and t.lid = 'blah'
 AND t.atid  = "abc"

Here is the plan

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexFtsSearch”,
“as”: “t”,
“covers”: [
“cover (((((t.type) = "task") and ((t.lid) = "blah")) and ((t.atid) = "abc")))”,
“cover ((meta(t).id))”
],
“index”: “s”,
“index_id”: “53905fdd24fda752”,
“keyspace”: “s”,
“namespace”: “default”,
“search_info”: {
“field”: “""”,
“options”: “{"index": "shadows"}”,
“outname”: “out”,
“query”: “{"query": {"conjuncts": [{"field": "lid", "term": "blah"}, {"field": "atid", "term": "abc"}]}, "score": "none"}”
},
“using”: “fts”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “cover (((((t.type) = "task") and ((t.lid) = "blah")) and ((t.atid) = "abc")))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover ((meta(t).id))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

Query #2 (Using IN)

SELECT meta(t).id FROM shadows t USE INDEX (USING FTS)
WHERE t.type=‘task’ and t.lid = ‘blah’
AND t.atid in [“abc”]

PLAN

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexFtsSearch”,
“as”: “t”,
“index”: “s”,
“index_id”: “53905fdd24fda752”,
“keyspace”: “s”,
“namespace”: “default”,
“search_info”: {
“field”: “""”,
“options”: “{"index": "shadows"}”,
“outname”: “out”,
“query”: “{"query": {"field": "lid", "term": "blah"}, "score": "none"}”
},
“using”: “fts”
},
{
“#operator”: “Fetch”,
“as”: “t”,
“keyspace”: “shadows”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((t.type) = "task") and ((t.lid) = "blah")) and ((t.atid) in ["abc"]))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(meta(t).id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

If I use IN clause in Query 2, it does a fetch, while Query #1 does not.

2

That is right. Covering of FTS Flex index is possible very narrow case. Most cases it need to do Fetch.

IN is not passed in to FTS in 6.6 release and will be addressed in future releases (MB-39350)(You can see that in “query”: “{“query”: {“field”: “lid”, “term”: “blah”}, “score”: “none”}”)
Alternative is OR+EQ vs IN

The following blog gives more details and use cases for Flex Index.

3

I tried

SELECT meta(t).id FROM s t USE INDEX (USING FTS)
WHERE t.type=‘task’ and t.lid = ‘blah’
AND (t.atid = “abc” OR t.atid = ‘c1’)

and the plan still fetches

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexFtsSearch”,
“as”: “t”,
“index”: “shadows”,
“index_id”: “67730273b02a1ac3”,
“keyspace”: “s”,
“namespace”: “default”,
“search_info”: {
“field”: “""”,
“options”: “{"index": "shadows"}”,
“outname”: “out”,
“query”: “{"query": {"disjuncts": [{"conjuncts": [{"field": "lid", "term": "blah"}, {"field": "atid", "term": "abc"}]}, {"conjuncts": [{"field": "lid", "term": "blah"}, {"field": "atid", "term": "c1"}]}]}, "score": "none"}”
},
“using”: “fts”
},
{
“#operator”: “Fetch”,
“as”: “t”,
“keyspace”: “shadows”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((t.type) = "task") and ((t.lid) = "blah")) and (((t.atid) = "abc") or ((t.atid) = "c1")))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(meta(t).id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

4

Covering of FTS Flex index is possible very narrow case (All are AND predicates, present in single index, FTS index doesn’t generate any false positives, query projecting only document key). In this case it is not (Can be improved further, may be).
If you are looking covering GSI is best option. Certain use cases FTS Flex index performs better due to early elimination .

Lot of disjuncts
Varying predicates
Multiple fields ARRAY predicates
Multiple Array predicates.

1 Like