Re: EXPLAIN and nfiltered, take two - Mailing list pgsql-hackers

From Tom Lane
Subject Re: EXPLAIN and nfiltered, take two
Date
Msg-id 8966.1316564392@sss.pgh.pa.us
Whole thread Raw
In response to Re: EXPLAIN and nfiltered, take two  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: EXPLAIN and nfiltered, take two
List pgsql-hackers
Marko Tiikkaja <marko.tiikkaja@2ndquadrant.com> writes:
>> The attached patch is the best I could come up with.  I considered 
>> showing "Rows Removed by Foo: (never executed)" and omitting the line 
>> altogether, but I didn't particularly like either of those options.  The 
>> current patch simply displays "Rows Removed by Foo: 0".

I ran into a couple more issues with this patch.

One is the handling of bitmapqualorig filtering (and correspondingly
indexqualorig, which the patch misses entirely).  These counts are
really quite a bit different from the other filter conditions we are
dealing with, because what they represent is not anything directly
user-controllable, but how lossy the indexscan is.  That is, we get a
count for any tuple that the index told us to visit but that turned out
to not actually satisfy the indexqual.  So the count is guaranteed zero
for any non-lossy indexscan, which includes most cases.  In view of
that, I find it useless and probably confusing to put out "Rows Removed
by Recheck Cond: 0" unless we're dealing with a lossy index.

Now the difficulty is that EXPLAIN doesn't really have any way to know
if the index is lossy, especially not if every such check luckily
happened to pass.

What I'm inclined to do is suppress the "rows removed" output, at least
in textual output format, unless it's nonzero.  But that sorta begs the
question of whether we shouldn't do that for all cases, not just index
recheck conditions.

Also, upthread it was argued that we shouldn't measure the effects of
joinqual filtering.  I don't buy this for a minute, especially not in
merge/hash joins, where a row thrown away by joinqual filtering is just
as expensive as one thrown away by otherqual filtering, and where you
can *not* determine how big the raw merge/hash join result is if you're
not told how much the joinqual removed.  I see the point about it not
being clear how to explain things for SEMI/ANTI join cases, but I think
we need to figure that out, not just punt.

Thoughts?
        regards, tom lane


pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Inlining comparators as a performance optimisation
Next
From: David Fetter
Date:
Subject: Re: WIP: Collecting statistics on CSV file data