On Thu, Nov 17, 2011 at 11:46 AM, Tomas Vondra
<tv@fuzzy.cz> wrote:
On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
> On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
>> I am in need of a tool or method to see each/every SQL query that
>> hits the PostgreSQL database. By query I mean the query in SQL
>> syntax with all the parameters passed.
>>
>> What I want to do is:
>> 1) see the query
>> 2) Determine how long the query takes to execute
>> 3) Possibly log both of the above to a log file
>>
>> Is there any tool internal to PostgreSQL that would allow me to do this?
>>
>> I cannot monitor it from the code that is actually connecting &
>> sending the query.
>>
>> any help or pointers would be greatly appreciated.
>
> just enable logging of queries.
As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.
Just as a warning, on heavily-loaded systems, this logging can have a significant impact to your performance. Not so much because it's logging, but due to the fact that your log-files may start requiring more disk I/O than the actual database. If you are going to do this under any serious load, I would recommend separating 'pg_log' on to a separate [set of] physical disk[s].
--Scott
There are two contrib modules that might help you - pg_stat_statements and
auto_explain. The former one is probably more interesting in this case.
Tomas
