Re: Tuning - Mailing list pgsql-admin

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/