I'm using Vertica V5.0.12 and trying to optimize some queries. I am seeing wild variation between successive profile runs of exactly the same query and I am having trouble resolving the timing result printed from vsql against the timing numbers that the execution_engine_profiles table reports:
Code: Select all
VSQL reported timing (us) 4059524 2393756 3026559 3584777 4227938 4175864 3220636 2423808 3146294 2262554
Sum execution times (us) 11573332 10980750 11304506 11176371 11192633 11963247 13216589 11734707 11484416 12004472
Sum clock times (us) 150244070 72815214 86611426 106801309 86491732 90276609 85790151 78952310 101863891 69199734
Sum input queue wait (us) 3363844 1786777 2116612 3104509 2356909 2804455 2422717 2033945 2228894 1800763
Sum output queue wait (us) 0 0 0 0 0 0 0 0 0 0
The 'Sum xxxx' rows are taken from the execution_engine_profiles table and are the sum of the 'xxxx' counter name.
Strangely the 5th column reports the longest execution time (4227938 us) and yet the first column's clock times sum is largest (150244070 us). The sum of clock times is completely incorrect since there's no way that the query took 150 seconds ...
How are the clock times to be interpreted?
Also, looking across the execution times they are all much the same but again they exceed the overall time for the query, what is happening here?