Re: [PG13] Planning (time + buffers) data structure in explain plan (format text) - Mailing list pgsql-hackers

From Julien Rouhaud
Subject Re: [PG13] Planning (time + buffers) data structure in explain plan (format text)
Date
Msg-id 20200819090429.s7hoyyw4a6jumgkc@nol
Whole thread Raw
In response to Re: [PG13] Planning (time + buffers) data structure in explain plan (format text)  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: [PG13] Planning (time + buffers) data structure in explain plan (format text)
List pgsql-hackers
On Wed, Aug 19, 2020 at 08:49:48PM +1200, David Rowley wrote:
> On Wed, 19 Aug 2020 at 19:22, Julien Rouhaud <rjuju123@gmail.com> wrote:
> > Hearing no objection, here's a patch to change the output as suggested by
> > Pierre:
> >
> > =# explain (analyze, buffers) select * from pg_class;
> >                                                QUERY PLAN                                              >
> > ------------------------------------------------------------------------------------------------------->
> >  Seq Scan on pg_class  (cost=0.00..16.86 rows=386 width=265) (actual time=0.020..0.561 rows=386 loops=1)
> >    Buffers: shared hit=9 read=4
> >  Planning:
> >    Planning Time: 4.345 ms
> >    Buffers: shared hit=103 read=12
> >  Execution Time: 1.447 ms
> > (6 rows)
> 
> I don't really have anything to say about the change in format, but on
> looking at the feature, I do find it strange that I need to specify
> ANALYZE to get EXPLAIN to output the buffer information for the
> planner.
> 
> I'd expect that EXPLAIN (BUFFERS) would work just fine, but I get:
> 
> ERROR:  EXPLAIN option BUFFERS requires ANALYZE
> 
> Ths docs [1] also mention this is disallowed per:
> 
> "This parameter may only be used when ANALYZE is also enabled."
> 
> I just don't agree that it should be. What if I want to get an
> indication of why the planner is slow but I don't want to wait for the
> query to execute? or don't want to execute it at all, say it's a
> DELETE!


I quite agree, this restriction is unhelpful since we have planning buffer
information.


> 
> It looks like we'd need to make BUFFERS imply SUMMARY


+1


> 
> However, I'm not quite sure how we should handle if someone does:
> EXPLAIN (BUFFERS on, SUMMARY off). Without the summary, there's no
> place to print the buffers, which seems bad as they asked for buffers.


But this won't be as much a problem if ANALYZE is asked, and having different
behaviors isn't appealing.  So maybe it's better to let people get what they
asked for even if that's contradictory?



pgsql-hackers by date:

Previous
From: David Rowley
Date:
Subject: Re: [PG13] Planning (time + buffers) data structure in explain plan (format text)
Next
From: Fujii Masao
Date:
Subject: Re: Creating a function for exposing memory usage of backend process