Thread

Optimizing again

Walt Bigelow <walt@stimpy.com> — 1998-10-16T01:25:35Z

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