Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #904 > unrolled thread

Get explain plan while query running

Back to article view | Back to comp.databases.postgresql

Contents

  Get explain plan while query running Rich Ford <richford990@gmail.com> - 2022-03-10 08:31 -0800
    Re: Get explain plan while query running George Neuner <gneuner2@comcast.net> - 2022-03-11 10:04 -0500

#904 — Get explain plan while query running

Is there a way to get the explain plan for a running query as opposed to having to wait for the query to complete which is the case when using EXPLAIN.

I know in Oracle there is DBMS_XPLAN and wondering if something similar in PostgreSQL. We have looked at auto_explain but this requires a cluster reboot and this is production and we have no downtime.

This is Aurora PostgreSQL 12.6.

Rich

[toc] | [next] | [standalone]

#905

On Thu, 10 Mar 2022 08:31:23 -0800 (PST), Rich Ford
<richford990@gmail.com> wrote:

>Is there a way to get the explain plan for a running query as opposed
>to having to wait for the query to complete which is the case when
>using EXPLAIN.

AFAIK, if you do /not/ specify analyze, explain only returns the plan
and does not also execute the query.  However, without analyze the
estimates of cost may be wildly inaccurate if statistics on the tables
and indexes are not up to date.


>I know in Oracle there is DBMS_XPLAN and wondering if something
>similar in PostgreSQL. We have looked at auto_explain but this
>requires a cluster reboot and this is production and we have no
>downtime.
>
>This is Aurora PostgreSQL 12.6.

[Assuming this has something to do with your previous post]
auto_explain won't help anyway if you can't wait for the problem
queries to execute.


George

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web