MapLookup vs MapToString performance
We're using a flex table to store json data, which we query. (were on Vertica 8.0.1)
We've been comparing 2 implementations of our queries:
Selecting specific fields from the json. for example:
select a,b,c from some_flex_table;
In this example a,b, and c are actually keys within the json. Behind the scene when we run
explain we see that this is actually translated to MapLookup('a'), MapLookup('b'),
The other option is to get the entire json into memory, and process it there. Like so:
select public.maptostring(raw) from some_flex_table;
and the result of maptostring() is handled in memory - we have the full json and we can get to any key within it.
We've noticed that the performance of option #2 is much faster then #1. moreover - if we run multiple queries concurrently, option #1 shows significant performance degradation, where as option #2 does not.
Has anyone encountered similar performance issues with MapLookup vs MapToString ?
Is there a way to analyze this and see how much time/resources are spent by Vertica to run the MapLookup?