From: | André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Cc: | Gregory Stark <stark(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Hash join in 8.3 |
Date: | 2007-12-14 12:18:10 |
Message-ID: | 47627482.6060609@ecomtecnologia.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Gregory Stark escreveu:
> André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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(dot)volpato(at)ecomtecnologia(dot)com(dot)br
From | Date | Subject | |
---|---|---|---|
Next Message | Vincenzo Romano | 2007-12-14 12:29:04 | Anomalia file FILBD.TXT |
Previous Message | Richard Huxton | 2007-12-14 12:15:36 | Re: Planner ignoring to use INDEX SCAN |