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