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).
