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.



  • Nimmi_guptaNimmi_gupta Employee

    Check this link
    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
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.