Direct Output to /dev/null in VSQL

Jim_Knicely

When tuning a query you often need to run the query many times as you make adjustments. If the query returns a lot of rows that could make for a messy screen and long waits as VSQL formats the data. All you really want to know is how long did it take to run the query. One method to avoid all that output is to enclose your query in a SELECT COUNT query. But that method doesn’t show the real time it takes to format the output. A better solution is to divert the output to /dev/null!

Example:

dbadmin=> \timing on
Timing is on.

dbadmin=> SELECT COUNT(*)
dbadmin->   FROM (SELECT COUNT(*)
dbadmin(>           FROM tiny_table
dbadmin(>           JOIN big_table
dbadmin(>          USING (c1)) foo;
COUNT
-------
     1
(1 row)

Time: First fetch (1 row): 2119.499 ms. All rows formatted: 2119.529 ms

A better way to get more accurate timings is to send all the output to /dev/null!

dbadmin=> \o /dev/null

dbadmin=> SELECT * FROM tiny_table JOIN big_table USING (c1);
Time: First fetch (1000 rows): 35.897 ms. All rows formatted: 14035.126 ms

dbadmin=> \o

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/ExportingDataUsingVsql.htm

Have fun!