Обсуждение: Long running queries
Greetings
SQL Server has a handy SQL "tool" to spot expensive and/or long running queries.
Is there a similar query/tool for Postgresql??
Thanks
Clive
I have this essentially bookmarked and query it often. I actually have a Slackbot running that yells at people if they show up in the results.
SELECT * FROM pg_stat_activity WHERE usename IS NOT NULL AND state = 'active' AND query_start <= NOW() - INTERVAL '60 minutes'; -- Long query
On Wed, Jan 25, 2023 at 9:55 AM Clive Swan <cliveswan@gmail.com> wrote:
GreetingsSQL Server has a handy SQL "tool" to spot expensive and/or long running queries.Is there a similar query/tool for Postgresql??ThanksClive
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
> On Jan 25, 2023, at 10:55 AM, Clive Swan <cliveswan@gmail.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 - pg_stat_activity view
On 1/25/23 11:55, Clive Swan wrote:
pg_stat_activity lists all of the currently running queries (among the columns in the table are query string, database name, user name, transaction start time and query start time).
GreetingsSQL Server has a handy SQL "tool" to spot expensive and/or long running queries.Is there a similar query/tool for Postgresql??
pg_stat_activity lists all of the currently running queries (among the columns in the table are query string, database name, user name, transaction start time and query start time).
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
On Wed, Jan 25, 2023 at 10:57:58AM -0700, Scott Ribe wrote: > > On Jan 25, 2023, at 10:55 AM, Clive Swan <cliveswan@gmail.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@momjian.us> https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
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
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@momjian.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@gmail.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@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Embrace your flaws. They make you human, rather than perfect,
which you will never be.