The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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'),
    MapLookup('c')

  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?

Thanks,

Ofer

Comments

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.