The best way of extracting data from flex tables

Hi LANC,

Flex tables offer you flexibility in allowing to store data that has evolving schema, which it appears that you are capitalizing it well in your solution. Performance, on the other hand, is a trade-off. The storage of non-materialized columns (aka virtual columns) is not as optimized as storage for real columns. Data retrieval of non-materialized columns is expected to be slower as a result.

The way to improve performance is to materialize columns to fully take advantage of the performance benefits of the columnar architecture. In queries, Vertica will pick materialized columns (over virtual columns) whenever they are available.

You could use the materialize_flextable_columns() function to do the job automatically. Or you could selectively materialize just the ten columns you're interested in (or a subset of those), adding those manually to your flex table. Just make sure you also default them to the value stored in the flex table raw column.

Example: Say I want to materialize an Integer column my_int_col in a flex table.

ALTER FLEX TABLE flex_table ADD COLUMN my_int_col INT DEFAULT (public.MapLookup(flex_table.__raw__, 'my_int_col'))::!int;

This query now gets substantially better performance.
SELECT my_int_col FROM flex_table;

That will get you the best of both worlds: performance and flexibility to store schema-evolving data in your flex table.

Regards.

Just to add to Ariel's excellent description, here's the doc pointer to materializing virtual columns: https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/FlexTables/MaterializingFlexTables.htm

Excellent info here. Thanks to all who shared.