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

stats on cursor and query execution troubleshooting

From: "Alban Médici (NetCentrex)" <amedici(at)fr(dot)netcentrex(dot)net>
To: pgsql-benchmarks(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: stats on cursor and query execution troubleshooting
Date: 2004-10-06 10:15:31
Message-ID: 4163C5C3.3030304@fr.netcentrex.net (view raw or flat)
Thread:
Lists: pgsql-benchmarkspgsql-performance
Hi,

I'm looking for the statistic of memory,  CPU,  filesystem access while 
executing some regular SQL query,  and I want to compare them to
same kind of results while executing a cursor function.

The stat collector give me good results (sequencial scans ,  acceded 
tuple .....)  for regular query but nor for cursor (as explain in the 
documentation)

For more results, i have activated some log level in the postgresql.conf :
    show_query_stats = true

But I have some trouble in the results interpretation  such as :

! system usage stats:

!       2.776053 elapsed 1.880000 user 0.520000 system sec

!       [1.910000 user 0.540000 sys total]

!       0/0 [0/0] filesystem blocks in/out

!       5/1 [319/148] 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:       3877 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

Here is  result done after fetching ALL a row with 178282 records in the 
table.
looking at the i/o stat of linux I saw a filesystem access while 
executing this request but not in the previous log !!!

!       0/0 [0/0] filesystem blocks in/out


I'm running postgresql 7.2.4  under redhat 7.2

Does am i wrong in my interpretation ?

Does any newest postgresql version could told me execution paln for a 
fetch AND better stats ?

thx

Ps: please excuse my poor english

-- 
Alban Médici
R&D software engineer
------------------------------
you can contact me @ :
http://www.netcentrex.net
------------------------------

Responses

pgsql-performance by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-10-06 10:19:24
Subject: Re: sequential scan on select distinct
Previous:From: Ole LangbehnDate: 2004-10-06 09:30:58
Subject: sequential scan on select distinct

pgsql-benchmarks by date

Next:From: Tom LaneDate: 2004-10-06 14:16:39
Subject: Re: [PERFORM] stats on cursor and query execution troubleshooting
Previous:From: Rod TaylorDate: 2004-09-14 20:46:06
Subject: Re: Largest DATABASE

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