Why does query_requests table report a query took only 100 ms when it actually took 10 seconds ?

Nicolas_R

I'm running queries against a Vertica table with close to 500 columns and only 100 000 rows.

A simple query (like select avg(col1) from mytable) takes 10 seconds, as reported by the Vertica vsql client with the \timing command.

But when checking column query_requests.request_duration_ms for this query, there's no mention of the 10 seconds, it reports less than 100 milliseconds.

The query_requests.start_timestamp column indicates that the beginning of the processing started 10 seconds after I actually executed the command.

The resource_acquisitions show no delay in resource acquisition, but its queue_entry_timestamp column also shows the queue entry occurred 10 seconds after I actually executed the command.

The same query run on the same data but on a table with only one column returns immediately. And since I'm running the queries directly on a Vertica node, I'm excluding any network latency issue.

It feels like Vertica is doing something before executing the query, which is taking most of the time, and is related to the number of columns of the table. Any idea what it could be, and what I could try to fix it ?

Jim_Knicely

Hi,

Seems like the query planing phase is taking a long time. What version of Vertica are you using? There were some issues in older versions.

I'm not seeing this problem in Vertica 9.0.1-3:

Example:

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v9.0.1-3
(1 row)

dbadmin=> select count(*) from test;
 count
--------
 100000
(1 row)

dbadmin=> select count(*) from columns where table_name = 'test';
 count
-------
   500
(1 row)

dbadmin=> \timing
Timing is on.

dbadmin=> select avg(col1) from test;
   avg
---------
 5.50554
(1 row)

Time: First fetch (1 row): 15.984 ms. All rows formatted: 16.007 ms

Nicolas_R

Thank you a lot for your answer Jim.
I'm using Vertica 8.1.0-1. I'll upgrade my test environement to Vertica 9, and will repeat the test. Hopefully, this will solve this issue

Jim_Knicely

It should as the that bug fix was in 8.1.1

See :
https://my.vertica.com/docs/ReleaseNotes/8.1.x/Vertica_8.1.x_Release_Notes.htm

Please follow up and let us know how your testing goes with Vertica 9!