pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLong running queries
6+ messages / 6 participants
[nested] [flat]
* Long running queries
@ 2023-01-25 17:55 Clive Swan <cliveswan@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Clive Swan @ 2023-01-25 17:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Long running queries
@ 2023-01-25 17:56 Wells Oliver <wells.oliver@gmail.com>
parent: Clive Swan <cliveswan@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Wells Oliver @ 2023-01-25 17:56 UTC (permalink / raw)
To: Clive Swan <cliveswan@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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:
> 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
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Long running queries
@ 2023-01-25 17:57 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Clive Swan <cliveswan@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Scott Ribe @ 2023-01-25 17:57 UTC (permalink / raw)
To: Clive Swan <cliveswan@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Long running queries
@ 2023-01-25 18:02 Ron <ronljohnsonjr@gmail.com>
parent: Clive Swan <cliveswan@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2023-01-25 18:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/25/23 11:55, Clive Swan wrote:
> Greetings
> 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_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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Long running queries
@ 2023-01-26 00:13 Bruce Momjian <bruce@momjian.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2023-01-26 00:13 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Clive Swan <cliveswan@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Long running queries
@ 2023-01-26 00:18 Benjamin Leis <benleis1@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 6+ messages in thread
From: Benjamin Leis @ 2023-01-26 00:18 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Clive Swan <cliveswan@gmail.com>; pgsql-admin@lists.postgresql.org
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@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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-26 00:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-25 17:55 Long running queries Clive Swan <cliveswan@gmail.com>
2023-01-25 17:56 ` Wells Oliver <wells.oliver@gmail.com>
2023-01-25 17:57 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-01-26 00:13 ` Bruce Momjian <bruce@momjian.us>
2023-01-26 00:18 ` Benjamin Leis <benleis1@gmail.com>
2023-01-25 18:02 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox