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?

score 22 · Accepted Answer · edited Oct 27 '15 at 19:57

22

In vsql type:

\timing

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

Repeating that will turn it off.

edited Oct 27 '15 at 19:57

woot

7,406
2
36
55

answered Jul 21 '11 at 16:51

Igor

236
1
2

I assume you meant to type "vsql" instead of "vsqk". – geoffrobinson Jul 21 '11 at 17:10
1

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) – chinnychinchin Oct 17 '16 at 15:57

score 4 · Answer 2 · edited Oct 27 '15 at 19:57

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.

score 0 · Answer 3 · edited Oct 27 '15 at 19:51

0

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.

edited Oct 27 '15 at 19:51

woot

7,406
2
36
55

answered Jul 21 '11 at 02:31

geoffrobinson

1,580
3
15
23

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

3 Answers3