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 />
