Re: What does \timing measure?

On Tue, Sep 24, 2013 at 6:07 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Sep 24, 2013 at 1:13 PM, Daniel Tahara <daniel(dot)tahara(at)yale(dot)edu>wrote:
>
>> I am attempting to benchmark a number of queries over a 15GB dataset with
>> ~ 10mil records. When I run linux time on the query execution (single
>> column projection), it returns 1 minute, but the \timing command returns
>> only 15 seconds?
>>
>
> Can you show exactly how you are executing those?
>

time echo '\\timing \\\\ select msg from test' | $PG_ROOT/bin/psql >>
out.txt

Here is the actual console output of running the above line four times:

Output from Unix time:
~/pgsql/bin/psql test 51.61s user 1.26s system 82% cpu 1:04.08 total
~/pgsql/bin/psql test 50.84s user 1.04s system 83% cpu 1:02.11 total
~/pgsql/bin/psql test 50.32s user 0.79s system 83% cpu 1:01.29 total
~/pgsql/bin/psql test 50.86s user 0.74s system 83% cpu 1:01.53 total

Results of \timing:
Time: 13423.454 ms
Time: 11861.327 ms
Time: 11568.109 ms
Time: 11292.633 ms

>
>
>> Can someone explain the difference? 1 minute is consistent with reading
>> the 15gb from disk at 250mb/s (I have SSDs), but is \timing supposed to
>> include that cost? Or simply the computation time plus the time to return
>> results.
>>
>
> Probably much of your data is cached in RAM so doesn't have be read from
> disk anyway. To the extent it does need to be read from disk, that time
> will be included.
>

shared_buffers are set to default (128mb), as is everything else in
postgresql.conf

>
> psql's \timing doesn't include the time it takes for psql to format and
> print the results to the screen (or whereever the output of psql is sent).
>
Got it.

>
> Cheers,
>
> Jeff
>
Thanks!