Re: using an index worst performances

Gaetano Mendola <mendola(at)bigfoot(dot)com> 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