Hide 'Execution time' in EXPLAIN (COSTS OFF)

Andres Freund <andres@2ndquadrant.com> writes:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd make
it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

Currently the output for that is:
postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
QUERY PLAN
--------------------------------
Result (actual rows=1 loops=1)
Total runtime: 0.035 ms
(2 rows)

Leaving off the total runtime doesn't seem bad to me.

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

I'm unconvinced that this'd add much to our regression testing capability,
though. The standard thing is to do an EXPLAIN to check the plan shape
and then run the query to see if it gets the right answer. Checking row
counts is pretty well subsumed by the latter, and is certainly not an
adequate substitute for it.

So on the whole, -1 ... this is an unintuitive and
non-backwards-compatible change that doesn't look like it buys much.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-06-03 15:08:15 -0400, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd make
it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

Currently the output for that is:
postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
QUERY PLAN
--------------------------------
Result (actual rows=1 loops=1)
Total runtime: 0.035 ms
(2 rows)

Leaving off the total runtime doesn't seem bad to me.

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

It's not what I'd have choosen when starting afresh, but as you say...

I'm unconvinced that this'd add much to our regression testing capability,
though. The standard thing is to do an EXPLAIN to check the plan shape
and then run the query to see if it gets the right answer. Checking row
counts is pretty well subsumed by the latter, and is certainly not an
adequate substitute for it.

The specific case I wanted it for was to test that a CREATE INDEX in a
specific situation actually has indexed a recently dead row. That can be
made visible via bitmap index scans... Generally index vs heap cases
aren't that easy to check with just the toplevel result.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jun 3, 2014 at 3:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@2ndquadrant.com> writes:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd make
it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

Currently the output for that is:
postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
QUERY PLAN
--------------------------------
Result (actual rows=1 loops=1)
Total runtime: 0.035 ms
(2 rows)

Leaving off the total runtime doesn't seem bad to me.

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

Maybe we could make it be controlled by TIMING. Seems like it fits
well-enough there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Jun 3, 2014 at 3:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

Maybe we could make it be controlled by TIMING. Seems like it fits
well-enough there.

Yeah, I thought about that too; but that sacrifices capability in the name
of terminological consistency. The point of TIMING OFF is to not pay the
very high overhead of per-node timing calls ... but that doesn't mean you
don't want the overall runtime. And it might not be convenient to get it
via client-side measurement.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On June 3, 2014 9:40:27 PM CEST, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Jun 3, 2014 at 3:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@2ndquadrant.com> writes:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd

make

it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

Currently the output for that is:
postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
QUERY PLAN
--------------------------------
Result (actual rows=1 loops=1)
Total runtime: 0.035 ms
(2 rows)

Leaving off the total runtime doesn't seem bad to me.

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

Maybe we could make it be controlled by TIMING. Seems like it fits
well-enough there.

Don't think that fits well - TIMING imo is about reducing the timing overhead. But the server side total time is still interesting. I only thought about tacking it onto COST because that already is pretty much tailored for regression test usage. C.F. disabling the planning time.

Andres

--
Please excuse brevity and formatting - I am writing this on my mobile phone.

Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund wrote:

On June 3, 2014 9:40:27 PM CEST, Robert Haas <robertmhaas@gmail.com> wrote:

Maybe we could make it be controlled by TIMING. Seems like it fits
well-enough there.

Don't think that fits well - TIMING imo is about reducing the timing
overhead. But the server side total time is still interesting. I only
thought about tacking it onto COST because that already is pretty much
tailored for regression test usage. C.F. disabling the planning time.

Pah. So what we need is a new mode, REGRESSTEST ON or something.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jun 3, 2014 at 4:02 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Andres Freund wrote:

On June 3, 2014 9:40:27 PM CEST, Robert Haas <robertmhaas@gmail.com> wrote:

Maybe we could make it be controlled by TIMING. Seems like it fits
well-enough there.

Don't think that fits well - TIMING imo is about reducing the timing
overhead. But the server side total time is still interesting. I only
thought about tacking it onto COST because that already is pretty much
tailored for regression test usage. C.F. disabling the planning time.

Pah. So what we need is a new mode, REGRESSTEST ON or something.

Well, we could invent that. But I personally think piggybacking on
COSTS makes more sense.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Jun 3, 2014 at 4:02 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Pah. So what we need is a new mode, REGRESSTEST ON or something.

Well, we could invent that. But I personally think piggybacking on
COSTS makes more sense.

I've been eagerly waiting for 8.4 to die so I could stop worrying
about how far back I can back-patch regression test cases using
"explain (costs off)". It'd be really annoying to have to wait
another five years to get a consistent new spelling of how to do
that. So yeah, let's stick to using COSTS OFF in the tests.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jun 03, 2014 at 08:05:48PM +0200, Andres Freund wrote:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd make
it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

I have wanted and would use such an option. I don't have a definite opinion
about how to spell the option.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-06-03 15:08:15 -0400, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

In 9.4. COSTS OFF for EXPLAIN prevents 'Planning time' to be
printed. Should we perhaps do the same for 'Execution time'? That'd make
it possible to use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in
regression tests.

Currently the output for that is:
postgres=# EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT 1;
QUERY PLAN
--------------------------------
Result (actual rows=1 loops=1)
Total runtime: 0.035 ms
(2 rows)

Leaving off the total runtime doesn't seem bad to me.

It seems a little weird to call it a "cost" ... but maybe that
ship has sailed given how we're treating the planning-time item.

I'm unconvinced that this'd add much to our regression testing capability,
though. The standard thing is to do an EXPLAIN to check the plan shape
and then run the query to see if it gets the right answer. Checking row
counts is pretty well subsumed by the latter, and is certainly not an
adequate substitute for it.

So on the whole, -1 ... this is an unintuitive and
non-backwards-compatible change that doesn't look like it buys much.

I've added the regression test I want this for.

0001 is the bugfix making me look into it
0002 is COSTS OFF removing the display of execution time
0003 is the regression test

Note that 0003 will require a kill -9 without 0001.

I am not sure myself if the test is really worth it. On one hand it's an
area that had seen several hard to find bugs over the years and is
likely to see further changes (e.g. CSN stuff) in the near future, on
the other hand the tests are tricky and require specific ordering.

Opinions?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Andres Freund 2014-06-04 <20140604194544.GB785@awork2.anarazel.de>

I'm unconvinced that this'd add much to our regression testing capability,
though. The standard thing is to do an EXPLAIN to check the plan shape
and then run the query to see if it gets the right answer. Checking row
counts is pretty well subsumed by the latter, and is certainly not an
adequate substitute for it.

So on the whole, -1 ... this is an unintuitive and
non-backwards-compatible change that doesn't look like it buys much.

I've added the regression test I want this for.

0001 is the bugfix making me look into it
0002 is COSTS OFF removing the display of execution time
0003 is the regression test

Note that 0003 will require a kill -9 without 0001.

I am not sure myself if the test is really worth it. On one hand it's an
area that had seen several hard to find bugs over the years and is
likely to see further changes (e.g. CSN stuff) in the near future, on
the other hand the tests are tricky and require specific ordering.

Hi,

there's another problem in this area: 9.4 adds "Planning time" to the
EXPLAIN output. If you don't want to see that, you need to use (costs
off), but this, well, also disables the costs. If you are running
regression tests to actually test the costs, you've lost in 9.4.

This problem just emerged in the Multicorn FDW where the regression
tests were monitoring the costs, but in 9.4 (costs off) kills that.

https://github.com/Kozea/Multicorn/pull/7

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 have an opinion how much this should affect the "EXPLAIN
(analyze, timing off)" output, but there's probably a sane solution.)

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.de> wrote:

there's another problem in this area: 9.4 adds "Planning time" to the
EXPLAIN output. If you don't want to see that, you need to use (costs
off), but this, well, also disables the costs. If you are running
regression tests to actually test the costs, you've lost in 9.4.

This problem just emerged in the Multicorn FDW where the regression
tests were monitoring the costs, but in 9.4 (costs off) kills that.

https://github.com/Kozea/Multicorn/pull/7

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")));

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Sep 23, 2014 at 1:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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.

Ah, right.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: Tom Lane 2014-09-23 <15155.1411493559@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Sep 24, 2014 at 8:02 AM, Christoph Berg <cb@df7cb.de> wrote:

Re: Tom Lane 2014-09-23 <15155.1411493559@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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)

From this I can see easily that the joins to b and c were skipped, however
the output the way it is at the moment is quite useless for regression
testing with.

Regards

David Rowley

On 2014-10-12 23:13:27 +1300, David Rowley wrote:

On Wed, Sep 24, 2014 at 8:02 AM, Christoph Berg <cb@df7cb.de> wrote:

Re: Tom Lane 2014-09-23 <15155.1411493559@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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@df7cb.de> wrote:

Re: Tom Lane 2014-09-23 <15155.1411493559@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Sep 20, 2014 at 4:13 PM, Christoph Berg <cb@df7cb.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

Ronan Dunklau <ronan@dunklau.fr> writes:

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.

That's a bogus argument, because it was impossible before too. We have
no such tests now, and it's unlikely we will ever add any, because costs
inherently are platform-dependent. The reason we invented COSTS OFF in
the first place was to make it possible to do EXPLAIN in regression tests
without getting platform-dependent output.

I have no great objection to making both COSTS OFF and TIMING OFF suppress
the "planning time" output, if that's the consensus. I would object to
taking away that behavior of COSTS OFF, because of the implications for
back-patching EXPLAIN queries in regression tests.

Another possibility, which would introduce less non-orthogonality into
the switch design, is to remove the connection to COSTS OFF but say that
planning time is only printed when execution time is also printed (ie,
only in EXPLAIN ANALYZE). This seems to me that it would not be removing
much functionality, because if you just did a plain EXPLAIN then you can
take the client-side runtime (psql \timing) as a close-enough estimate
of planning time.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers