How do I display the query time when a query completes in Vertica?

Question

When using vsql, I would like to see how long a query took to run once it completes. For example when i run:

select count(distinct key) from schema.table;

I would like to see an output like:

5678
(1 row)
total query time: 55 seconds.

If this is not possible, is there another way to measure query time?

Answer 1

In vsql type:

\timing 

and then hit Enter. You'll like what you'll see :-)

Repeating that will turn it off.

Answer 2

Regarding the other part of your question:

is there another way to measure query time?

Vertica can log a history of all queries executed on the cluster which is another source of query time. Before 6.0 the relevant system table was QUERY_REPO, starting with 6.0 it is QUERY_REQUESTS.

Assuming you're on 6.0 or higher, QUERY_REQUESTS.REQUEST_DURATION_MS will give you the query duration in milliseconds.

Example of how you might use QUERY_REQUESTS:

    select *
    from query_requests
    where request_type = 'QUERY'
    and user_name = 'dbadmin'
    and start_timestamp >= CURRENT_DATE
    and request ilike 'select%from%schema.table%'
    order by start_timestamp;

The QUERY_PROFILES.QUERY_DURATION_US and RESOURCE_ACQUISITIONS.DURATION_MS columns may also be of interest to you. Here are the short descriptions of those tables in case you're not already familiar:

RESOURCE_ACQUISITIONS - Retains information about resources (memory, open file handles, threads) acquired by each running request for each resource pool in the system.

QUERY_PROFILES - Provides information about queries that have run.