Message-ID: <f83f7705b465f87b07da6ffe57809cc305d1881f.camel@cybertec.at>
Subject: Re: Check explain plan of a running query in other session
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jonas Gassenmeyer <gassenmj@gmail.com>, pgsql-novice@lists.postgresql.org
Date: Mon, 08 Nov 2021 09:42:46 +0100
In-Reply-To: 
 <CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A@mail.gmail.com>
References: 
	<CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-2.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f83f7705b465f87b07da6ffe57809cc305d1881f.camel%40cybertec.at>
Precedence: bulk

On Mon, 2021-11-08 at 09:20 +0100, Jonas Gassenmeyer wrote:
> I am asking myself for a while if there is an easy option (via dictionary views like
> pg_stat_activity or similar) to check what execution plan was chosen for a long
> running SQL statement while it is running in a different session?
> I have a performance problem in a plpgsql procedure that executes an update within
> a loop. My guess is that it chose a generic plan instead of bind peeking and then
> does not use an index.
> 
> I am not able to proof my theory, since I don't know how to get the explain plan for it.
> For me the easiest would be to check the running statement in a different session and
> "hook into" the other plpgsql session to check what the optimizer chose.
> 
> Is that something I can do easily? If not: What is the PostgreSQL way of doing it?

There is nothing like that built into PostgreSQL, but you could try pg_show_plans:
https://github.com/cybertec-postgresql/pg_show_plans

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com