Gaetano Mendola <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
> | Could we see EXPLAIN ANALYZE EXECUTE output for each case?
> [snip]
> See above.
Okay, so the issue here is choosing between a nestloop or a hash join
that have very nearly equal estimated costs:
> ~ -> Hash Join (cost=1.74..46.14 rows=1 width=760) (actual
> time=0.342..0.825 rows=3 loops=1)
> ~ Hash Cond: (("outer".vtype)::text = ("inner"."type")::text)
> ~ -> Nested Loop (cost=0.00..46.13 rows=1 width=760) (actual
> time=0.278..0.933 rows=3 loops=1)
> ~ Join Filter: (("outer".vtype)::text = ("inner"."type")::text)
In the indexed case it's the same choice, but at a different level of joining:
> ~ -> Hash Join (cost=1.74..13.15 rows=1 width=768) (actual
> time=0.281..0.651 rows=5 loops=1)
> ~ Hash Cond: (("outer".vtype)::text =
> ("inner"."type")::text)
> ~ -> Nested Loop (cost=0.00..13.14 rows=1 width=768) (actual
> time=0.268..0.936 rows=5 loops=1)
> ~ Join Filter: (("outer".vtype)::text =
> ("inner"."type")::text)
With only 0.01 unit of difference in the costs, it's perfectly plausible
for a change in the statistics to change the estimated cost just enough
to give one plan or the other the edge in estimated cost.
Given that the runtimes are in fact pretty similar, it doesn't bother me
that the planner is estimating them as essentially identical.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
