Re: BUG #13908: Query returns too few rows

On Thu, Feb 4, 2016 at 4:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > *The second-level Hash Join combines/sees, in both cases, 7,993 and
> > 1,275,138 records but depending on the LEFT/RIGHT order of the sub-nodes
> > appears to provide a different result.*
>
> Hmm, that's pretty fishy; and given all the hacking that's gone on lately
> on hash join, 9.5 introducing a bug there wouldn't be all that astonishing.
> But I decline to try to find it without a self-contained test case.
>
>
​So, one of the tables involved has 70M-ish rows of which only maybe 2M-ish
of them are necessary to fulfill the query in terms of getting the right
answer. Realizing this I deleted the other 68M records and then
immediately (same transaction) run the counting query and find that I still
get an incorrect result. I ran a count instead of an explain analyze so
I'm not sure exactly what it did behind the scenes. Regardless, I got the
under-counting behavior. I then commit the transaction, run vacuum
analyze, and the re-run the counting query and that completes I now get the
correct count.

Do you expect that some portion of the 68M records that were deleted would
be required to diagnose the problem or can knobs be twiddled in some way to
get similar behavior exhibited without them actually being present. The
fact that the records were deleted, but not vacuumed, and a wrong result
was returned makes me think it should be possible but I don't know enough
to say for sure or to make informed decisions as to which knobs to turn (if
indeed such knobs are exposed since I do not have source-editing or
debugging capabilities).

David J
​
​