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