Re: Hash join in 8.3

Tom Lane escreveu:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>
>> 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.
>>
Let me show this part again, to make things easier to understand =)

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.

> Actually, now that I think about it, 8.3 should be *more* likely than
> 8.1 to choose a nestloop-with-inner-indexscan plan. 8.1 didn't have the
> changes to allow a discount for repeated inner indexscans.
>
> I'm wondering if
>
> (a) the 8.1 installation being compared to had some planner cost
> parameter changes that were not copied into the 8.3 installation; or
>
The parameters are at their default. The only changes made are in shared
buffers, work and maintenance mem.

> (b) the only reason 8.1 likes the nestloop plan is that it has no
> statistics on the test tables, whereas 8.3 does have stats because
> of autovacuum being on by default.
>
I dont know if I understand what test tables you are talking about.
To run this tests I disabled autovacuum, and run "vacuum analyze test /
jtest" before.

--

[]´s,

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