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

Enable \timing from the vsql Command Line

This tip was authored by Jim Knicely

The vsql \timing meta-command reports, in milliseconds, the length of time it takes each SQL statement to execute.

Example:

dbadmin=> \timing
Timing is on.

dbadmin=> SELECT COUNT(*) FROM big_varchar_table WHERE pk BETWEEN 1000000 AND 2000000;
  COUNT
---------
1000001
(1 row)
Time: First fetch (1 row): 179.609 ms. All rows formatted: 179.691 ms

You can also enable \timing from the command line using the vsql -i command.

Example:

[[email protected] ~]$ vsql -ic "SELECT COUNT(*) FROM big_varchar_table WHERE pk BETWEEN 1000000 AND 2000000;"
  COUNT
---------
1000001
(1 row)
Time: First fetch (1 row): 52.728 ms. All rows formatted: 52.881 ms

Have Fun!

Comments

  • CarlGCarlG Employee

    Note as well that where \timing used to be a simple toggle, you can now expressly assert the state desired with:
    \timing on
    or
    \timing off

    The toggle-mode of simply \timing continues to work as well. This change was made in Vertica 8.1

  • when I try to use --timing or -i with vsql on the command line i get an error:
    /opt/vertica/bin/vsql: unrecognized option '--timing'
    Try "vsql --help" for more information.
    The version of vsql i have running is version 08.00.0003
    Do i need a newer version? Can I update vsql without updating my cluster?
    Is there a different parameter?

  • moshegmosheg Administrator
    edited August 2020

    Indeed, only from 8.1 the option to enable \timing from the command line using the vsql -i command, was added.
    It is advised to install the latest client version, because all clients are forward compatible.
    The section which provides information on compatibility for the Vertica Analytic Database driver and server versions, is shown here:
    https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SupportedPlatforms/ClientDrivers.htm

  • Jim_KnicelyJim_Knicely Administrator
    edited August 2020

    Just for fun I verifed this using a Vertica 10 vsql client and a Vertica 8.0.0-3 DB!

    [[email protected] ~]$ ./vsql --version
    vsql version 10.00.0100, built for Linux64, contains support for command-line editing
    
    [[email protected] ~]$ ./vsql -ic "SELECT version();"
                  version
    ------------------------------------
     Vertica Analytic Database v8.0.0-3
    (1 row)
    
    Time: First fetch (1 row): 5.161 ms. All rows formatted: 5.203 ms
    
Sign In or Register to comment.

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