Re: Check explain plan of a running query in other session

Am Mo., 8. Nov. 2021 um 09:43 Uhr schrieb Laurenz Albe <
laurenz(dot)albe(at)cybertec(dot)at>:

> 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

Thanks Laurenz,
i tried to install this extension.

One more dump question: Where do I find this *contrib* folder? It is
currently unclear to me *where* to git clone this repo - all my other
extensions just didn't need a special place they just needed an make
install.
This one seems to need a special place "somewhere between the PostgreSQL
binaries". Is there a PostgreSQL convention where to place such binaries?

---
Jonas Gassenmeyer