I have been fooling around with adding decompiled display of plan
qualification conditions to EXPLAIN output.  With this, you can
for example tell the difference between indexscanned and
not-indexscanned clauses, without having to dig through EXPLAIN
VERBOSE dumps.  Here is an example motivated by Rob Hoopman's
recent query on pgsql-general:

regression=# create table foo (f1 int, f2 int, f3 int, unique(f1,f2));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo'
CREATE
regression=# explain select * from foo where f1 = 11;
INFO:  QUERY PLAN:

Index Scan using foo_f1_key on foo  (cost=0.00..17.07 rows=5 width=12)
  indxqual: (f1 = 11)

EXPLAIN
regression=# explain select * from foo where f1 = 11 and f2 = 44;
INFO:  QUERY PLAN:

Index Scan using foo_f1_key on foo  (cost=0.00..4.83 rows=1 width=12)
  indxqual: ((f1 = 11) AND (f2 = 44))

EXPLAIN
regression=# explain select * from foo where f1 = 11 and f3 = 44;
INFO:  QUERY PLAN:

Index Scan using foo_f1_key on foo  (cost=0.00..17.08 rows=1 width=12)
  indxqual: (f1 = 11)
  qual: (f3 = 44)

EXPLAIN
regression=# explain select * from foo where f2 = 11 and f3 = 44;
INFO:  QUERY PLAN:

Seq Scan on foo  (cost=0.00..25.00 rows=1 width=12)
  qual: ((f2 = 11) AND (f3 = 44))

EXPLAIN

The display of join conditions isn't yet ready for prime time:

regression=# explain select * from tenk1 a left join tenk1 b using (unique1)
regression-# where a.hundred < b.hundred;
INFO:  QUERY PLAN:

Merge Join  (cost=0.00..2343.45 rows=10000 width=296)
  merge: ("outer"."?column1?" = "inner"."?column16?")
  qual: ("outer"."?column7?" < "inner"."?column6?")
  ->  Index Scan using tenk1_unique1 on tenk1 a  (cost=0.00..1071.78 rows=10000 width=148)
  ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..1071.78 rows=10000 width=148)

EXPLAIN

but it's getting there.

Question for the group: does this seem valuable enough to put into the
standard EXPLAIN output, or should it be a special option?  I can
imagine showing it only in EXPLAIN VERBOSE's summary display, or adding
a GUC variable to enable it, or adding another option keyword to
EXPLAIN, but I don't much want to do any of those things.  On the other
hand, maybe this stuff won't make any sense to non-experts anyway.
Thoughts?

			regards, tom lane

Sat, 09 Mar 2002 18:02:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Adding qualification conditions to EXPLAIN output
> Question for the group: does this seem valuable enough to put into the
> standard EXPLAIN output, or should it be a special option?  I can
> imagine showing it only in EXPLAIN VERBOSE's summary display, or adding
> a GUC variable to enable it, or adding another option keyword to
> EXPLAIN, but I don't much want to do any of those things.  On the other
> hand, maybe this stuff won't make any sense to non-experts anyway.
> Thoughts?

I like EXPLAIN VERBOSE for that. GUC seems overkill.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 9 Mar 2002 18:43:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding qualification conditions to EXPLAIN output
On Sat, 09 Mar 2002 18:02:17 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I have been fooling around with adding decompiled display of plan
> qualification conditions to EXPLAIN output.  With this, you can
> for example tell the difference between indexscanned and
> not-indexscanned clauses, without having to dig through EXPLAIN
> VERBOSE dumps.  Here is an example motivated by Rob Hoopman's
> recent query on pgsql-general:
 ...
> Question for the group: does this seem valuable enough to put into the
> standard EXPLAIN output, or should it be a special option?  I can
> imagine showing it only in EXPLAIN VERBOSE's summary display, or adding
> a GUC variable to enable it, or adding another option keyword to
> EXPLAIN, but I don't much want to do any of those things.  On the other
> hand, maybe this stuff won't make any sense to non-experts anyway.
> Thoughts?

AFAIC, I'd think adding another keyword is better if the standard
EXPLAIN is extended. 

 e.g. 
   EXPLAIN keyword SELECT * FROM ...
   EXPLAIN ANALYZE keyword SELECT * FROM ...
   

Regards,
Masaru Sugawara



Sun, 10 Mar 2002 14:43:14 +0900
Masaru Sugawara <rk73@sea.plala.or.jp>
Re: Adding qualification conditions to EXPLAIN output
On Sat, Mar 09, 2002 at 06:02:17PM -0500, Tom Lane wrote:
> I have been fooling around with adding decompiled display of plan
> qualification conditions to EXPLAIN output.  With this, you can
> for example tell the difference between indexscanned and
> not-indexscanned clauses, without having to dig through EXPLAIN
> VERBOSE dumps.  Here is an example motivated by Rob Hoopman's
> recent query on pgsql-general:

Very neat, Tom. Information on projections would also be nice.

> Question for the group: does this seem valuable enough to put into the
> standard EXPLAIN output, or should it be a special option?  I can
> imagine showing it only in EXPLAIN VERBOSE's summary display, or adding
> a GUC variable to enable it, or adding another option keyword to
> EXPLAIN, but I don't much want to do any of those things.  On the other
> hand, maybe this stuff won't make any sense to non-experts anyway.
> Thoughts?

My initial thought is to display the information in one of the new
VERBOSE levels, perhaps the first (default)?

Liam

-- 
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com

Mon, 11 Mar 2002 10:00:34 -0500
Liam Stewart <liams@redhat.com>
Re: Adding qualification conditions to EXPLAIN output