Adding qualification conditions to EXPLAIN output

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Adding qualification conditions to EXPLAIN output
Date: 2002-03-09 23:02:17
Message-ID: 19128.1015714937@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2002-03-09 23:43:08 Re: Adding qualification conditions to EXPLAIN output
Previous Message Tom Lane 2002-03-09 22:46:28 Re: Small fix for _equalValue()