Re: LARGE table won't use index?

You can't get it to use indexes on the subselect since postgres doesn't
know if the subselect corresponds directly to a tables index since it
often doesn't. There are two ways to fix the speed problem.

1. Make the subselect smaller:

SELECT *
FROM article_keyword t1
WHERE keyword_id IN
(SELECT keyword_id
FROM keyword sub1
WHERE word = 'spam'
AND t1.keyword_id = sub1.keyword_id
);

2. Join the tables:

SELECT t1.*
FROM article_keyword t1 JOIN keyword t2 USING (keyword_id)
WHERE t2.word = 'spam';

On 15 Jun 2001, Paul Bemowski wrote:
> search.dev=# explain select * from article_keyword where keyword_id in
> search.dev-# (select keyword_id from keyword where word='spam');
> NOTICE: QUERY PLAN:
>
> Seq Scan on article_keyword (cost=0.00..3378740757.90 rows=1820389
> width=12)
> SubPlan
> -> Materialize (cost=1856.04..1856.04 rows=1345 width=4)
> -> Index Scan using keyword_word_index on keyword
> (cost=0.00..1856.04 rows=1345 width=4)
>
> EXPLAIN
> ---------------------------------------------------------------------------