optimisation of outer join - Mailing list pgsql-general
From | Nicholas Piper |
---|---|
Subject | optimisation of outer join |
Date | |
Msg-id | 20010809102256.A16942@piamox7.haus Whole thread Raw |
Responses |
Re: optimisation of outer join
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
List | pgsql-general |
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 !
pgsql-general by date: