Hi all,
I’m wondering if someone could see my mistake I’m doing while creating a SUFFIX Index.
I simplify the Doc to make the conversation a bit easier.
Let’s assume the following Doc.
{
title: 'xxxx München',
name: 'ccvvff München bbfdfg'
location: {
city: 'München'
area: 'Bayern'
}
}
So I created two indexes already for title and name, which are working as expected.
No I tried to create as well a SUFFIX Index on location.city but this one is not used.
Here is my definition.
CREATE INDEX `guj_suffixes_locationCity` ON `guj`(distinct (array `city_array` for `city_array` in suffixes(lower(`location.city`)) end)) using GSI;
Now if I run the query:
SELECT distinct(location.city) from guj WHERE ANY city_array IN SUFFIXES(LOWER(location.city)) SATISFIES city_array LIKE ‘münchen%’ END;
the index isn’t used.
Explain:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “guj”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “guj”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “any city_array in suffixes(lower(((guj.location).city))) satisfies (city_array like “münchen%”) end”
},
{
“#operator”: “InitialProject”,
“distinct”: true,
“result_terms”: [
{
“expr”: “((guj.location).city)”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
“text”: “SELECT distinct(location.city) from guj WHERE ANY city_array IN SUFFIXES(LOWER(location.city)) SATISFIES city_array LIKE ‘münchen%’ END;”
}
On both of the first level fields, the index is working fine. So what’s my mistake here? I guess I have to define the SUFFIX index for SubDocuments differently, right?
Best
Kirsten