Since there were a few posts here on this, I thought I'd ask this
question.
I am trying to do a large 'join' and when I use explain on the following
query it fails to do an index scan on tbladdressdirectory.
explain
select librarynumber,
tblmediaformat.mediaformat,
tblmediatype.mediatype,
tblmediasize.mediasize,
tbladdressdirectory.company
FROM
tblmedialibrary,
tblmediaformat,
tblmediatype,
tblmediasize,
tbladdressdirectory
WHERE
tblmedialibrary.mediaformatid = tblmediaformat.formatid
AND tblmedialibrary.mediatypeid = tblmediatype.typeid
AND tblmedialibrary.mediasizeid = tblmediasize.mediasizeid
AND tblmedialibrary.fclientid = tbladdressdirectory.agencyid
AND librarynumber = '9988';
It returns:
NOTICE: QUERY PLAN:
Hash Join (cost=57.85 size=31448 width=92)
-> Hash Join (cost=18.32 size=293 width=76)
-> Hash Join (cost=11.69 size=50 width=60)
-> Nested Loop (cost=6.15 size=11 width=44)
-> Index Scan on tblmedialibrary (cost=2.05 size=2 width=2 8)
-> Index Scan on tblmediatype (cost=2.05 size=37 width=16)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on tblmediaformat (cost=2.09 size=33 width=16)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on tblmediasize (cost=1.99 size=30 width=16)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on tbladdressdirectory (cost=17.43 size=316 width=16)
EXPLAIN
But when I do:
explain select company from tbladdressdirectory WHERE agencyid = 350;
I returns:
NOTICE: QUERY PLAN:
Index Scan on tbladdressdirectory (cost=2.05 size=2 width=12)
EXPLAIN
Why does the optimizer not do an index scan on tbladdressdirectory when
doing a link? An index scan seems 12% faster..
Is there some modification to my query that can speed this up?
JUST DID THIS:
Basically tblmedialibrary has 3 fields which are "links" to
tbladdressdirectory. So, each piece of media can have 3 companies related
to it.
I added to the select: tbladdressdirectory_1.company
and then to FROM: tbladdressdirectory as tbladdressdirectory_1
then the link: tblmedialibrary.aclientid = tbladdressdirectory_1.agencyid
And again it does not do an index search on either tbladdressdirectory
search. I want to be able to grab all the data at once from the database
server, but the time it takes to do all links at once is MUCH greater than
2 or even 3 seperate selects.
This shows to be even slower when specifying multiple librarynumbers.
Oh yeah, the First line returned from explain "Hash Join" goes from
cost=57.85 to cost=958.99 after I added the second tbladdressdirectory
join. 16 times more on the cost with one additional join!
Any insight is appricated!
Thanks,
Walt