Confused by 'timing' results

From: A J <s5aly(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Confused by 'timing' results
Date: 2010-08-31 17:42:16
Message-ID: 788866.21140.qm@web120020.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

time echo '\timing \\select * from table1 where id = 123;' | psql

I am trying to time a simple select statement from different clients located at
different places. The database is on US east-coast.
In the above query. the 'timing' will time the database time and the 'time'
command at the very start will time the complete time for the query including
network time.

When I run the query on the database, I get:
Time: 2.357 ms
real 0m0.010s

From a separate client on east-coast:
Time: 29.555 ms
real 0m0.164s

From a separate client on the west-coast:
Time: 82.236 ms
real 0m0.408s

From a separate cloud client from asia:
Time: 262.715 ms
real 0m1.311s

While I did expect the 'real' time to be different and increase (from server to
east-coast to west-coast to asia ), I did not expect the database time to
increase appreciably. Can anyone explain why the database time for a simple
select (reading from buffer) would increase so much (from 2.357 to 29.555 to
82.236 to 262.715ms) because of the client location ?

Because I ran the select several times before the above test, am assuming all
the selects just read from the shared buffer and did not hit the actual disks on
the database server.

Thank you. - AJ

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2010-08-31 18:14:27 Re: Confused by 'timing' results
Previous Message Joshua D. Drake 2010-08-31 15:57:50 Re: General migration question