Query performance with Array Index


#1

SELECT x.* FROM default a use keys “xyz_12”
join default x ON keys ARRAY a.docid || “_” || TOSTRING(sub) for sub in ARRAY_RANGE(0,a.subDocIndex) END

trying to improve above query performance, is it array index improves the performance ?


#2

This query will not use any index due to USE KEYS, ON KEYS.


#3

is there any alternatives to improve performance ?


#4

You can try this

SELECT RAW t
FROM default AS t
USE KEYS ARRAY_FLATTEN((SELECT ARRAY a.docid || "_" || TOSTRING(sub) FOR sub IN ARRAY_RANGE(0,a.subDocIndex) END
                        FROM a USE KEYS "xyz_12")
                      );