Re: Hash join in 8.3

Gregory Stark escreveu:
> André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br> writes:
>
>> And the query:
>>
>> # select j.i, t.t from jtest j inner join test t on t.i = j.i where (j.i*1.5)
>> between 3000000 and 4000000;
>>
>> Planner for [1]:
>> Nested Loop (cost=0.00..270192.02 rows=20000 width=41) (actual
>>
>> Planner for [2]:
>> Hash Join (cost=176924.02..297518.03 rows=20000 width=38) (actual
>>
>> Now, turning off hashing:
>> # set enable_hashjoin=off;
>> # set enable_hashagg=off;
>>
>> Again for [2]:
>> Merge Join (cost=178781.75..328370.60 rows=20000 width=38) (actual
>>
>
> I think the answer is that if you have bad statistics you'll get a bad plan
> and which bad plan is going to be pretty much random.
>
I believe the statistics are ok, I´ve runned vacuum analyze before all
those tries.
> But I'm curious if you turn off mergejoin whether you can get a Nested Loop
> plan and what cost 8.3 gives it. It looks to me like 8.3 came up with a higher
> cost for Nested Loop than 8.1.9 (I think 8.1.10 came out with some planner
> fixes btw) and so it's deciding these other plans are better. And they might
> have been better for the imaginary scenario that the planner thinks is going
> on.
Not anymore :)
Nested Loop (cost=0.00..389461.65 rows=20000 width=38)
Total runtime: 22934.656 ms

Without hash and merge, the plan is exactly the same for 8.1 and 8.3.
No inicial cost for nested loops... it seems that hash < merge < nested
in this case.

--

[]´s,

André Volpato
Ecom Tecnologia LTDA - Análise e Desenvolvimento
andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br