public inbox for [email protected]help / color / mirror / Atom feed
Check explain plan of a running query in other session 5+ messages / 3 participants [nested] [flat]
* Check explain plan of a running query in other session @ 2021-11-08 08:20 Jonas Gassenmeyer <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Jonas Gassenmeyer @ 2021-11-08 08:20 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check explain plan of a running query in other session @ 2021-11-08 08:42 Laurenz Albe <[email protected]> parent: Jonas Gassenmeyer <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2021-11-08 08:42 UTC (permalink / raw) To: Jonas Gassenmeyer <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check explain plan of a running query in other session @ 2021-11-10 14:24 Rafia Sabih <[email protected]> parent: Jonas Gassenmeyer <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Rafia Sabih @ 2021-11-10 14:24 UTC (permalink / raw) To: Jonas Gassenmeyer <[email protected]>; +Cc: [email protected] On Mon, 8 Nov 2021 at 09:20, Jonas Gassenmeyer <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check explain plan of a running query in other session @ 2021-12-22 07:58 Jonas Gassenmeyer <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Jonas Gassenmeyer @ 2021-12-22 07:58 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Am Mo., 8. Nov. 2021 um 09:43 Uhr schrieb Laurenz Albe < [email protected]>: > 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check explain plan of a running query in other session @ 2021-12-22 09:39 Laurenz Albe <[email protected]> parent: Jonas Gassenmeyer <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Laurenz Albe @ 2021-12-22 09:39 UTC (permalink / raw) To: Jonas Gassenmeyer <[email protected]>; +Cc: [email protected] 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 protected]>: > > There is nothing like that built into PostgreSQL, but you could try pg_show_plans: > > https://github.com/cybertec-postgresql/pg_show_plans > > 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? 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 -- Cybertec | https://www.cybertec-postgresql.com ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-12-22 09:39 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-11-08 08:20 Check explain plan of a running query in other session Jonas Gassenmeyer <[email protected]> 2021-11-08 08:42 ` Laurenz Albe <[email protected]> 2021-12-22 07:58 ` Jonas Gassenmeyer <[email protected]> 2021-12-22 09:39 ` Laurenz Albe <[email protected]> 2021-11-10 14:24 ` Rafia Sabih <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox