Profiling of SQL queries...

Hi there,

I wanted to make a *rough* estimates of how much time does
each of the SQL query sent to Postgresql takes.

The objective is to indentify slow running queries and optimise
them. also to do some statistical analysis on the queries ,
like most frequent queries etc ,etc.

To accomplish it i was thinking to run postmaster with
----------------------------
debug_print_query = true
debug_pretty_print = true
show_query_stats = true
----------------------------
and use perl to process the log files it generates,

eg when i execute "select count(*) from users;"
this i get in my logfile.

------------------------------------------------------
DEBUG: query: SELECT count(*) from users;
DEBUG: QUERY STATISTICS
! system usage stats:
! 0.519799 elapsed 0.090000 user 0.080000 system sec
! [0.100000 user 0.090000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 271/5 [573/126] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 781 read, 0 written, buffer hit rate = 25.48%
! Local blocks: 0 read, 0 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written

------------------------------------------------------

my question is is there a more earier/elegant way of doing it?
also i seek suggestion on which all figures in the log output above are
important

shud i concentrate on the "elaspsed duration" of .519799 sec or
any other figures are also imporatant.

thanks in advance for comments/suggestions

regds
mallah.

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.