Thread: optimisation of outer join

Hi everyone again ! You've been so useful that I've got more questions
:-)

Sorry about the width of my posts by the way, I'm presuming it is
preferred to have wide tables than try and wrap them.

I'm trying to select all rows from a join, and then also some other
rows from a third join if the rows exist. If they don't, then return
blank entries.

My normal query which only returns full rows and the one I've tried to
alter to do exactly what I want is below. The question I have is why
my new one is so slow, and doesn't appear to be using indexes again.

(The new attempt is first)

depos=# explain select cdtitles.title, cdtitles.artist, song, fk_products_id, p.title, p.artist from cdsongs, cdtitles
leftjoin products p on (cdtitles.fk_products_id = p.id) where cdtitles.cdid = cdsongs.cdid and song like 'mushroom
festi%';
NOTICE:  QUERY PLAN:

Nested Loop  (cost=10829.23..60547.16 rows=1 width=76)
  ->  Index Scan using idx_cdsongs_song on cdsongs  (cost=0.00..4.96 rows=1 width=16)
  ->  Materialize  (cost=56010.95..56010.95 rows=362500 width=60)
        ->  Hash Join  (cost=10829.23..56010.95 rows=362500 width=60)
              ->  Seq Scan on cdtitles  (cost=0.00..17312.00 rows=362500 width=32)
              ->  Hash  (cost=9967.78..9967.78 rows=344578 width=28)
                    ->  Seq Scan on products p  (cost=0.00..9967.78 rows=344578 width=28)

EXPLAIN
depos=# select cdtitles.title, cdtitles.artist, song, fk_products_id, p.title, p.artist from cdsongs, cdtitles left
joinproducts p on (cdtitles.fk_products_id = p.id) where cdtitles.cdid = cdsongs.cdid and song like 'mushroom festi%'; 
              title               | artist |                   song                   | fk_products_id |
title            | artist  

----------------------------------+--------+------------------------------------------+----------------+------------------------------+--------
 god ween satan                   | ween   | mushroom festival in hell                |                |
             |  
 God WEEN Satan - The Oneness     | Ween   | mushroom festival in hell                |          46458 | GOD WEEN SATAN
-THE ONENESS | WEEN 
 Paintin' The Town Brown (Disc 1) | Ween   | mushroom festival in hell (holland 1/91) |                |
             |  
(3 rows)

depos=# explain select cdtitles.title, cdtitles.artist, song, fk_products_id, p.title, p.artist from cdsongs, cdtitles,
productsp where cdtitles.fk_products_id = p.id and cdtitles.cdid = cdsongs.cdid and song like 'mushroom festi%'; 
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..14.56 rows=1 width=76)
  ->  Nested Loop  (cost=0.00..9.84 rows=1 width=48)
        ->  Index Scan using idx_cdsongs_song on cdsongs  (cost=0.00..4.96 rows=1 width=16)
        ->  Index Scan using idx_cdtitles_cdid on cdtitles  (cost=0.00..4.87 rows=1 width=32)
  ->  Index Scan using products_pkey on products p  (cost=0.00..4.71 rows=1 width=28)

EXPLAIN
depos=# select cdtitles.title, cdtitles.artist, song, fk_products_id, p.title, p.artist from cdsongs, cdtitles,
productsp where cdtitles.fk_products_id = p.id and cdtitles.cdid = cdsongs.cdid and song like 'mushroom festi%'; 
            title             | artist |           song            | fk_products_id |            title             |
artist 

------------------------------+--------+---------------------------+----------------+------------------------------+--------
 God WEEN Satan - The Oneness | Ween   | mushroom festival in hell |          46458 | GOD WEEN SATAN - THE ONENESS |
WEEN
(1 row)

depos=#


Thank you for any suggestions!

 Nick

--
Part 3 MEng Cybernetics; Reading, UK       http://www.nickpiper.co.uk/
Change PGP actions of mailer or fetch key see website   1024D/3ED8B27F
Choose life. Be Vegan :-) Please reduce needless cruelty + suffering !

On Thu, 9 Aug 2001, Nicholas Piper wrote:

> Hi everyone again ! You've been so useful that I've got more questions
> :-)
>
> Sorry about the width of my posts by the way, I'm presuming it is
> preferred to have wide tables than try and wrap them.
>
> I'm trying to select all rows from a join, and then also some other
> rows from a third join if the rows exist. If they don't, then return
> blank entries.
>
> My normal query which only returns full rows and the one I've tried to
> alter to do exactly what I want is below. The question I have is why
> my new one is so slow, and doesn't appear to be using indexes again.
>
> (The new attempt is first)
>
> depos=# explain select cdtitles.title, cdtitles.artist, song,
> fk_products_id, p.title, p.artist from cdsongs, cdtitles left join
> products p on (cdtitles.fk_products_id = p.id) where cdtitles.cdid =
> cdsongs.cdid and song like 'mushroom festi%';
> NOTICE:  QUERY PLAN:

You're forcng the cdtitles cross products join first I believe here.
I think you may want to force the other join first, so maybe...

explain select s.title, s.artist, song, fk_products_id, p.title, p.artist
from (cdsongs inner join cdtitles on (cdtitles.cdid=cdsongs.cdid)) s
 left join products p on (s.fk_products_id = p.id) where song like
 'mushroom festi%';

On Thu, 09 Aug 2001, Stephan Szabo wrote:

> > depos=# explain select cdtitles.title, cdtitles.artist, song,
> > fk_products_id, p.title, p.artist from cdsongs, cdtitles left join
> > products p on (cdtitles.fk_products_id = p.id) where cdtitles.cdid =
> > cdsongs.cdid and song like 'mushroom festi%';
> > NOTICE:  QUERY PLAN:
>
> You're forcng the cdtitles cross products join first I believe here.
> I think you may want to force the other join first, so maybe...
>
> explain select s.title, s.artist, song, fk_products_id, p.title, p.artist
> from (cdsongs inner join cdtitles on (cdtitles.cdid=cdsongs.cdid)) s
>  left join products p on (s.fk_products_id = p.id) where song like
>  'mushroom festi%';

Fantastic, that works great ! Thanks :-)

 Nick

--
Part 3 MEng Cybernetics; Reading, UK       http://www.nickpiper.co.uk/
Change PGP actions of mailer or fetch key see website   1024D/3ED8B27F
Choose life. Be Vegan :-) Please reduce needless cruelty + suffering !