Will an outer join on two indexed fields use the indexes?
Will an outer join on two indexed fields use the indexes?
От:
"Nick Fankhauser" <nickf@ontko.com>
Дата:
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/
Re: Will an outer join on two indexed fields use the indexes?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Nick Fankhauser" 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