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?

Kimbro

Answer 1

In vsql type:

\timing 

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

Repeating that will turn it off.

Underslung

I assume you meant to type "vsql" instead of "vsqk". – Divinadivination

be aware that this is the vsql client's local timing (so this time would also include any time your query spent queuing waiting for its turn on the database server-side) – Crompton

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.

Locomotion

Answer 3

I'm not sure how to enable that in vsql or if that's possible. But you could get that information from a script.

Here's the psuedocode (I used to use perl):

print time
system("vsql -c 'select * from table'");
print time

---

Or put time into a variable and do some subtraction.

The other option is to use some tool like Toad to connect to Vertica instead of using vsql.

Divinadivination