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> <CAM+6J97N7RY0WQg2u8qiEUhOKPVgZ=fO835CYdRkGoJ3TzCJmw@mail.gmail.com>
In-Reply-To: <CAM+6J97N7RY0WQg2u8qiEUhOKPVgZ=fO835CYdRkGoJ3TzCJmw@mail.gmail.com>
From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Date: Sun, 20 Oct 2024 00:01:07 +0530
Message-ID: <CAM+6J97kkL_EkCnK9weJLcxgJaTDPFJVV6EKq3Q1noV7JB8cnQ@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="0000000000001e91120624d8a096"
Archived-At: <https://www.postgresql.org/message-id/CAM%2B6J97kkL_EkCnK9weJLcxgJaTDPFJVV6EKq3Q1noV7JB8cnQ%40mail.gmail.com>
Precedence: bulk

--0000000000001e91120624d8a096
Content-Type: text/plain; charset="UTF-8"

On Sat, 19 Oct 2024 at 23:48, Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:

>
>
> 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.
>
>
actually my bad. pls ignore.

i forgot we could leverage client_min_messages to get the output to console

/*
postgres=# explain analyze create materialized view mv as select * from t;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..35.50 rows=2550 width=4) (actual
time=0.027..0.028 rows=0 loops=1)
 Planning Time: 0.549 ms
 Execution Time: 7.309 ms
(3 rows)

                                 ^
postgres=# explain analyze refresh materialized view mv;
                QUERY PLAN
-------------------------------------------
 Utility statements have no plan structure
(1 row)

postgres=# alter system set client_min_messages TO log;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# refresh materialized view
postgres-# mv ;
LOG:  duration: 0.016 ms  plan:
Query Text: refresh materialized view
mv ;
Seq Scan on public.t  (cost=0.00..35.50 rows=2550 width=4) (actual
time=0.009..0.009 rows=0 loops=1)
  Output: col1
REFRESH MATERIALIZED VIEW
*/


I think i am good. thanks all.
-- 
Thanks,
Vijay

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

--0000000000001e91120624d8a096
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:48, Vijaykumar Jain &lt;<a href=3D"mailto:vijaykumarjain.github@gmail=
.com">vijaykumarjain.github@gmail.com</a>&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div=
 dir=3D"ltr"><br></div><br clear=3D"all"><div>ok, it makes sense for the re=
ason 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 n=
ested_statements, and triggers ...</div><div>a user who finds the console o=
utput complicated, could well use a pager or redirect the output to the fil=
e via \o which is client side.</div><div><br></div></div></div></blockquote=
><div>=C2=A0</div><div>actually my bad. pls ignore.</div><div><br></div><di=
v>i forgot we could leverage client_min_messages to get the output to conso=
le=C2=A0</div><div><br></div><div>/*</div><div>postgres=3D# explain analyze=
 create materialized view mv as select * from t;</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN</div=
><div>---------------------------------------------------------------------=
--------------------------</div><div>=C2=A0Seq Scan on t=C2=A0 (cost=3D0.00=
..35.50 rows=3D2550 width=3D4) (actual time=3D0.027..0.028 rows=3D0 loops=
=3D1)</div><div>=C2=A0Planning Time: 0.549 ms</div><div>=C2=A0Execution Tim=
e: 7.309 ms</div><div>(3 rows)</div><div><br></div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0^</div><div>postgres=3D# explain analyze refresh=
 materialized view mv;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 QUERY PLAN</div><div>----------------------------------------=
---</div><div>=C2=A0Utility statements have no plan structure</div><div>(1 =
row)</div><div><br></div><div>postgres=3D# alter system set client_min_mess=
ages TO log;</div><div>ALTER SYSTEM</div><div>postgres=3D# select pg_reload=
_conf();</div><div>=C2=A0pg_reload_conf</div><div>----------------</div><di=
v>=C2=A0t</div><div>(1 row)</div><div><br></div><div>postgres=3D# refresh m=
aterialized view</div><div>postgres-# mv ;</div><div>LOG:=C2=A0 duration: 0=
.016 ms=C2=A0 plan:</div><div>Query Text: refresh materialized view</div><d=
iv>mv ;</div><div>Seq Scan on public.t=C2=A0 (cost=3D0.00..35.50 rows=3D255=
0 width=3D4) (actual time=3D0.009..0.009 rows=3D0 loops=3D1)</div><div>=C2=
=A0 Output: col1</div><div>REFRESH MATERIALIZED VIEW</div><div>*/</div><div=
><br></div><div><br></div><div>I think i am good. thanks all.=C2=A0</div></=
div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" c=
lass=3D"gmail_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.co=
m/cabecada" target=3D"_blank">Vijaykumar Jain</a><br></div></div></div></di=
v></div>

--0000000000001e91120624d8a096--