1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • Get explain plan while query running

    From Rich Ford@21:1/5 to All on Thu Mar 10 08:31:23 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From George Neuner@21:1/5 to richford990@gmail.com on Fri Mar 11 10:04:19 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)