planer picks a bad plan (seq-scan instead of index) when adding an additional join - Mailing list pgsql-general

hi list.

as soon as i left-join an additional table, the query takes 24sec instead of
0.2sec, although the added fields have no impact on the resultset:

--------------------
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
--------------------
Hash Left Join  (cost=8402.16..10733.16 rows=39900 width=1276) (actual
time=260.712..260.722 rows=2 loops=1)
  Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
  Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR
(lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
  ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900 width=1062) (actual
time=0.036..23.594 rows=20866 loops=1)
  ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual
time=168.121..168.121 rows=37417 loops=1)
        ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214)
(actual time=0.024..131.401 rows=37417 loops=1)
Total runtime: 264.193 ms
2 rows fetched
--------------------

now, an additional table (containing 600k records) is added through a left
join. all the sudden the query takes 24sec. although there are indices on
both dvds.dvd_ean and data_soundmedia.sm_info_ean, the planer does not make
use of the indices but rather chooses to do 2 seq-scans.

--------------------
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
--------------------
Hash Left Join  (cost=317592.21..326882.92 rows=159086 width=1936) (actual
time=21021.023..22242.253 rows=2 loops=1)
  Hash Cond: (dvds.dvd_mov_id = movies.mov_id)
  Filter: ((lower((movies.mov_name)::text) ~~ '%superman re%'::text) OR
(lower((dvds.dvd_name)::text) ~~ '%superman re%'::text) OR
(lower((dvds.dvd_edition)::text) ~~ '%superman re%'::text))
  ->  Merge Left Join  (cost=309190.05..313899.09 rows=159086 width=1722)
(actual time=19876.552..21902.007 rows=20866 loops=1)
        Merge Cond: ("outer"."?column20?" = "inner"."?column29?")
        ->  Sort  (cost=23027.68..23127.43 rows=39900 width=1062) (actual
time=507.886..520.143 rows=20866 loops=1)
              Sort Key: (dvds.dvd_ean)::text
              ->  Seq Scan on dvds  (cost=0.00..1292.00 rows=39900
width=1062) (actual time=0.047..100.415 rows=20866 loops=1)
        ->  Sort  (cost=286162.37..287781.38 rows=647601 width=660) (actual
time=19336.011..20328.247 rows=646633 loops=1)
              Sort Key: (data_soundmedia.sm_info_ean)::text
              ->  Seq Scan on data_soundmedia  (cost=0.00..31080.01
rows=647601 width=660) (actual time=0.074..2834.831 rows=647601 loops=1)
  ->  Hash  (cost=8194.93..8194.93 rows=82893 width=214) (actual
time=177.033..177.033 rows=37417 loops=1)
        ->  Seq Scan on movies  (cost=0.00..8194.93 rows=82893 width=214)
(actual time=0.118..129.716 rows=37417 loops=1)
Total runtime: 24419.939 ms
2 rows fetched
--------------------

shouldn't the planer join the additional table *after* filtering? even if it
does first joining then filtering, why isn't the existing index not used?

pgsql is 8.2beta2

thanks,
thomas