MIME-Version: 1.0
References: 
 <CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A@mail.gmail.com>
In-Reply-To: 
 <CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A@mail.gmail.com>
From: Rafia Sabih <rafia.pghackers@gmail.com>
Date: Wed, 10 Nov 2021 15:24:11 +0100
Message-ID: 
 <CA+FpmFf16zXGvQB_MMVeh79Wm-gRE3AGLofVKo5qsJGzOaYOtA@mail.gmail.com>
Subject: Re: Check explain plan of a running query in other session
To: Jonas Gassenmeyer <gassenmj@gmail.com>
Cc: pgsql-novice@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BFpmFf16zXGvQB_MMVeh79Wm-gRE3AGLofVKo5qsJGzOaYOtA%40mail.gmail.com>
Precedence: bulk

On Mon, 8 Nov 2021 at 09:20, Jonas Gassenmeyer <gassenmj@gmail.com> wrote:
>
> Hello,
>
> 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?

It is for reasons like these that we at Zalando developed an extension
pg_mon (https://github.com/RafiaSabih/pg_mon),
There you can find details like what are the scan methods and joins
etc used by the query.
You can give this a try.

-- 
Regards,
Rafia Sabih