Enable \timing from the vsql Command Line

[Deleted User]

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:

[dbadmin@s18384357 ~]$ 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!

CarlG

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

brocha

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?

mosheg

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_Knicely

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

[dbadmin@vertica01 ~]$ ./vsql --version
vsql version 10.00.0100, built for Linux64, contains support for command-line editing

[dbadmin@vertica01 ~]$ ./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