Обсуждение: Will an outer join on two indexed fields use the indexes?

Hi-

I've got another "plan" question- I'm trying to tune a query that uses an
outer join on two indexed (not unique) fields. I did an explain on it & was
surprised to find that the index wasn't used:

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)

My question is- Does the fact that this is an outer join cause this, or is
soem other factor involved? If it is the outer join, is there a different
way to join these which will be more efficient?

Thanks!

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

"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