Why is a "select * from table1 where field in (select field from table2
where condition )"
is so dramatically bad compared to:
"select * from table1, table2 where table1.field = table2.field and
condition"
I can't understand why the first query isn't optimized better than the
second one. The 'in' query forces a full table scan (it shouldn't) and
the second one uses the indexes. Does anyone know why?
I know I am no SQL guru, but my gut tells me that the 'in' operator
should be far more efficient than a join.
Here are the actual queries:
cdinfo=# explain select trackid from zsong where muzenbr in (select
muzenbr from ztitles where title = 'Mulan') ;
NOTICE: QUERY PLAN:
Seq Scan on zsong (cost=100000000.00..219321449380756.66 rows=2193213
width=4) SubPlan -> Materialize (cost=100000022.50..100000022.50 rows=10 width=4) -> Seq Scan on ztitles
(cost=100000000.00..100000022.50
rows=10 width=4)
cdinfo=# explain select trackid from zsong, ztitles where
ztitles.muzenbr = zsong.muzenbr and title = 'Mulan' ;
NOTICE: QUERY PLAN:
Merge Join (cost=0.00..183664.10 rows=219321 width=12) -> Index Scan using zsong_muznbr on zsong
(cost=0.00..156187.31
rows=2193213 width=8) -> Index Scan using ztitles_pkey on ztitles (cost=0.00..61.50
rows=10 width=4)
cdinfo=# \d zsong Table "zsong"Attribute | Type | Modifier
-----------+-------------------+-------------------------------------------muzenbr | integer |disc |
integer |trk | integer |song | character varying |trackid | integer | not
nulldefault
nextval('trackid'::text)artistid | integer |acd | character varying |
Indices: zsong_muznbr, zsong_pkey
cdinfo=# \d ztitles Table "ztitles"Attribute | Type | Modifier
------------+-------------------+----------muzenbr | integer | not nullartistid | integer
|cat2 | character varying |cat3 | character varying |cat4 | character varying |performer | character
varying|performer2 | character varying |title | character varying |artist1 | character varying |engineer |
charactervarying |producer | character varying |labelname | character varying |catalog | character varying
|distribut | character varying |released | character varying |origrel | character varying |nbrdiscs | character
varying|spar | character varying |minutes | character varying |seconds | character varying |monostereo |
charactervarying |studiolive | character varying |available | character(1) |previews | character varying
|pnotes | character varying |acd | character varying |
Index: ztitles_pkey
--
http://www.mohawksoft.com
