DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=mjR+cJNX1d7r4HBxdiquS3g2W4uF/zj6jUWnLWufzrYpgpxGRo6rkQUdmrg8KozVZnKPDPQ3IFiMaA3AeYQCs/LZrSriZ+ahL+Jidz6P12ERsAO5iF9DrP6eJi6BJRzb3KIGyhi6wZWncEf9aA7XopnUyYkHwv+ihE0Qd4FxjHA=
Message-ID: <36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com>
Date: Wed, 26 Mar 2008 09:15:34 -0400
From: "Jonah H. Harris" <jonah.harris@gmail.com>
To: Jack <jdetate@gmail.com>
Subject: Re: Tuning
Cc: pgsql-admin@postgresql.org
In-Reply-To: <002601c88e02$5629fc10$027df430$@com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <002601c88e02$5629fc10$027df430$@com>

On Mon, Mar 24, 2008 at 6:56 PM, Jack <jdetate@gmail.com> wrote:
> Is there a Postgres equivalent of tkprofs for tuning?

No.  But what you could do is combine several Postgres things to try
and get the same information.

IIRC, you could use:
- log_parser_stats
- log_planner_stats
- log_executor_stats

while running EXPLAIN ANALYZE on the query.

psql -U user1 -d postgres > /tmp/pgprof.out 2>&1 <<SQL
SET log_parser_stats TO on;
SET log_planner_stats TO on;
SET log_executor_stats TO on;
SET client_min_messages TO log;
EXPLAIN ANALYZE SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;
SQL

This will output:

SET
SET
SET
SET
LOG:  PARSER STATISTICS
DETAIL:  ! system usage stats:
!       0.047635 elapsed 0.000000 user 0.000000 system sec
!       [0.008000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       2/19 [10/726] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       2/0 [41/13] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
!       0.000003 elapsed 0.000000 user 0.000000 system sec
!       [0.008000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [10/726] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [41/14] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
!       0.000002 elapsed 0.000000 user 0.000000 system sec
!       [0.008000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [10/726] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [41/15] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
!       0.142050 elapsed 0.004000 user 0.000000 system sec
!       [0.012000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/42 [10/786] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       7/0 [50/16] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:         10 read,          0 written, buffer hit
rate = 75.61%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
!       0.075110 elapsed 0.000000 user 0.000000 system sec
!       [0.012000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       1/6 [11/795] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       1/0 [51/17] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
LOG:  EXECUTOR STATISTICS
DETAIL:  ! system usage stats:
!       0.164599 elapsed 0.000000 user 0.000000 system sec
!       [0.012000 user 0.004000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       2/95 [12/884] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       8/1 [58/18] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:         10 read,          0 written, buffer hit
rate = 80.77%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..5.25 rows=225 width=56) (actual
time=19.637..20.404 rows=150 loops=1)
 Total runtime: 42.937 ms
(2 rows)

You could write a quick awk script to parse this out into something
more usable... but you get the idea.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/