Re: Hash Join cost estimates

On Thu, 2013-03-28 at 19:56 -0400, Stephen Frost wrote:
> 41K hashed, seqscan 4M: 115030.10 + 1229.46 = 116259.56
> 4M hashed, seqscan 41K: 1229.46 + 211156.20 = 212385.66

I think those are backwards -- typo?

> In the end, I think the problem here is that we are charging far too
> much for these bucket costs (particularly when we're getting them so
> far wrong) and not nearly enough for the cost of building the hash
> table in the first place.
>
> Thoughts? Ideas about how we can 'fix' this? Have others run into
> similar issues?

Yes, I have run into this issue (or something very similar). I don't
understand why the bucketsize even matters much -- assuming few hash
collisions, we are not actually evaluating the quals any more times than
necessary. So why all of the hashjoin-specific logic in determining the
number of qual evaluations? The only reason I can think of is to model
the cost of comparing the hashes themselves.

Also, searching the archives turns up at least one other, but I think
I've seen more:

http://www.postgresql.org/message-id/A82128A6-4E3B-43BD-858D-21B129F7BEEB@richrelevance.com

Regards,
Jeff Davis