Thread: Determine query run-time from pg_* tables

Hi,

Is there a method to obtain the query's runtime from any of the pg_*
tables?

Right now, I'm only seeing the backend_start_time (from
pg_stat_activity) (and I'm also not sure how to interpret this.

Ow Mun Heng wrote:
> Hi,
>
> Is there a method to obtain the query's runtime from any of the pg_*
> tables?

No.  You can use log_durations and log_statement for that.  We don't
store that info in tables.

> Right now, I'm only seeing the backend_start_time (from
> pg_stat_activity) (and I'm also not sure how to interpret this.

This is the time that the session was started.  Nothing to do with the
query itself.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Oct 23, 2007, at 8:43 AM, Alvaro Herrera wrote:

> Ow Mun Heng wrote:
>> Hi,
>>
>> Is there a method to obtain the query's runtime from any of the pg_*
>> tables?
>
> No.  You can use log_durations and log_statement for that.  We don't
> store that info in tables.
>
>> Right now, I'm only seeing the backend_start_time (from
>> pg_stat_activity) (and I'm also not sure how to interpret this.
>
> This is the time that the session was started.  Nothing to do with the
> query itself.

query_start does, however, give you the time that the query started.
I use something like

SELECT procpid, client_addr, to_char(now() - query_start, 'DD
HH24:MI:SS') as query_time, current_query
FROM pg_stat_activity
ORDER BY query_time DESC;

To get a snapshot view of what's currently running in our databases.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Tue, 2007-10-23 at 09:28 -0500, Erik Jones wrote:
> > Ow Mun Heng wrote:
> >> Hi,
> >>
> >> Is there a method to obtain the query's runtime from any of the pg_*
> >> tables?
> query_start does, however, give you the time that the query started.
> I use something like
>
> SELECT procpid, client_addr, to_char(now() - query_start, 'DD
> HH24:MI:SS') as query_time, current_query
> FROM pg_stat_activity
> ORDER BY query_time DESC;

Thanks for this.. but I found that this query doesn't really do much for
the query_time. It's always 00 for a long runnig query >1min.

I've re-wrote it using

SELECT procpid, client_addr, now() - query_start as query_time,
current_query
FROM pg_stat_activity
ORDER BY query_time DESC;

the to_char doesn't really do much for me..

Thanks for the pointer though.. It led me to the right direction.