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

--000000000000ccc3250624d7f5e3
Content-Type: text/plain; charset="UTF-8"

On Sat, 19 Oct 2024 at 22:55, David G. Johnston <david.g.johnston@gmail.com>
wrote:

>
> 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.
>
> Thanks David.
The reason is, a normal db user who does not have access to logs or the db
server, will rely on what the console throws back.
 as a result, they are not able to get the complete view of what is
happening ...

not sure how relevant is this context, but when i was playing with citus,
they have a flag
set citus.explain_all_tasks = 1;
which is session based and dumps the plan on all remote workers / shard
nodes right to the user, which otherwise only dumps for one node .. yes its
verbose, but it helps the user to see what was run on the shard.

Similarly, I was expecting to explain to give a full plan with nested plans
if requested back to the user in their session directly. if they dont want
the extended output, the default flags should be fine ?
Or is it the case, where there can be million rows from remote tables, and
auto_explain will log all scans for all matching rows, thereby killing the
console session or something ?
i tried to check the code for auto_explain , there is nothing that helps
understand why it was provided as a separate .


-- 
Thanks,
Vijay

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

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">On Sat, 19 Oct 2024 at 2=
2:55, David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">d=
avid.g.johnston@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><div style=3D"font-family:arial,helvetica,sans-serif"><br></div></=
div><div class=3D"gmail_quote"><div style=3D"font-family:arial,helvetica,sa=
ns-serif">With auto_explain you end up producing multiple individual explai=
ns, 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 exe=
cution.=C2=A0 Running explain directly on a top-level query doesn&#39;t est=
ablish 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 s=
ingle 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 w=
ell.=C2=A0 While auto_explain uses the log file which is much simpler to us=
e in this manner since each auto_explain can just be told to write its outp=
ut to the log and not worry about any other considerations.</div><div style=
=3D"font-family:arial,helvetica,sans-serif"><br></div></div></div></blockqu=
ote><div>Thanks David.</div><div>The reason is, a normal db user who does n=
ot have access to logs or the db server, will rely on what the console thro=
ws back.</div><div>=C2=A0as a result, they are not able to get the complete=
 view of what is happening ...=C2=A0</div><div><br></div><div>not sure how =
relevant is this context, but when i was playing with citus, they have a fl=
ag</div><div>set citus.explain_all_tasks =3D 1;=C2=A0</div><div>which is se=
ssion based and dumps the plan on all remote workers / shard nodes right to=
 the user, which otherwise only dumps for one node .. yes its verbose, but =
it helps the user to see what was run on the shard.</div><div><br></div><di=
v>Similarly, I was expecting to explain to give a full plan with nested pla=
ns if requested back to the user in their session directly. if they dont wa=
nt the extended output, the default flags should be fine ?=C2=A0</div><div>=
Or is it the case, where there can be million rows from remote tables, and =
auto_explain will log all scans for all matching rows, thereby killing the =
console session or something ?</div><div>i tried to check the code for auto=
_explain , there is nothing that helps understand why it was provided as a =
separate .</div><div><br></div><div><br></div><div>=C2=A0</div></div><span =
class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gm=
ail_signature"><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>

--000000000000ccc3250624d7f5e3--