MIME-Version: 1.0
References: <CAM+6J96FeJxruxqPcZTxTuanKbZzmy0AUMOzChAr72y4iSd+7g@mail.gmail.com>
In-Reply-To: <CAM+6J96FeJxruxqPcZTxTuanKbZzmy0AUMOzChAr72y4iSd+7g@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 19 Oct 2024 10:25:09 -0700
Message-ID: <CAKFQuwYCf42_Gvf3pbyjgYzJ2-adS6j_hBgde+ORck2vXXKO5w@mail.gmail.com>
Subject: Re: explain vs auto_explain
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cf04b80624d7b5ed"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYCf42_Gvf3pbyjgYzJ2-adS6j_hBgde%2BORck2vXXKO5w%40mail.gmail.com>
Precedence: bulk

--000000000000cf04b80624d7b5ed
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Oct 19, 2024 at 10:05=E2=80=AFAM Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:

> Why does auto_explain have extended flags to support nested statements an=
d
> triggers whereas explain does not.
> the objects of concern have the same ownership and access, so it does not
> even sound like a security thing.
>
> or maybe it is just a format/display issue ?
> i tried to check the doc, there is no explicit mention of differences
> PostgreSQL: Documentation: 17: F.3. auto_explain =E2=80=94 log execution =
plans of
> slow queries <https://www.postgresql.org/docs/current/auto-explain.html>
>
>
With auto_explain you end up producing multiple individual explains, one
for each top-level query being executed.  Each one produced by auto_explain
which hooks into the point where a top-level query begins execution.
Running explain directly on a top-level query doesn't establish any of the
needed hooks to produce these additional explains.  While those hooks could
be installed the output for manual explain is the single query result sent
to the client.  How multiple explains could be combined into that single
output channel would need to be figured out as well.  While auto_explain
uses the log file which is much simpler to use in this manner since each
auto_explain can just be told to write its output to the log and not worry
about any other considerations.

In short, there is no fundamental reason manual explain couldn't be
improved along these lines.

David J.

--000000000000cf04b80624d7b5ed
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Sat, Oct 19, 2024 at 10:05=E2=80=AFAM Vijaykumar Jain &=
lt;<a href=3D"mailto:vijaykumarjain.github@gmail.com">vijaykumarjain.github=
@gmail.com</a>&gt; wrote:</span></div></div><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"=
ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>Why does auto_explain have exte=
nded flags to support nested statements and triggers whereas explain does n=
ot.</div><div>the objects of concern have the same ownership and access, so=
 it does not even sound like a security thing.</div><div><br></div><div>or =
maybe it is just a format/display issue ?=C2=A0</div><div>i tried to check =
the doc, there is no explicit mention of differences=C2=A0</div><div><a hre=
f=3D"https://www.postgresql.org/docs/current/auto-explain.html" target=3D"_=
blank">PostgreSQL: Documentation: 17: F.3.=C2=A0auto_explain =E2=80=94 log =
execution plans of slow queries</a></div><div><br></div></div></div></div><=
/div></blockquote><div><br></div><div class=3D"gmail_default" style=3D"font=
-family:arial,helvetica,sans-serif">With auto_explain you end up producing =
multiple individual explains, one for each top-level query being executed.=
=C2=A0 Each one produced by auto_explain which hooks into the point where a=
 top-level query begins execution.=C2=A0 Running explain directly on a top-=
level query doesn&#39;t establish any of the needed hooks to produce these =
additional explains.=C2=A0 While those hooks could be installed the output =
for manual explain is the single query result sent to the client.=C2=A0 How=
 multiple explains could be combined into that single output channel would =
need to be figured out as well.=C2=A0 While auto_explain uses the log file =
which is much simpler to use in this manner since each auto_explain can jus=
t be told to write its output to the log and not worry about any other cons=
iderations.</div><div class=3D"gmail_default" style=3D"font-family:arial,he=
lvetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-fa=
mily:arial,helvetica,sans-serif">In short, there is no fundamental reason m=
anual explain couldn&#39;t be improved along these lines.</div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></d=
iv><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-s=
erif">David J.</div><div class=3D"gmail_default" style=3D"font-family:arial=
,helvetica,sans-serif"><br></div></div></div>

--000000000000cf04b80624d7b5ed--