Re: Timing a query

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