Skip site navigation (1) Skip section navigation (2)

Re: Timing a query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Francisco Reyes <lists(at)natserv(dot)com>
Cc: "Elizabeth O'Neill" <elizabeth(dot)oneill(at)abcmail(dot)co(dot)uk>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Timing a query
Date: 2001-11-27 16:47:41
Message-ID: 19369.1006879661@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
>> Can someone tell me if there is a way of getting the time a query has taken
>> to run to display after the query results ( where in gives the row
>> count).

You can get more info than you probably want by setting show_query_stats
to true (either with SET, or in postgresql.conf if you want it across
all backends).  Also set debug_print_query to true.  Now you'll get
stuff like this in the postmaster log:

DEBUG:  query: select count(*) from tenk1 a, tenk1 b where a.unique1=b.unique2;
DEBUG:  QUERY STATISTICS
! system usage stats:
!	2.128752 elapsed 1.300000 user 0.100000 system sec
!	[1.380000 user 0.110000 sys total]
!	249/119 [249/122] filesystem blocks in/out
!	0/0 [0/43] page faults/reclaims, 0 [0] swaps
!	0 [0] signals rcvd, 0/0 [6/7] messages rcvd/sent
!	82/51 [86/59] voluntary/involuntary context switches
! postgres usage stats:
!	Shared blocks:        480 read,          0 written, buffer hit rate = 28.25%
!	Local  blocks:          0 read,          0 written, buffer hit rate = 0.00%
!	Direct blocks:          0 read,          0 written

You'll have to look at the code to interpret all of these numbers, but I
think the numbers in square brackets are the raw getrusage results (ie,
totals since backend startup) and the ones outside are the deltas over
the query.  I've never looked to see exactly what's counted in the
"postgres usage stats".

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Lamar OwenDate: 2001-11-27 17:05:33
Subject: Re: Postgres 7.1.3 RPMs for RedHat 6.2 ?
Previous:From: Stephan SzaboDate: 2001-11-27 16:16:10
Subject: Re: Casting Varchar to Numeric

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group