Message-ID: <a3eed63ae63a0a0ec55b963f88f12b5fda56b904.camel@cybertec.at>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without
 Third-Party Tools
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: mahamood hussain <hussain.ieg@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Sat, 11 Apr 2026 17:35:23 +0200
In-Reply-To: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
References: 
	<CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a3eed63ae63a0a0ec55b963f88f12b5fda56b904.camel%40cybertec.at>
Precedence: bulk

On Fri, 2026-04-10 at 23:18 +0530, mahamood hussain wrote:
> From a DBA perspective, I=E2=80=99m looking to proactively identify probl=
em areas=E2=80=94such as:
> =C2=A0* Long-running queries

log_min_duration_statement =3D 2000

> =C2=A0* Jobs/stored procedures consuming high temp space

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

> =C2=A0* 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 p=
lan
and tune the query if you can.

> =C2=A0* Lock waits, deadlocks, and memory-heavy operations

log_lock_waits =3D on

Deadlocks are logged automatically.

Memory use is not tracked.

Yours,
Laurenz Albe