Gregory Stark escreveu:
André Volpato <andre.volpato@xxxxxxxxxxxxxxxxxxxxx> 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.volpato@xxxxxxxxxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
