Array index and unnest causes different execution plan

SQL++
1

Hi,

I’m trying to use an array index (cover) and unnest. It seems that when I convert the syntax to use unnest, the execution plan is different.

Using the standard travel-sample, let say we create an index that looks like this:

CREATE INDEX isched_stops
ON `travel-sample` (DISTINCT ARRAY i.flight FOR i IN schedule END, stops)
WHERE (type = "route") AND (array_length(schedule) < 10);

Note that this is different from the regular isched_covered so I’ve named it isched_stops.

Original query would look like this:

SELECT schedule FROM `travel-sample`
WHERE ANY i IN schedule SATISFIES i.flight LIKE "UA%" END
and stops="0"
AND array_length(schedule) < 10 
AND type = "route";

Execution plan would include this:

"spans": [
    {
        "Range": {
            "High": [
                "\"UB\"",
                "\"0\""
            ],
            "Inclusion": 1,
            "Low": [
                "\"UA\"",
                "\"0\""
            ]
        }
    }
],

I convert it to use unnest:

SELECT i FROM `travel-sample` t 
UNNEST t.schedule AS i 
WHERE (i.flight LIKE "UA%") and (t.stops = "0") AND (array_length(t.schedule) < 10) AND (t.type = "route");

The plan becomes:

"spans": [
    {
        "Exact": true,
        "Range": {
            "High": [
                "\"UB\""
            ],
            "Inclusion": 1,
            "Low": [
                "\"UA\""
            ]
        }
    }
],

It looks like now it is filtering the stops instead as stops is not mentioned anywhere in the execution plan other than the condition (and text).

Is that the expected behavior? I wanted to index both the {scheduled.flight, stops}. (This is just an example so yes, it may not make sense why I would want to index stops.) :slight_smile:

2

This is expected. Unnest can use Array Index when array key is leading key and there is predicate on Unnest array key. In this situations only Array keys is pushed to indexer and all other predicates are applied as post scan filters.