Display Last SQL Run Time

Jim_Knicely

When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs.

That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long ruing query, simply check the CURRENT_SESSION system to get the run time of your previous SQL statement!

Example:

dbadmin=> SELECT COUNT(*) FROM big_fact JOIN big_dim ON big_dim.c20 = big_fact.c20;
   COUNT
------------
1073784457
(1 row)

Oops! I forgot to turn on timing. That took a while to run, but how long??? This long…

dbadmin=> SELECT last_statement,
dbadmin->        last_statement_duration_us / 1000000.0 last_statement_duration_seconds
dbadmin->   FROM current_session;
                              last_statement                               | last_statement_duration_seconds
---------------------------------------------------------------------------+---------------------------------
SELECT COUNT(*) FROM big_fact JOIN big_dim ON big_dim.c20 = big_fact.c20; |              57.901827000000000
(1 row)

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/CURRENT_SESSION.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/timing.htm

Have fun!