Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How do I globally enable JFMT?

Is it possible to enable JFMT(v) for all VARCHAR columns by default (rather than do it in each query) - and if so, how?

Question from Optimizing Query Performance and Resource Pool Tuning @rbankula @bat

Best Answer


  • @rroelke and @Deb_Chatigny ,
    Just ran into this thread...
    I ran into some challenges using the parameter at DB level. At session level it worked GREAT!
    Have you or anyone else experienced this?
    We could really use the DB level parameter! Any follow up and/or help is greatly appreciated.
    @rbankula for awareness.

  • Jim_KnicelyJim_Knicely Administrator
    edited February 2021

    There are reasons why JoinDefaultTupleFormat defaults to "fixed" :) i.e. Variable-length optimized joins are limited to 2GiB of join data. If a join attempts to go above the 2GiB limit, it throws the "join inner did not fit in memory" error; Wide VARCHAR columns on inner side of a join degrades performance of hash joins; Etc...

    There are a few Jiras out there to address the known issues with the VLTS (Variable Length Tuple Store). I will keep this thread updated as to when they are resolved and are avaiable in Vertica as hot fixes.

    That being said, your workload might benefit immensely by changing JoinDefaultTupleFormat to "variable"!

  • Thanks @Jim_Knicely ,
    It has been a long time! Hope all is well.
    Thanks for some details on this parameter (including JIRA). Will keep a watch on the thread.

    With current status (version 9.2.x +), do you think I can advise my customers to use this at "session level"?
    With all the extensive tests I have done "at session level settings" this JoinDefaultTupleFormat parameter ROCKS!

    Of course "JFMT" hint could be used as well, but that is more laborious and too "tied" to code.
    Your thoughts, please?

  • Jim_KnicelyJim_Knicely Administrator
    edited February 2021

    @Sudhakar_B - Yeah, I think it's okay to recommend setting JoinDefaultTupleFormat to "variable" at the session level! This will isolate the change from anything that the database itself is up to :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
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.