Thread: Adding qualification conditions to EXPLAIN output
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=10000width=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
> 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, Pennsylvania19026
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
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
> Index Scan using foo_f1_key on foo (cost=0.00..17.08 rows=1 width=12) > indxqual: (f1 = 11) > qual: (f3 = 44) Wow, that looks really nice. The field headers could probably be more verbose, like: Index Scan using foo_f1_key on foo (cost=0.00..17.08 rows=1 width=12) Index Filter: (f1 = 11) Filter: (f3 = 44) and for btree ranges: Lower Index Filter: Upper Index Filter: > 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 Imho make it standard for EXPLAIN. Simply too useful to not show it :-) Andreas