Re: Hide 'Execution time' in EXPLAIN (COSTS OFF)

Le dimanche 12 octobre 2014 13:17:00 Andres Freund a écrit :
> On 2014-10-12 23:13:27 +1300, David Rowley wrote:
> > On Wed, Sep 24, 2014 at 8:02 AM, Christoph Berg <cb(at)df7cb(dot)de> wrote:
> > > Re: Tom Lane 2014-09-23 <15155(dot)1411493559(at)sss(dot)pgh(dot)pa(dot)us>
> > >
> > > > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > > > > On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb(at)df7cb(dot)de> wrote:
> > > > >> Can we have "EXPLAIN (timing off)" in 9.4+ hide the "Planning time"
> > > > >> line? That would even be backwards compatible with 9.x where it
> > > > >> would
> > > > >> be a no-op.
> > > > >
> > > > > I don't think that'll work becuase:
> > > > > /* check that timing is used with EXPLAIN ANALYZE */
> > > > > if (es.timing && !es.analyze)
> > > > >
> > > > > ereport(ERROR,
> > >
> > > (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> > >
> > > > > errmsg("EXPLAIN option TIMING
> > > > >
> > > > > requires ANALYZE")));
> > > >
> > > > It looks to me like that would complain about EXPLAIN (TIMING ON),
> > > > not the case Christoph is suggesting. What he proposes seems a bit
> > > > odd and non-orthogonal, but we could make the code do it if we wanted.
> > >
> > > I don't think this warrants a new flag, and TIMING OFF seems to be the
> > > right naming for it. (In fact it was the first I tried, and I was
> > > cursing quite a bit over the lack of configurability until I realized
> > > that COSTS OFF disabled the planning time display as well.) It might
> > > be a bit odd, but it's easy to remember.
> >
> > I'm pretty interested in seeing something change around here.
> > The patch I'm working on at the moment (INNER JOIN removals) implements
> > "skipping" of joins at execution time rather than planning time. Currently
> > I'm working on the regression test for this and it's not all that easy due
> > to the execution time appearing in the results.
> >
> > An explain analyze output from master with the patch can look something
> > like:
> >
> > explain (analyze, costs off, timing off)
> > select a.* from a inner join b on a.b_id = b.id inner join c on b.c_id =
> > c.id;
> >
> > QUERY PLAN
> >
> > ---------------------------------------------------
> >
> > Hash Join (actual rows=1 loops=1)
> >
> > Hash Cond: (b.c_id = c.id)
> > -> Hash Join (actual rows=1 loops=1)
> >
> > Hash Cond: (a.b_id = b.id)
> > -> Seq Scan on a (actual rows=1 loops=1)
> > -> Hash (never executed)
> >
> > -> Seq Scan on b (never executed)
> >
> > -> Hash (never executed)
> >
> > -> Seq Scan on c (never executed)
> >
> > Execution time: 0.092 ms
> >
> > (10 rows)
>
> So you're now the third person reporting problems here. Let's remove
> 'execution time' for COSTS off.
>
> I personally would even say that we should backpatch that to make
> backpatches involving regression tests less painful.

That wouldn't solve the first problem mentioned, which is that for some
regression tests one may want to test the costs themselves, which is now
impossible with the new planning time feature.

What would IMO make both cases suitable would be to eliminate ALL timing from
TIMING OFF, not only the timing on the individual nodes. As was mentioned
before, it is a bit counter intuitive to have COSTS OFF disable the planning
time, and not TIMING OFF.

>
> Greetings,
>
> Andres Freund

--
Ronan Dunklau