Re: Will an outer join on two indexed fields use the indexes?

"Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
> monroe=# explain select * from
> monroe-# (charge left outer join criminal_disposition on
> monroe(# (charge.charge_id = criminal_disposition.charge_id));
> NOTICE: QUERY PLAN:

> Hash Join (cost=260.68..21110.40 rows=147101 width=360)
> -> Seq Scan on charge (cost=0.00..4883.01 rows=147101 width=252)
> -> Hash (cost=150.94..150.94 rows=5894 width=108)
> -> Seq Scan on criminal_disposition (cost=0.00..150.94 rows=5894
> width=108)

This seems like a perfectly reasonable plan to me, given that query,
and assuming that the row-count estimates aren't completely out of touch
with reality. A mergejoin-based plan isn't obviously better, and a
nestloop-based plan is almost certainly worse. (You could try forcing
those plan types and comparing the actual runtimes if you doubt it.)
If you had additional constraints --- say, a WHERE clause that selects
just one or a few rows of "charge" --- then a different plan type might
be more appropriate.

> My question is- Does the fact that this is an outer join cause this, or is
> soem other factor involved?

A left join constrains the planner's choices somewhat (it can't choose
to put the lefthand table on the inside of the join, for example). In
this case I doubt that's making any difference. Anyway, if you need an
outer join then you need it --- there are no better alternatives.

regards, tom lane