An unwanted seqscan

Hi,

I am having trouble understanding why a seqscan is chosen for this query.

In practice the seqscan is very expensive, whereas the nested loop is usually quite fast, even with several hundred
rowsreturned from meta_keywords_url. 

The server is running version 8.1.3, and both tables were analyzed recently.  meta_keywords contains around 25% dead
rows,meta_keywords_url contains no dead rows. 

I have included the query written both as a subquery and as a join.

Thanks for any assistance!
Brian



live=> explain select * from meta_keywords where url_id in (select url_id from meta_keywords_url where host =
'postgresql.org');
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost=1755.79..545380.52 rows=9442 width=29)
   Hash Cond: ("outer".url_id = "inner".url_id)
   ->  Seq Scan on meta_keywords  (cost=0.00..507976.54 rows=7110754 width=29)
   ->  Hash  (cost=1754.35..1754.35 rows=576 width=4)
         ->  Bitmap Heap Scan on meta_keywords_url  (cost=11.02..1754.35 rows=576 width=4)
               Recheck Cond: ((host)::text = 'postgresql.org'::text)
               ->  Bitmap Index Scan on meta_keywords_url_host_path  (cost=0.00..11.02 rows=576 width=0)
                     Index Cond: ((host)::text = 'postgresql.org'::text)
(8 rows)

live=> set enable_seqscan=off;
SET
live=> explain select * from meta_keywords where url_id in (select url_id from meta_keywords_url where host =
'postgresql.org');
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1755.79..3161748.83 rows=9442 width=29)
   ->  HashAggregate  (cost=1755.79..1761.55 rows=576 width=4)
         ->  Bitmap Heap Scan on meta_keywords_url  (cost=11.02..1754.35 rows=576 width=4)
               Recheck Cond: ((host)::text = 'postgresql.org'::text)
               ->  Bitmap Index Scan on meta_keywords_url_host_path  (cost=0.00..11.02 rows=576 width=0)
                     Index Cond: ((host)::text = 'postgresql.org'::text)
   ->  Index Scan using meta_keywords_url_id on meta_keywords  (cost=0.00..5453.28 rows=2625 width=29)
         Index Cond: (meta_keywords.url_id = "outer".url_id)
(8 rows)

live=> explain select * from meta_keywords join meta_keywords_url using (url_id) where host = 'postgresql.org';
                                                                                   QUERY PLAN                        
---------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1758.52..543685.43 rows=9297 width=107)
   Hash Cond: ("outer".url_id = "inner".url_id)
   ->  Seq Scan on meta_keywords  (cost=0.00..506859.29 rows=6994929 width=28)
   ->  Hash  (cost=1757.08..1757.08 rows=577 width=83)
         ->  Bitmap Heap Scan on meta_keywords_url  (cost=11.02..1757.08 rows=577 width=83)
               Recheck Cond: ((host)::text = 'postgresql.org'::text)
               ->  Bitmap Index Scan on meta_keywords_url_host_path  (cost=0.00..11.02 rows=577 width=0)
                     Index Cond: ((host)::text = 'postgresql.org'::text)
(8 rows)

live=> set enable_seqscan=off;
SET
live=> explain select * from meta_keywords join meta_keywords_url using (url_id) where host = 'postgresql.org';
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..3348211.21 rows=9297 width=107)
   ->  Index Scan using meta_keywords_url_host_path on meta_keywords_url  (cost=0.00..2230.24 rows=577 width=83)
         Index Cond: ((host)::text = 'postgresql.org'::text)
   ->  Index Scan using meta_keywords_url_id on meta_keywords  (cost=0.00..5765.81 rows=2649 width=28)
         Index Cond: (meta_keywords.url_id = "outer".url_id)
(5 rows)