Re: tweaking NTUP_PER_BUCKET

On 19.7.2014 20:24, Tom Lane wrote:
> Tomas Vondra <tv(at)fuzzy(dot)cz> writes:
>> I've reviewed the two test cases mentioned here, and sadly there's
>> nothing that can be 'fixed' by this patch. The problem here lies in the
>> planning stage, which decides to hash the large table - we can't fix
>> that in the executor.
>
> We've heard a couple reports before of the planner deciding to hash a
> larger table rather than a smaller one. The only reason I can think of
> for that is if the smaller table has many more duplicates, so that the
> planner thinks the executor might end up traversing long hash chains.
> The planner's estimates could easily be off in this area, of course.
> estimate_hash_bucketsize() is the likely culprit if it's wrong.
>
> Which test case are you seeing this in, exactly?

The two reported by Stephen here:

http://www.postgresql.org/message-id/20130328235627.GV4361@tamriel.snowman.net

Just download this (I had to gunzip it):

http://snowman.net/~sfrost/test_case.sql
http://snowman.net/~sfrost/test_case2.sql

regards
Tomas