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

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

"Matt Friedman" <matt@daart.ca> writes:

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

WHERE
index_word.word IN ('radio','spry')

The above is perfectly OK. It's really just a shorthand for
index_word.word = 'radio' OR index_word.word = 'spry'
anyway.

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

The thread was about "foo IN (SELECT ...)". A sub-select IN is a
completely different animal from IN (list-of-values).

regards, tom lane