Re: Hash join in 8.3

Gregory Stark escreveu:
> André Volpato <andre.volpato@ecomtecnologia.com.br> writes:
>
>> I think I found the answer!
>>
>> 8.1: likes nested loop even after vacuumdb on the database.<br>
>>
>> 8.3: likes hash at first time but:
>> - after vacuumdb *on the database* (I was running on the tables.....), it turns out to:
>> Merge Join (cost=178779.93..328503.44 rows=30000 width=38) in 20005.207 ms
>> # set enable_mergejoin=off;
>> Hash Join(cost=156644.00..365204.03 rows=30000 width=38) in 29104.390 ms
>> * a very faster hash here, seqscanning the smaller table before the bigger one. Tricky!
>>
>> I wont trust table vacuums anymore...
>>
>>
>
> HTML-only mail isn't looked upon too favourably here.
>
My bad. Tbird for some reason isn´t auto-removing html in sent mail to
@postgresql.org.

> You keep saying "vacuum" which makes me think maybe you're not actually
> analyzing your tables at all. "vacuum" doesn't analyze the tables, you have to
> run "analyze" (or "vacuum analyze") for that.
I always run vaccumm analyze. The plan only changes in 8.3 after
"vacuumdb -v -z database".

> Sorry, I should have said "bad estimates". That is, because of the
>
>   j*1.5 BETWEEN 3000000 AND 4000000
That's supposed to be that way :)
I think all of this worth for me to have a clue on how the planner goes
in bad sql, wich causes bad estimates.

Thank you all for your support!

--

[]´s,

André Volpato
Ecom Tecnologia LTDA - Análise e Desenvolvimento
andre.volpato@ecomtecnologia.com.br