Re: Slow SELECT...IN statements

I am working on a select that would use the IN statement as you can view
below.

After reading this thread, which says that the IN statement is "slow" I am
wondering how I would rewrite using "EXISTS...IN"

I've searched the docs for references to "EXISTS IN" but haven't found
anything with regards to selects.

Can you tell me how I can write this using "exists"? Would I reap a
significant performance gain by using "exists" instead of just "in"

SELECT
  index_uri.uri,
  index_uri.description,
  index_uri.title,
  index_type.type,
  index_type.icon,
  SUM(index.word_count) AS score
FROM
  index,index_word,index_uri,index_type
WHERE
  index_word.word IN ('radio','spry')
AND
  index_word.word_id=index.word_id
AND
  index_uri.uri_id = index.uri_id
AND
  index_type.type_id = index_uri.type_id
GROUP BY
  index_uri.uri,
  index_uri.description,
  index_uri.title,
  index_type.type,
  index.word_count,
  index_type.icon
ORDER BY
  score DESC




----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Jan Wessely" <jawe@jawe.net>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 23, 2001 9:12 AM
Subject: Re: Slow SELECT...IN statements


> [ Charset ISO-8859-1 unsupported, converting... ]
> > The FAQ states in entry 4.23 that SELECT...IN statements are slow and
> > recommends to use EXISTS...IN statements instead. It also states that
this
> > will be resolved in some future version.
> > I didn't find any entries about that in the TODO list, does anybody know
> > when this will be fixed?
>
> It will be fixed when we do the query tree rewrite, which is on the TODO
> list, hopefully for 7.2.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>