Will an outer join on two indexed fields use the indexes? 
Author Message
 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

--------------------------------------------------------------------------

Ray Ontko & Co.     Software Consulting Services     http://www.***.com/

---------------------------(end of broadcast)---------------------------



Sat, 25 Sep 2004 05:29:32 GMT
 Will an outer join on two indexed fields use the indexes?

Quote:

> 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.

Quote:
> 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

---------------------------(end of broadcast)---------------------------



Sat, 25 Sep 2004 05:46:22 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Simple Outer Join Not using Index

2. BTrieve,ODBC,order by clause using two indexed fields,Novell problem

3. I can't add a key (index) on two fields using VB

4. need help opening a two field index with VB6 using access 2000

5. outer join and indexes

6. Outer join, index failure

7. Outer Joins with Bitmap Indexes

8. help on self-outer joins & indexes

9. Outer Join in Indexed view is not allowed

10. Outer join / index loss - strange behaviour

11. Outer join, index failure

12. Help! Clustered Indexes and Outer Joins


 
Powered by phpBB® Forum Software