public inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

From: Laurenz Albe <laurenz.albe@cybertec.at>
To: mahamood hussain <hussain.ieg@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
Date: Sat, 11 Apr 2026 17:35:23 +0200
Message-ID: <a3eed63ae63a0a0ec55b963f88f12b5fda56b904.camel@cybertec.at> (raw)
In-Reply-To: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
References: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>

On Fri, 2026-04-10 at 23:18 +0530, mahamood hussain wrote:
> From a DBA perspective, I’m looking to proactively identify problem areas—such as:
>  * Long-running queries

log_min_duration_statement = 2000

>  * Jobs/stored procedures consuming high temp space

SELECT temp_blks_written, query
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 10;

>  * Queries resulting in sequential scans due to missing indexes

There is no direct way to find that.

First, look at tables that receive large sequential scans frequently:

SELECT relid::regclass, seq_scan, seq_tup_read
FROM pg_stat_all_tables
ORDER BY least(seq_scan, seq_tup_read) DESC
LIMIT 10;

Then examine the long-running queries that consume a lot of database time:

SELECT total_exec_time, query
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY total_exec_time DESC
LIMIT 10;

See if any of those use one the tables found with the first query.

Then use EXPLAIN (ANALYZE, BUFFERS) on the statement to get the execution plan
and tune the query if you can.

>  * Lock waits, deadlocks, and memory-heavy operations

log_lock_waits = on

Deadlocks are logged automatically.

Memory use is not tracked.

Yours,
Laurenz Albe

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: laurenz.albe@cybertec.at, hussain.ieg@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
  In-Reply-To: <a3eed63ae63a0a0ec55b963f88f12b5fda56b904.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox