How to explain plan query size limit?

As part of a predictive model we have a query that is 4,344 lines long. When we try to run an explain plan we get the message: “ALERT! SQL/Command truncated due to size”.

I couldn't find any documentation as to the size limit for an explain plan - does anyone know? Clearly the query needs to be rewritten but it would be nice to know the size limit.

thanks

Answers

  • Nimmi_guptaNimmi_gupta Employee

    Check this link
    https://forum.vertica.com/discussion/239396/explain-plan-query-size-limit
    You can increase the size of the MaxParsedQuerySizeMB config param. The default is 512 MB. Although, note that this param should be changed under support guidance...
    dbadmin=> \x
    Expanded display is on.
    dbadmin=> select parameter_name, current_value, description, change_under_support_guidance from configuration_parameters where parameter_name = 'MaxParsedQuerySizeMB';
    -[ RECORD 1 ]
    parameter_name | MaxParsedQuerySizeMB
    current_value | 512
    description | Maximum amount of memory allowed for parsing a single request; Increasing this value may help with 'Request size too big' errors (MB)
    change_under_support_guidance | t

  • marcothesanemarcothesane Employee

    @MikeKnopfler -
    ALERT! SQL/Command truncated due to size is not an error message of Vertica, but very probably from your client application - there are quite some that simply can't grasp the idea that there exists such monstrosities of single statements containing hundreds of kilobytes of characters.

    The exact error message that @Nimmi_gupta is referring to in his answer is the one that I just triggered myself on my Vertica database:
    ERROR 4964: The query contains an expression that is too complex to analyze
    I triggered it with this script:

    $ wc -l one.sql
    9988 one.sql
    $ ls -alF --time-style=long-iso one.sql
    -rw-rw-r-- 1 gessnerm gessnerm 349420 2020-05-18 16:02 one.sql
    

    So I'm trying to pump a 9988 line script of 349,420 bytes through vsql. It manages to pass through to Vertica, but then Vertica complains - and you need to try to fix it - under support guidance - as @Nimmi_gupta suggested.

    Good luck and happy playing ...

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.