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

"Nick Fankhauser" <nickf@ontko.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