Обсуждение: Check explain plan of a running query in other session
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
Jonas
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
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 checkwhat 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 ageneric 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 sessionto 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