> ## Content Index
> Fetch the complete content index at: https://til.codes/llms.txt
> Use this file to discover other available public pages before exploring further.

# Finding long running SQL queries in PostgreSQL
- URL: https://til.codes/finding-long-running-sql-queries-in-postgresql/
- Published: 2020-10-18T14:31:31.000Z
- Updated: 2020-10-18T14:31:31.000Z
- Author: Manu Ajith
- Tags: postgresql, SQL, #Import 2024-07-13 21:01

Recently in one of the projects that I was working on, I came across a situation where the SQL query times was getting slower and slower per day, and I had to figure out what was happening to the system.

One of the things that I checked immediately was whether there were any long running queries in the system, that is affecting the other queries, and whoila, there it was - the system had a bunch of rogue queries that had been running for over a day or so.

Running the following command gave me queries that were running for more than 5 seconds.

```sql
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state ='active' AND query NOT ILIKE '%pg_stat_activity%' AND (now() - pg_stat_activity.query_start) > interval '5 seconds'
ORDER BY duration desc;
```

This would give you a result like:

```SQL
-[ RECORD 1 ]------------------------------------------------------------
pid      | 6473
duration | 00:00:01.011715
state    | active
query    | ..........

```

Now you have the `pid` of the query, lets try to get some more information on the query.

```bash
strace -f -s2048 -p 6473
```

Running a `strace` on the query gives you more information on what the query is doing.

In my case, it was waiting indefinitely for acquiring a lock, which would never succeed. Solution for me was to terminate the query as it had already gone rogue.

```sql
SELECT pg_cancel_backend(6473)

```

the `pg_cancel_backend` command kills the running query.