Re: Long running queries

We do a variety of variants on the following to find long running things.
The one below is looking for 'idle in txn' txns longer than 20 minutes.

SELECT NOW() - coalesce(xact_start, state_change) AS duration, * FROM
pg_stat_activity WHERE NOW() - coalesce(xact_st
art, state_change) > interval '20 minutes' AND state = 'idle in
transaction' ORDER BY xact_start

On Wed, Jan 25, 2023, 4:14 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Jan 25, 2023 at 10:57:58AM -0700, Scott Ribe wrote:
> > > On Jan 25, 2023, at 10:55 AM, Clive Swan <cliveswan(at)gmail(dot)com> wrote:
> > >
> > > SQL Server has a handy SQL "tool" to spot expensive and/or long
> running queries.
> > >
> > > Is there a similar query/tool for Postgresql??
> >
> > - pg_stat_statements extension
> > - log_min_duration config parameter
>
> FYI, log_min_duration_statement will not print the duration until the
> query completes, so it is good for analysis later.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
> EDB https://enterprisedb.com
>
> Embrace your flaws. They make you human, rather than perfect,
> which you will never be.
>
>
>