public inbox for [email protected]
help / color / mirror / Atom feedRe: explain vs auto_explain
4+ messages / 3 participants
[nested] [flat]
* Re: explain vs auto_explain
@ 2024-10-19 17:50 David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2024-10-19 17:50 UTC (permalink / raw)
To: Vijaykumar Jain <[email protected]>; +Cc: pgsql-general
On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain <
[email protected]> wrote:
> i tried to check the code for auto_explain , there is nothing that helps
> 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.
If you are interested in working on a patch to address your use case I say
go for it. Designing the inputs and outputs actually doesn't seem that
challenging, if exceptionally vulnerable to bike-shedding. But I imagine
the internals are quite a different matter.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explain vs auto_explain
@ 2024-10-19 18:01 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-10-19 18:01 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Vijaykumar Jain <[email protected]>; pgsql-general
"David G. Johnston" <[email protected]> writes:
> On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain <
> [email protected]> wrote:
>> i tried to check the code for auto_explain , there is nothing that helps
>> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explain vs auto_explain
@ 2024-10-19 18:18 Vijaykumar Jain <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Vijaykumar Jain @ 2024-10-19 18:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-general
On Sat, 19 Oct 2024 at 23:31, Tom Lane <[email protected]> wrote:
> "David G. Johnston" <[email protected]> writes:
> > On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain <
> > [email protected]> wrote:
> >> i tried to check the code for auto_explain , there is nothing that helps
> >> 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.
--
Thanks,
Vijay
Open to work
Resume - Vijaykumar Jain <https://github.com/cabecada;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: explain vs auto_explain
@ 2024-10-19 18:31 Vijaykumar Jain <[email protected]>
parent: Vijaykumar Jain <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Vijaykumar Jain @ 2024-10-19 18:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-general
On Sat, 19 Oct 2024 at 23:48, Vijaykumar Jain <
[email protected]> 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;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-19 18:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-19 17:50 Re: explain vs auto_explain David G. Johnston <[email protected]>
2024-10-19 18:01 ` Tom Lane <[email protected]>
2024-10-19 18:18 ` Vijaykumar Jain <[email protected]>
2024-10-19 18:31 ` Vijaykumar Jain <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox