Re: EXPLAIN ANALYZE bug/patch

As Niel so nicely pointed out this morning, the output of EXPLAIN ANALYZE is
not quite clear when branches of the query are never executed. So this tiny
patch fixes that.

The patch is attached and can also be found at:
http://svana.org/kleptog/pgsql/pgsql-explain.patch

--- Example of new output ---

template1=# explain analyze select 1 where exists (select 1 from pg_class
where relname = 's') and 0 = (select 1 from pg_proc);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.23..0.23 rows=0 loops=1)
One-Time Filter: ($0 AND (0 = $1))
InitPlan
-> Seq Scan on pg_class (cost=0.00..4.55 rows=1 width=0) (actual time=0.22..0.22 rows=0 loops=1)
Filter: (relname = 's'::name)
-> Seq Scan on pg_proc (cost=0.00..71.89 rows=1489 width=0) (never executed)
Total runtime: 0.31 msec
(7 rows)

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
> As Niel so nicely pointed out this morning, the output of EXPLAIN ANALYZE is
> not quite clear when branches of the query are never executed. So this tiny
> patch fixes that.
>
> The patch is attached and can also be found at:
> http://svana.org/kleptog/pgsql/pgsql-explain.patch
>
> --- Example of new output ---
>
> template1=# explain analyze select 1 where exists (select 1 from pg_class
> where relname = 's') and 0 = (select 1 from pg_proc);
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.23..0.23 rows=0 loops=1)
> One-Time Filter: ($0 AND (0 = $1))
> InitPlan
> -> Seq Scan on pg_class (cost=0.00..4.55 rows=1 width=0) (actual time=0.22..0.22 rows=0 loops=1)
> Filter: (relname = 's'::name)
> -> Seq Scan on pg_proc (cost=0.00..71.89 rows=1489 width=0) (never executed)
> Total runtime: 0.31 msec
> (7 rows)
>
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > There are 10 kinds of people in the world, those that can do binary
> > arithmetic and those that can't.

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
> As Niel so nicely pointed out this morning, the output of EXPLAIN ANALYZE is
> not quite clear when branches of the query are never executed. So this tiny
> patch fixes that.
>
> The patch is attached and can also be found at:
> http://svana.org/kleptog/pgsql/pgsql-explain.patch
>
> --- Example of new output ---
>
> template1=# explain analyze select 1 where exists (select 1 from pg_class
> where relname = 's') and 0 = (select 1 from pg_proc);
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.23..0.23 rows=0 loops=1)
> One-Time Filter: ($0 AND (0 = $1))
> InitPlan
> -> Seq Scan on pg_class (cost=0.00..4.55 rows=1 width=0) (actual time=0.22..0.22 rows=0 loops=1)
> Filter: (relname = 's'::name)
> -> Seq Scan on pg_proc (cost=0.00..71.89 rows=1489 width=0) (never executed)
> Total runtime: 0.31 msec
> (7 rows)
>
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > There are 10 kinds of people in the world, those that can do binary
> > arithmetic and those that can't.

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073