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:

  1. 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'),

  2. 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?




  • Never mind - we made a mistake. Please ignore. :-)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file