IN is still slow in PG.
Try Exists:
Change This:
select * from article_keyword where keyword_id in
(select keyword_id from keyword where word='spam');
To:
select * from article_keyword where exists
( select keyword_id from keyword where word='spam' and keyword.keyword_id =
article_keyword.keyword_id );
They should be the same thing. Postgres will figure that out some day.
-Dennis
Paul Bemowski wrote:
> I'm having a problem getting PostgreSQL 7.2.1 to use an index when it
> is painfully obvious that the index will be faster. Here is the
> situation:
>
> 2 tables:
>
> 1) keyword
> keyword_id integer primary key
> word varchar(50)
>
> - the word is indexed (keyword_word_index)
> - the table contains ~100k rows
>
> 2) article_keyword (this is a mapping table for a many-many)
> article_id integer
> keyword_id integer
> relevenace integer
> primary_key(article_id, keyword_id)
>
> - the keyword_id col is indexed (ak_kwid_index)
> - the table contains ~2 million rows.
>
> OK. Consider the follwing psql/explain output:
> -------------------------------------------------------------------------
> search.dev=# select * from keyword where word='spam';
> keyword_id | word
> ------------+------
> 2616 | spam
> (1 row)
>
> search.dev=# explain select * from article_keyword where
> keyword_id=2616;
> NOTICE: QUERY PLAN:
>
> Index Scan using ak_kwid_index on article_keyword (cost=0.00..2990.85
> rows=810 width=12)
>
> EXPLAIN
> 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
>
---------------------------------------------------------------------------
>
> Note that when I query using the explicit keyword_id, the query plan
> uses the index at a cost of about 3000. When I use a subselect (which
> returns the EXACT same keyword_id and I explicitly used) the plan uses
> a sequence scan of 2 million rows at a cost of about 3.3 billion!!
>
> I really need for postgres to use the indexes on the mapping table,
> what can I do. Any help from the PostgreSQL gods is appreciated.
>
> Paul Bemowski
> bemowski@yahoo.com
>
