Direct Output to /dev/null in VSQL

Posted May 16, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Quick Tip - blue button
When tuning a query, you often need to run it many times as you make adjustments. If the query returns a lot of rows, then this can make for a messy screen and long waits as VSQL formats the data. All you really want to know is how long it took 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/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/ExportingDataUsingVsql.htm

Have fun!