Description
For unnest queries like:
explain SELECT * FROM default emp1 use index(idxVM2,idxVM3) UNNEST VMs as x where x.os = 3 and x.RAM = 3;
Where idxVM2 is an array index on x.os and idxVM3 is an index on x.RAM.
Shouldn’t the output be intersect scan instead of just indexscan on first index specified in the bracket?
Also for queries like:
explain SELECT * FROM default emp1 use index(idxVM2,idx2) UNNEST VMs as x where x.RAM = 3
and (ANY y IN emp1.tasks SATISFIES y = 'Sales' END);
Where idxVM2 is an array index on x.RAM being unnested and idx2 is an array index on tasks.
Why does the above explain query use idx2 instead of intersect scan or idxVM2?
I tried few other queries also with unnest in which I expected intersect scan will be used and it seems I don’t see it being used.