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

From: Jonas Gassenmeyer <[email-masked]>
Date: Mon, 8 Nov 2021 09:20:33 +0100

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üße
Jonas

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 08 Nov 2021 09:42:46 +0100

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

---

From: Rafia Sabih <[email-masked]>
Date: Wed, 10 Nov 2021 15:24:11 +0100

On Mon, 8 Nov 2021 at 09:20, Jonas Gassenmeyer <[email-masked]> 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.
>
> 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.
>

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

---

From: Jonas Gassenmeyer <[email-masked]>
Date: Wed, 22 Dec 2021 08:58:59 +0100

> https://github.com/cybertec-postgresql/pg_show_plans

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

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 22 Dec 2021 10:39:17 +0100

On Wed, 2021-12-22 at 08:58 +0100, Jonas Gassenmeyer wrote:
> Am Mo., 8. Nov. 2021 um 09:43 Uhr schrieb Laurenz Albe <[email-masked]>:
> 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?

I don't think you have to put it into the PostgreSQL source tree.  You can extract
the source anywhere and use PGXS to build:

PATH=/path/to/postgresql/bin:$PATH USE_PGXS=1 make

You will need the PostgreSQL headers and "pg_config" installed.

Yours,
Laurenz Albe