Re: Measuring CPU time use? (Another stupid question)

On Wed, Dec 18, 2002 at 12:45:16PM -0700, scott.marlowe wrote:

Scott, Jessica,

> On Wed, 18 Dec 2002, Jessica Blank wrote:
>
> > I wish to find a way to measure the CPU time used by any given query (or
> > set of queries).

> Well, you can kinda get a feel for this by using the time command in unix,
> like:
>
> 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

I think this will only return the CPU usage for the psql command.  As
you see, sys time is only 0.02s,  and user time is 0.  The 2.449s
reported by the "real" time probably be the time used by the backend to
process the query, plus any communication overhead and the like.  This
is very close to the 2400msec returned by the explain command.


The way I'd do it is activate the usage reporting in postgresql.conf and
see the logs after each query.  You can extract the CPU usage from
there, among other useful things.  This is SHOW_QUERY_STATS,
SHOW_PARSER_STATS, SHOW_PLANNER_STATS, SHOW_EXECUTOR_STATS; see
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/runtime-config.html#LOGGING

HTH,

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I dream about dreams about dreams", sang the nightingale
under the pale moon" (Sandman)