Seq scan on join, not on subselect? analyze this - Mailing list pgsql-sql

Dear Postgres Folks,<br /><br /> I'm a bit confused why the query planner is not restricting my join, and not using
theindex.<br /> Two explain analyze statements follow. Why is the second so much better?<br /><br /><br /><tt><font
size="-1">lyell5=>vacuum analyze;<br /> lyell5=> select version();<br /> PostgreSQL 8.3.4 on x86_64-pc-linux-gnu,
compiledby GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)<br /><br /> lyell5=> explain analyze select *
fromwords where word_key in (select word_key from article_words where context_key=535462);<br />
+------------------------------------------------------------------------------------------------------------------------------------------------------+<br
/>|                                                                      QUERY
PLAN                                                                     |<br />
+------------------------------------------------------------------------------------------------------------------------------------------------------+<br
/>| Nested Loop  (cost=56073.81..56091.41 rows=2 width=13) (actual time=0.808..4.723 rows=777
loops=1)                                                  |<br /> |   ->  HashAggregate  (cost=56073.81..56073.83
rows=2width=4) (actual time=0.795..1.072 rows=777 loops=1)                                            |<br /> |        
-> Index Scan using article_word_idx on article_words  (cost=0.00..55960.50 rows=45327 width=4) (actual
time=0.030..0.344rows=777 loops=1) |<br /> |               Index Cond: (context_key =
535462)                                                                                                    |<br /> |  
-> Index Scan using words_pkey on words  (cost=0.00..8.78 rows=1 width=13) (actual time=0.003..0.004 rows=1
loops=777)                           |<br /> |         Index Cond: (words.word_key =
article_words.word_key)                                                                                       |<br /> |
Totalruntime: 4.936
ms                                                                                                                             
|<br/>
+------------------------------------------------------------------------------------------------------------------------------------------------------+<br
/><br/> lyell5=> explain analyze select words.* from article_words join words using (word_key) where
context_key=535462;<br/>
+------------------------------------------------------------------------------------------------------------------------------------------------+<br
/>|                                                                   QUERY
PLAN                                                                  |<br />
+------------------------------------------------------------------------------------------------------------------------------------------------+<br
/>| Hash Join  (cost=192092.90..276920.93 rows=45327 width=17) (actual time=6020.932..60084.817 rows=777
loops=1)                                 |<br /> |   Hash Cond: (article_words.word_key =
words.word_key)                                                                                        |<br /> |  
-> Index Scan using article_word_idx on article_words  (cost=0.00..55960.50 rows=45327 width=8) (actual
time=0.031..0.547rows=777 loops=1) |<br /> |         Index Cond: (context_key =
535462)                                                                                                    |<br /> |  
-> Hash  (cost=93819.62..93819.62 rows=5653462 width=13) (actual time=6020.605..6020.605 rows=5651551
loops=1)                             |<br /> |         ->  Seq Scan on words  (cost=0.00..93819.62 rows=5653462
width=13)(actual time=0.006..2010.962 rows=5651551 loops=1)                  |<br /> | Total runtime: 60085.616
ms                                                                                                                   
|<br/>
+------------------------------------------------------------------------------------------------------------------------------------------------+<br
/><br/><br /> lyell5=> \d article_words;<br />     Table "public.article_words"<br />
+-------------+---------+-----------+<br/> |   Column    |  Type   | Modifiers |<br />
+-------------+---------+-----------+<br/> | word_key    | integer |           |<br /> | context_key | integer
|          |<br /> +-------------+---------+-----------+<br /> Indexes:<br />     "article_word_idx" btree
(context_key)<br/>     "article_word_key_idx" btree (word_key) CLUSTER<br /> Foreign-key constraints:<br />    
"article_words_context_key_constraint"FOREIGN KEY (context_key) REFERENCES contexts(context_key) ON DELETE CASCADE
DEFERRABLEINITIALLY DEFERRED<br />     "article_words_word_key_constraint" FOREIGN KEY (word_key) REFERENCES
words(word_key)ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED<br /><br /> lyell5=> \d words;<br />
                                      Table "public.words"<br />
+----------+------------------------+------------------------------------------------------------+<br/> |  Column 
|         Type          |                         Modifiers                          |<br />
+----------+------------------------+------------------------------------------------------------+<br/> | word_key |
integer               | not null default nextval(('word_key_seq'::text)::regclass) |<br /> | word     | character
varying(255)| not null                                                   |<br />
+----------+------------------------+------------------------------------------------------------+<br/> Indexes:<br />
   "words_pkey" PRIMARY KEY, btree (word_key)<br />     "word_idx" btree (word)<br /> Referenced by:<br />  
"article_words_word_key_constraint"IN article_words FOREIGN KEY (word_key) REFERENCES words(word_key) ON DELETE CASCADE
DEFERRABLEINITIALLY DEFERRED<br /></font></tt><br />