MIME-Version: 1.0
References: <CAM+6J96FeJxruxqPcZTxTuanKbZzmy0AUMOzChAr72y4iSd+7g@mail.gmail.com>
 <CAKFQuwYCf42_Gvf3pbyjgYzJ2-adS6j_hBgde+ORck2vXXKO5w@mail.gmail.com>
 <CAM+6J949waO2r35PDFJ65fy1Anc+oukOOy_CRw4wY65qiNc_2g@mail.gmail.com>
 <CAKFQuwY0SSLdm7KLmRVouXD_2=60-oOsdXfF56gx9n=bNhvWnw@mail.gmail.com> <1128627.1729360916@sss.pgh.pa.us>
In-Reply-To: <1128627.1729360916@sss.pgh.pa.us>
From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Date: Sat, 19 Oct 2024 23:48:12 +0530
Message-ID: <CAM+6J97N7RY0WQg2u8qiEUhOKPVgZ=fO835CYdRkGoJ3TzCJmw@mail.gmail.com>
Subject: Re: explain vs auto_explain
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e940e80624d871f7"
Archived-At: <https://www.postgresql.org/message-id/CAM%2B6J97N7RY0WQg2u8qiEUhOKPVgZ%3DfO835CYdRkGoJ3TzCJmw%40mail.gmail.com>
Precedence: bulk

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

On Sat, 19 Oct 2024 at 23:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Sat, Oct 19, 2024 at 10:43=E2=80=AFAM Vijaykumar Jain <
> > vijaykumarjain.github@gmail.com> wrote:
> >> i tried to check the code for auto_explain , there is nothing that hel=
ps
> >> understand why it was provided as a separate .
>
> > Probably because output to log was easier than reworking the internals =
to
> > make output to client happen.
>
> The reason that auto_explain exists is to capture plans for queries
> that are being issued by real applications --- which aren't programmed
> to issue EXPLAIN for themselves, and likely don't have a good place to
> put the data if they did.  Also, auto_explain can capture runtime
> details for queries that are really being executed and delivering
> results, whereas EXPLAIN ANALYZE doesn't deliver the query results and
> thus can't be shoehorned into real applications.  So it's partly a
> matter of not having a protocol spec that would allow the EXPLAIN data
> to be delivered on a side channel, but mostly a recognition that
> rewriting applications to capture such data would be painful.
>
>                         regards, tom lane
>

ok, it makes sense for the reason of having auto_explain. but maybe i did
ask correctly,
why do we not have the extended flags in auto_explain , in , explain wrt
nested_statements, and triggers ...
a user who finds the console output complicated, could well use a pager or
redirect the output to the file via \o which is client side.

as i mentioned the reason is, there are differences on what auto_explain
captures and what explain does... and the dev user is not able to see the
difference
without having access to logs.
for example , iirc
refresh materialised view does not show the plan , although there was once
a feature reported, which showed the difference in support for parallelism.
ex in this discussion
Thread: CREATE/REFRESH MATERIALIZED VIEW planner difference? : Postgres
Professional <https://postgrespro.com/list/thread-id/2553661>

i dont expect this to be a feature request or something, it was just that i
wanted to be aware why there are differences,
 because the cloud guys have strict control over logs as it has many other
things, so they just wont give access at all.


--=20
Thanks,
Vijay

Open to work
Resume - Vijaykumar Jain <https://github.com/cabecada>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 19 Oct 2024 =
at 23:31, Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.=
us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">&quot;David G. Johnston&quot; &lt;<a href=3D"mailto:david.g.johnston@gmai=
l.com" target=3D"_blank">david.g.johnston@gmail.com</a>&gt; writes:<br>
&gt; On Sat, Oct 19, 2024 at 10:43=E2=80=AFAM Vijaykumar Jain &lt;<br>
&gt; <a href=3D"mailto:vijaykumarjain.github@gmail.com" target=3D"_blank">v=
ijaykumarjain.github@gmail.com</a>&gt; wrote:<br>
&gt;&gt; i tried to check the code for auto_explain , there is nothing that=
 helps<br>
&gt;&gt; understand why it was provided as a separate .<br>
<br>
&gt; Probably because output to log was easier than reworking the internals=
 to<br>
&gt; make output to client happen.<br>
<br>
The reason that auto_explain exists is to capture plans for queries<br>
that are being issued by real applications --- which aren&#39;t programmed<=
br>
to issue EXPLAIN for themselves, and likely don&#39;t have a good place to<=
br>
put the data if they did.=C2=A0 Also, auto_explain can capture runtime<br>
details for queries that are really being executed and delivering<br>
results, whereas EXPLAIN ANALYZE doesn&#39;t deliver the query results and<=
br>
thus can&#39;t be shoehorned into real applications.=C2=A0 So it&#39;s part=
ly a<br>
matter of not having a protocol spec that would allow the EXPLAIN data<br>
to be delivered on a side channel, but mostly a recognition that<br>
rewriting applications to capture such data would be painful.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><br clear=3D"all"><div>ok, it makes sense for the reason=
 of having auto_explain. but maybe i did ask correctly,=C2=A0</div><div>why=
 do we not have the extended flags in auto_explain , in , explain wrt neste=
d_statements, and triggers ...</div><div>a user who finds the console outpu=
t complicated, could well use a pager or redirect the output to the file vi=
a \o which is client side.</div><div><br></div><div>as i mentioned the reas=
on is, there are differences on what auto_explain captures and what explain=
 does... and the dev user is not able to see the difference</div><div>witho=
ut having access to logs.</div><div>for example , iirc=C2=A0</div><div>refr=
esh materialised view does not show the plan , although there was once a fe=
ature reported, which showed the difference in support for parallelism.</di=
v><div>ex in this discussion</div><div><a href=3D"https://postgrespro.com/l=
ist/thread-id/2553661">Thread: CREATE/REFRESH MATERIALIZED VIEW planner dif=
ference? : Postgres Professional</a></div><div><br></div><div>i dont expect=
 this to be a feature request or something, it was just that i wanted to be=
 aware why there are differences,</div><div>=C2=A0because the cloud guys ha=
ve strict control over logs as it has many other things, so they just wont=
=C2=A0give access at all.</div><div><br></div><div><br></div><span class=3D=
"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_sign=
ature"><div dir=3D"ltr">Thanks,<div>Vijay</div><div><br></div><div>Open to =
work</div><div>Resume -=C2=A0<a href=3D"https://github.com/cabecada" target=
=3D"_blank">Vijaykumar Jain</a><br></div></div></div></div></div>

--000000000000e940e80624d871f7--