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