Re: REVIEW: EXPLAIN and nfiltered

On Thu, Jan 20, 2011 at 11:10 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Jan 19, 2011 at 10:16 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>> This patch looked good, in general, to me.  I added a few documentation
>>> updates and a comment, but it's a very straight-forward patch as far as
>>> I can tell.  Passes all regressions and my additional testing.
>
>> I have not looked at the code for this patch at all as yet, but just
>> as a general user comment - I really, really want this.  It's one of
>> about, uh, two pieces of information that the EXPLAIN output doesn't
>> give you that is incredibly important for troubleshooting.
>
> What's the other one?

In the following sort of plan:

rhaas=# explain analyze select * from bob b, sally s where b.a = s.a;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..117890.00 rows=1000 width=8) (actual
time=0.036..533.372 rows=1000 loops=1)
-> Seq Scan on sally s (cost=0.00..5770.00 rows=400000 width=4)
(actual time=0.014..46.469 rows=400000 loops=1)
-> Index Scan using bob_pkey on bob b (cost=0.00..0.27 rows=1
width=4) (actual time=0.001..0.001 rows=0 loops=400000)
Index Cond: (a = s.a)
Total runtime: 533.935 ms
(5 rows)

...you cannot really tell how many rows the index scan was expected to
match, or actually did match. The answer to the latter question
certainly isn't 0. We previously discussed making the rows= line go
out to three decimal places when used in an inner-index-scan context,
which would help a lot - you could then multiply rows by loops to get
an approximate answer. My preferred fix would be just to remove the
unhelpful division-by-nloops code that gets applied in this case, but
that's less backward-compatible.

> The main problem I've got with this patch is that there's no place to
> shoehorn the information into the textual EXPLAIN format without
> breaking a lot of expectations (and hence code --- it's insane to
> imagine that any significant amount of client-side code has been
> rewritten to make use of xml/json output yet).  It would be nice to know
> what other requests are likely to be coming down the pike before we
> decide exactly how we're going to break things.

It's hard to predict the nature of future feature requests, but this
and the above are at the top of my list of ongoing gripes, and there
isn't a close third.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company