Board index PostgreSQL

Adding qualification conditions to EXPLAIN output

Adding qualification conditions to EXPLAIN output

Post by Tom La » Mon, 11 Mar 2002 08:02:56



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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 
Top

Adding qualification conditions to EXPLAIN output

Post by Bruce Momji » Mon, 11 Mar 2002 08:43:58


Quote:> 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

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

 
 
 
Top

Adding qualification conditions to EXPLAIN output

Post by Masaru Sugawa » Mon, 11 Mar 2002 14:42:09


On Sat, 09 Mar 2002 18:02:17 -0500


> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 
Top

Adding qualification conditions to EXPLAIN output

Post by Liam Stewa » Wed, 13 Mar 2002 00:39:06



> 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.

Quote:> 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

--

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 
Top

Adding qualification conditions to EXPLAIN output

Post by Zeugswetter Andreas SB S » Thu, 14 Mar 2002 01:45:45


Quote:> 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:

Quote:> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 
Top

1. Needing to add a condition based on a condition

This is from a tournaments' points table.
I am needing to add an option and need some help.
As this time this query is run to give descriptions
for all tournaments having divisions having points.
Only divisions with points exist because no division is
added until it has points. But, now I am seeing
the value of making it also be able to query just
one specific tournament. A specific tournament
may not have any points in a specific division.
As it stands now, it will add the description whether
needed or not. I would like to control this right
in the SQL statement.

At that time I need to conditionally check to see if a tournament
was selected/evaluated and which tournament that is.
I would then only need to select the records where
Tournament = varTournament AND PointsThisDay > 0
Since I will not need a description of an empty division.

Can someone tell me how to build this in as a conditional
only when "Tournament" actually equals something, without
effecting the other queries which do not require a condition
regarding "PointsThisDay" ?

"SELECT DivNumber,DivRank,DivAge,DivGender"
"FROM FSTPoints WHERE Affiliation = '" & Affiliate & "' AND DivNumber = " &
iterate & ";"

I just noticed this myself so wanted to add it.
If you question why there is an Affiliation condition, since this would also
bring up a set of records where there may be no points, something
has changed and the other affiliation has been moved to a new table.
That apparently now makes having an Affiliation condition here superfluous.
If it would make the query any easier to write, I could remove that
condition.
However, leaving it in, leaves me more versatile for the future.

Thanks,
Fox

2. Metadata

3. Help interpreting the output of EXPLAIN

4. Help....Oracle 8i Assistants not working !!!!!!

5. output of set explain ?

6. *** import table from access into SQL7.0 via ADO (ODBC) ***

7. Labeling join rule in EXPLAIN output

8. Copying of records

9. output from EXPLAIN VERBOSE

10. Set explain output

11. Explain Output made Colourful

12. EXPLAIN PLAN OUTPUT ?????????

13. Rationalizing EXPLAIN VERBOSE output


All times are UTC
Board index