Re: LARGE table won't use index?

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
>