On Wed, 18 Dec 2002, Jessica Blank wrote:
> Hi.. again, I feel stupid. But I Googled for this info, and could not find
> it (and I call myself the Goddess of Google, and with good reason...)
>
> I wish to find a way to measure the CPU time used by any given query (or
> set of queries).
>
> I could not find any information on how to do this...
>
> Is there some secret internal table (like the V$ virtual tables in Oracle)
> that contains this info?
>
> I'd love to find a V$CPUTIME table or something, so I could best optimize
> my SQL...
>
> I don't want to have to do it the inefficient way... e.g. by running the
> same group of queries 1000 times or so several times, and averaging the
> times...
Well, you can kinda get a feel for this by using the time command in unix,
like:
time psql database -c 'explain analyze query goes here'
That will run the whole query but only output the explain analyze output,
adn the time command will tell you how long it took in user time, sys
time, and real time. Generally a large real time versus a small user+sys
time means there was lots of waiting on I/O. For instance, on my little
test database set up with 100,000 accounts for pgbench, this is what I
get:
time psql -c 'explain analyze select * from accounts'
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on accounts (cost=0.00..2640.00 rows=100000 width=100) (actual
time=0.10..1992.75 rows=100000 loops=1)
Total runtime: 2400.61 msec
(2 rows)
real 0m2.469s
user 0m0.000s
sys 0m0.020s
Which shows that the CPU was only working about 0.020 seconds, while
waiting for the I/O to finish up.
Note that often the most important optimization you can make in SQL isn't
in reducing your CPU load, but your I/O load. On most modern machines
with 1GHz processors, it's the hard drive subsystem's performance that is
your most limiting factor, so avoiding lots of head movement / I/O becomes
job one.
For somethings CPU load is an issue, and you can tell those things by the
above method, because the user+sys times will add up to almost the total
of the real time listed.
There are some profiling tools out there (OProfile comes to mind) that can
live in the kernel and profile any process, but they can be quite a
handful to setup, configure, and interpret the output.