MIME-Version: 1.0
From: Jonas Gassenmeyer <gassenmj@gmail.com>
Date: Mon, 8 Nov 2021 09:20:33 +0100
Message-ID: 
 <CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A@mail.gmail.com>
Subject: Check explain plan of a running query in other session
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000433dae05d042ab8f"
Archived-At: 
 <https://www.postgresql.org/message-id/CADH0_2V5NkwpqKA5GoFq_FNd01CY6zPcNam6jdvrf0H58K16-A%40mail.gmail.com>
Precedence: bulk

--000000000000433dae05d042ab8f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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?

Danke und viele Gr=C3=BC=C3=9Fe
Jonas

--000000000000433dae05d042ab8f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Hello,<div dir=3D"auto"><br></div><div dir=3D"auto">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 f=
or a long running SQL statement while it is running in a different session?=
</div><div dir=3D"auto">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.<br></div><div=
 dir=3D"auto"><br></div><div dir=3D"auto">I am not able to proof my theory,=
 since I don&#39;t know how to get the explain plan for it.</div><div dir=
=3D"auto">For me the easiest would be to check the running statement in a d=
ifferent session and &quot;hook into&quot; the other plpgsql session to che=
ck what the optimizer chose.</div><div dir=3D"auto"><br></div><div dir=3D"a=
uto">Is that something I can do easily? If not: What is the PostgreSQL way =
of doing it?<br><br><div data-smartmail=3D"gmail_signature" dir=3D"auto">Da=
nke und viele Gr=C3=BC=C3=9Fe <br>Jonas</div></div></div>

--000000000000433dae05d042ab8f--